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

=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

- Crystal Syntax for Custom Functions
- Correct VBA syntax
- Basic Syntax for Custom Functions
- Syntax for Accepting Worksheet Formulas in VB
- VBA Code For OR-Function Using Cell Property
- Writing a VBA code for Dsum function
- VBA Code For OR-Function Using Cell Property
- VBA Code To Enter Array Formula
- VBA : VBA code for summation formula
- The XL2003 VBA code for cell link to another workbook ?
- VBA using standard cell functions
- Comments for functions
- Convert Functions/Formulas to VBA Code
- Function, formula or macro
- Macro Syntax for cell character checking
- Vba code for cell reference
- Syntax for selecting all rows
- What is the syntax for the IF formula in Excel?
- What is the VBA code for VLOOKUP?
- Using VBA to populate cells with formulas containing user-defined functions
- VBA Syntax for If cell is a Formula
- Syntax for using variables in a cell formula to reference paths/fi
- Cell Formula to VBA Tool
- Importing: Syntax for making a DDE reference cell

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

cell when the row number and column number are known?

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.

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

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.

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

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

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.

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

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

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.

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

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

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 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.

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

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

Thanks, ck

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!

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 SubWhen 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

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.

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

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: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.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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]