Match function

[frubeng]

Hello, I am using the function:

[VBA]Application.WorksheetFunction.Match("Total", Range(Cells(i, 1),Cells(i, 1)), 0)[/VBA]

To find certain cells in my sheet that contains:
April Total, May Total and so on. Is there a way I can find the first one, then simply move on to the next one?

I need to make a few things happen around the cells where these totals are found (and the user will write more total cells as the months go by.)

Thanks!

[Bob Phillips]

That range is just a single cell, so using Match is pointless, you could just compare its value against your target value.

[frubeng]

That range is just a single cell, so using Match is pointless, you could just compare its value against your target value.

Yeah, I used a larger range in the code (but tried a few different things afterwards...)
How would I compare, because my cell contains more than just total (it has may total, june total etc.)?

Thanks

[Bob Phillips]

So doesn't Match just find the first one already? Then just resize the range as big as you want.

I think a lot more detail is required, we are groping in the dusk here at what you are trying to do.

[frubeng]

Well I have a sheet that has

date sum
04/12 30

april Total 30

05/01 40
05/12 30
05/15 38

May Total 108


But I want to automate a row insert is someone writes a new entery for May for example, such that there will always be a blank line above May total (and same for april total).
As per your advice, I have a loop that checks for the word Total, does stuff and then stores where that row as row2.

Then I use
[vba]Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 1), Cells(row2 + 20, 1)), 0)[/vba]

But it gives me Unable to get the Match property of the worksheetfunction class (error 1004).


For your reference, I've attached my code:
[vba]Sub main()
sumtotal = 0
For i = 1 To Month(Now) - 3
row2 = Row
Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 1), Cells(row2 + 20, 1)), 0)
If IsEmpty(Workbooks("BSW-Commisions").Spreadsheets("FBCO").Cells(Row - 1, 1)) Then
Workbooks("BSW-Commisions").Spreadsheets("FBCO").Cells(Row - 1, 1).RowInsert
End If
'Summing all the comms for the monthly total
For j = 1 To Row - row2 - 1
Sum = Sum + Workbooks("BSW-Commisions").Spreadsheets("FBCO").Cells(row2 + j, 4)
Next j
Workbooks("BSW-Commisions").Spreadsheets("FBCO").Cells(Row, 4) = Sum
sumtotal = sumtotal + Sum
Next i
longrow = Application.WorksheetFunction.Match("Annual", Range("a1:z1"), 0)
longcol = Application.WorksheetFunction.Match(0, "Annual", Range("a1:z1"))
Workbooks("BSW-Commisions").Spreadsheets("FBCO").Cells(longrow - 1, longcol) = sumtotal

End Sub
[/vba]