Free Microsoft Excel 2013 Quick Reference

Correct VBA syntax for cell function formula

The following formula works great in a worksheet cell at counting every 5th row (5 rows total) that contains data:

=SUMPRODUCT(--(A1:A100<>""),--(MOD(ROW(A1:A100),5)=1))

However, I can't get the correct syntax for it to work in VBA code.
Can someone help? Thanks a million. mikeburg


Post your answer or comment

comments powered by Disqus
Crystal Syntax for Custom Functions

Crystal syntax custom functions are expression oriented, like Crystal syntax formulas. You can use either syntax formats listed below. Function ([ argList

What is the correct vba syntax to use to make a particular cell the active
cell when the row number and column number are known?

Basic Syntax for Custom Functions

Crystal Reports Basic for Visual Studio ... The syntax for custom functions in Basic syntax is very similar to the syntax for functions in Visual Basic.

Hello,

I was looking for a way to have a custom user setup of formulas read by a
standard VB code so the code can adjust to assigning values per formula
entered.

The process involves three worksheets:

Setup
Data
Results

For every column in Results I will have a corresponding formula in Setup.
In Setup, I will have a row to enter in the formulas that all reference the
Data worksheet cells.

In VBA, I will use a For Next loop for reading in formulas, and then nest
that into another For Next loop that runs the count of rows.

If I understand VB syntax correctly my psuedo code would look like this
(assuming that I already have the means to assign the count of rows further
up the code):

For row of Rows as i
For formula of Formulas as j
Data(i,j).formula=Setup(r,j).formula
Results(i,j).value=Evaluate(Data(i,j))
Next
Next

I will have a clear worksheet command at the top for the Results worksheet
and assign the code to a macro button. Upon clicking the macro button the
code will clear the worksheet of old data, read the number of rows of the new
Data as Rows, , do the same for columns for the number of fomrulas, and BAM
run the For Next loops to lay down converted data.

Does this sound like a good plan? This is my first real VB code from
scratch (I have been tweaking provided Macros for years and its time I grow
up!)

Thanks.


Here is the OR-FUNCTION Formula which works but I need to change
formula to use cell property references instead.

If Worksheets("Project Info").Range("$J2").Value = 1 Then
Worksheets("Update Schedule").Range("$B2").Value =
"=OR((YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=0" _
& "YEAR(B$1)-YEAR('Project
Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=12)"

HERE'S THE CODE FOR CHANGING TO USE CELL PROPERTY

I count number of rows in worksheet to find out how may times I must
loop through the formulas below in section FORMULAS FOR UPDATE
SCHEDULE. This works o'kay.

'COUNTING NUMBER OF CLIENTS ON PROJECT INFO WORKSHEET
Worksheets("Project Info").Activate
Dim r As Integer
Dim row As Range

With ActiveSheet
r = Worksheets("Project Info").Range("S1").Value + 1
Set row = Range(Cells(2, 1), Cells(r, 13))
Range(Cells(2, 1), Cells(r, 13)).Select
Worksheets("Formulas").Range("A2").Value = Selection.Rows.Count
Range("A1").Select
End With

'FORMULAS FOR UPDATE SCHEDULE
Worksheets("Update Schedule").Activate

Dim a As Integer
Dim sr As Integer
Dim z As Integer

'start row variable
sr = Worksheets("Formulas").Range("B2").Value
'per year variable
a = sr
'loop variable
z = Worksheets("Formulas").Range("A2").Value + 1

'Formula
For numclient = 1 To z
If Worksheets("Project Info").Cells(a, 10) = 12 Then
Worksheets("Update Schedule").Cells(a, 2) = "TRUE"
ElseIf Worksheets("Project Info").Cells(a, 10) = 1 Then
Worksheets("Update Schedule").Cells(a, 2) =
"=OR((YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12"
_
& "+((MONTH(Cells(1,2))-MONTH(Worksheets("Project
Info").Cells(a,1)))=0," _
& "YEAR(Cells(1,2))-YEAR(Worksheets("Project
Info").Cells(a,1))*12" _
& "+((Cells(1,2)-MONTH('Worksheets("Project
Info").Cells(a,1)))=12)"
End If
a = a + 1
Next numclient

My problem is with the ElseIf statement using the OR-Function. I
receive a Compile error: Expected: end of statement at this point
...-YEAR(Worksheets("Project Info").Cells(a,1))... The compiler stops
at "Project

Info". I'm having a mental block on how to fix this.

Is there anyone who can help me. Thanks. Kylie

--
kylie
------------------------------------------------------------------------
kylie's Profile: http://www.excelforum.com/member.php...o&userid=35565
View this thread: http://www.excelforum.com/showthread...hreadid=553255

Dear All,
Can someone help me with writing a VBA code for the Dsum function. I basically want to have a VBA formula which can help me write the criteria in the VBA formula itself rather than as reference to another range as it is currently in the Dsum function. I have attached an excel file where I am showing the Dsum function, however, I want to do the same thing, but using a VBA code. The reason is that I have a huge amount of data and I want to avoid writing the Criteria in a separate range of cells and then refer to the range in the Dsum function. Many thanks for your help.

Here is the OR-FUNCTION Formula which works but I need to change formula to use cell property references instead.

If Worksheets("Project Info").Range("$J2").Value = 1 Then
Worksheets("Update Schedule").Range("$B2").Value = "=OR((YEAR(B$1)-YEAR('Project Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=0" _
& "YEAR(B$1)-YEAR('Project Info'!$A2))*12+((MONTH(B$1)-MONTH('Project Info'!$A2)))=12)"

HERE'S THE CODE FOR CHANGING TO USE CELL PROPERTY

I count number of rows in worksheet to find out how may times I must loop through the formulas below in section FORMULAS FOR UPDATE SCHEDULE. This works o'kay.

'COUNTING NUMBER OF CLIENTS ON PROJECT INFO WORKSHEET
Worksheets("Project Info").Activate
Dim r As Integer
Dim row As Range

With ActiveSheet
r = Worksheets("Project Info").Range("S1").Value + 1
Set row = Range(Cells(2, 1), Cells(r, 13))
Range(Cells(2, 1), Cells(r, 13)).Select
Worksheets("Formulas").Range("A2").Value = Selection.Rows.Count
Range("A1").Select
End With

'FORMULAS FOR UPDATE SCHEDULE
Worksheets("Update Schedule").Activate

Dim a As Integer
Dim sr As Integer
Dim z As Integer

'start row variable
sr = Worksheets("Formulas").Range("B2").Value
'per year variable
a = sr
'loop variable
z = Worksheets("Formulas").Range("A2").Value + 1

'Formula
For numclient = 1 To z
If Worksheets("Project Info").Cells(a, 10) = 12 Then
Worksheets("Update Schedule").Cells(a, 2) = "TRUE"
ElseIf Worksheets("Project Info").Cells(a, 10) = 1 Then
Worksheets("Update Schedule").Cells(a, 2) = "=OR((YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12" _
& "+((MONTH(Cells(1,2))-MONTH(Worksheets("Project Info").Cells(a,1)))=0," _
& "YEAR(Cells(1,2))-YEAR(Worksheets("Project Info").Cells(a,1))*12" _
& "+((Cells(1,2)-MONTH('Worksheets("Project Info").Cells(a,1)))=12)"
End If
a = a + 1
Next numclient

My problem is with the ElseIf statement using the OR-Function. I receive a Compile error: Expected: end of statement at this point ...-YEAR(Worksheets("Project Info").Cells(a,1))... The compiler stops at "Project

Info". I'm having a mental block on how to fix this.

Is there anyone who can help me. Thanks. Kylie

Hi everyone!!

I'm new here. I'm from Australia and its a nice sunny day here today in Sydney :-)

I'm low to intermediate level VBA understanding, so I hope to learn from the wizards around this place and improve my VBA knowledge for scientific data analysis purposes.

Anyway, I cannot figure out the correct syntax for this array formula which works perfectly well within the worksheet following CTRL_SHFT_ENTER. In the sub it says "expected: end of statement" and the word "auditory" is highlighted.


	VB:
	
Range("F2").Select 
Selection.FormulaArray = "=AVERAGE(IF($A$11:$A$800="Auditory",IF($B$11:$B$800="CVR LH",$C$11:$C$800,"")))" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all

I need a VBA code for a summation formula. I have a column of values which are calculated by a formula.But the value of the second cell below the 1st cell in the column is a summation of the calculated value in the 1st + 2nd cell of the same column and the 3rd cell below the top is a summation of the 3 calculated values and subsequently for a column of 10 rows.
Each cell in the column uses the same formula with input according to the row in ascending order.

Can any experts out there pls give me some help.Urgent. Thx.

Hi,

What is the Excel 2003 VBA code for a cell formula linked to another excel workbook ?

Active Workbook cell A1 ='C:directory[Prices.xls]Day25'!$A$15

thx
thb

Hi,
I have recently started learning a few bits and pieces with using macros nd VBA code in excel. My question is in regards to the functions you can normally use for cells, such as SUM, SUMPRODUCT, COUNTIF etc. Can these be used in VBA to assign a value to a variable "x", or does the code have to be related to a cell value?

here is an example of what I am tringing to do, I have only assigned the value of "x" to B1 so I can see what the value of x is.

I am used to programing with matlab so if operations like this are possible it would make the learning curve a little easier I think.

Thanks
Elbarto

Sub testing()
Dim x As Integer
'try to use countif function to determine non-zero cells


x = CountIf(Range("A4:A11"), <>0)

Range("B1").Value = x


End Sub


Sometimes in worksheets the formulas can get out of hand, and when you open a
file a while after you last touched it, you can no longer understand some
formulas you have in your worksheets.

Can you please include syntax for cell function parser, so that it is
possible to add any comment text after the last bracket and it'd just ignore
it. You can even have an apostrophe like in VB to avoid inveting a wheel.

Say, such a formula including comment could be valid in a cell:
=(EOMONTH(TODAY(),0)-EOMONTH(TODAY(),-1)) 'counts number of days in a
current month.

Dear Excel helpers,

In my workbook, I have entered a lot of functions mainly VLOOKUPs. I want to write VBA codes for the functions without manually writing them. Is there a way in which I could get the manually written functions converted to codes automatically?

Thank you,

Angela

I am looking for a function, formula or macro that will do the following:

Seach an indefinite number of cells in a column to check if two or more cells equal a specifc value.

Hi,

I would like to know the syntax for checking to see if a cell is a certain amount of characters before a command button's action can be prosecuted.

For example, on Sheet1 A1, users would have to enter a 9 digit ID number. Then they would have to click a button to have that ID number added to a running list on Sheet2. But I want to check, when the button is pressed, to make sure it is 9 digits. Can someone help me?

Thanks

I have a worksheet with 2000 rows.
I want to type my desire row no. in H2 Cell and create a command button 'GoTo' after H9.
I want to jump on (H2*64+4) ; when i click on goto command button.
please tell me VBA Code for this command button.

The following snippet generates an application/object defined error on the second line. Could someone correct the syntax for me?

	VB:
	
 SourceWB = Workbooks.Open(FULL_FILENAME, _ 
False, True) 
[COLOR=Red]ComboItems = SourceWB.Worksheets(1).Range(SourceWB.Worksheets(1) _ 
.Range("B2:"), SourceWB.Worksheets(1).Range("B2").End(xlDown))[/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


2Please help with the syntax for the following:

If the contents in A1 = the contents in A2, put the contents of B2 in D1.

Thanks, ck

Hi all,
i'm a newbie to excel...
i m looking for VBA code for VLOOKUP function anyone can help?
any good website recommend?

pls kindly help! thanks!

Hi,

As per title, I'm trying to populate some formulas from VBA but having problems. This is the UDF and populating sub:

Function GetDateIfValid(DateCell As Range)
On Error GoTo CleanExit
If IsDate(DateCell.Value) And DateCell.Value <= Date And DateCell.Offset(-1, 1) <> "D" Then
    GetDateIfValid = DateCell.Value
    Exit Function
End If

CleanExit:
GetDateIfValid = ""
Application.Calculate
End Function

Sub populateDatesFormulae()
Dim TC As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Range("A2:A60")
    TC = Cells(2, (cell.Row - 1) * 3).Address
    cell.Formula = "=GetDateIfValid(INDIRECT(""'Employee Tracking'!" & TC &
"""))"
Next cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
When I run it I just get the hourglass until I hit escape. It puts the formula into the first cell correctly but it has a '#VALUE!' error and does not continue with the other cells. If I then hit 'calculate' the formula works fine.

edit - I've now found that if I leave the calculation as automatic it will populate all the cells but still hangs afterwards.

Been messing around for a while with this so any suggestions would be gratefully received.

Thanks,

Chinchin

Hello
I am trying to right a macro that if a cell in a range has a formula I want it to change the formula.

I know how to change it if the cell in the range is empty, but don't know the syntax if their is already a formula in place.

This is what I use when he cell is empty
Public Sub Reset()
For Each ce In Range("G8:G15")
If IsEmpty(ce) Then
ce.FormulaR1C1 = "=RC[-1]*R10C1"
ce.Font.ColorIndex = 11
End If
Next
End Sub

I think the line i need to change is "If IsEmpty(ce) Then", but not sure what to??

thanks for any suggestions.

Ok, I have data in several other exel files that I want to link to in several
formulas throughout my spreadsheet.

For example the data in the other worksheets I linking to a

'H:Financials2007January[Plant1 Statistics.xls]Worksheet1'!B$26
'H:Financials2007January[Plant2 Statistics.xls]Worksheet1'!B$26
'H:Financials2007February[Plant1 Statistics.xls]Worksheet1'!B$26
'H:Financials2007February[Plant2 Statistics.xls]Worksheet1'!B$26
'H:Financials2007January[Plant1 salespeople.xls]Worksheet1'!B$26
'H:Financials2007January[Plant1 salespeople.xls]Worksheet2'!B$26

I would like to calculate formulas using this other data, by being able to
use variables for different components of the path name for these files, so
that I can easily change what files the data is being pulled from to populate
a cell.

For example, I'd like to be able to let A1="January", B1="Statistics" and
C1="Worksheet", D1="H:Finanacials2007". E1="Plant1

and then have my formula fill these "variables" into my cell's formula to
use the right path to pull the data in from the correct other worksheet.

So my cell formula(with the correct syntax) might read something like:
='D1A1[E1 B1]C1'!B26

to get the value in: H:Financials2007January[Plant1
salespeople.xls]Worksheet1'!B$26

This example is a little extreme, however I am trying to link to about 50
different worksheets that have consistent paths, formatted worksheet names
and formulas, and I am trying to find out the syntax to use variables within
these paths, rather than having to "hard-code" the different path names
through out my spreadsheet that is accumulating the data.

Any help you could give me would be GREATLY appreaciated!!!

Thanks,
Joan

This is one of those mundane little activities I found myself doing frequently enough that I finally just had to automate it.

When working in VBA I frequently need to range enter a formula across multiple cells. It's not particularly difficult to convert the cell-based formula to the appropriate VBA R1C1 format and adjusting for double quotes if necessary, but if you do it enough, it gets old; especially when converting formulas with lots of double quotes.

So, here's the new gadget I just added to my personal macro toolbar that will convert the activecell formula to the proper VBA text string, and write it to the clipboard so I can then just paste it in my code.

I've even seen some questions posted here along the lines of, "What's the proper syntax for inputting my formula in VBA so the range refs are incremented properly?" This pretty much instantly answers that type of question.

Simple little code, but I find it rather helpful.


	VB:
	
 
 
Sub Get_VBA_Formula() 
     
    Dim VBA_Formula As String, n$, x$ 
    Dim i As Integer 
    Dim MyData As DataObject 
     
    VBA_Formula = ActiveCell.FormulaR1C1Local 
     
     'double quote substitution
    For i = 1 To Len(VBA_Formula) 
        n$ = Mid(VBA_Formula, i, 1) 
        If n$ = """" Then 
            x$ = x$ & """""" 
        Else 
            x$ = x$ & n$ 
        End If 
    Next i 
     
     'post formula to clipboard
    Set MyData = New DataObject 
    VBA_Formula = """" & x$ & """" 
    MyData.SetText VBA_Formula 
    MyData.PutInClipboard 
     
     'confirmation
    MsgBox VBA_Formula, vbOKOnly, "VBA Formula Copied to Clipboard" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I believe it works for any formula (array or not). If you can think of a character other than the double quote that I may not be accounting for properly in VBA, or some other issue I've overlooked, please add your comments to the thread.

Anyone know the correct syntax for making a DDE cell?

When I Paste Special (link) from my DDE application, it puts curly brackets {} around the formula. However if I manually edit it, the brackets disappear (although it still works just fine). If I try to manually put the backets back in there, it treats the whole cell as a string.

The reason I ask is that I am trying to put some more complex DDE links in that I cannot cut and paste from the DDE server application, and they don't appear to be working at all. However they do have some special characters in that Excel complains make it an invalid formula. This is how I beleive the link needs to look:

=DeTransfer|Command![ExecuteMacro]


No luck finding an answer? You could always try Google.