Match function

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!

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

Quote:

---

Originally Posted by xld

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

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.

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]