PDA

View Full Version : Match function



frubeng
05-19-2009, 05:07 AM
Hello, I am using the function:

Application.WorksheetFunction.Match("Total", Range(Cells(i, 1),Cells(i, 1)), 0)

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
05-19-2009, 05:20 AM
That range is just a single cell, so using Match is pointless, you could just compare its value against your target value.

frubeng
05-19-2009, 05:30 AM
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
05-19-2009, 05:35 AM
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
05-19-2009, 06:05 AM
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
Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 1), Cells(row2 + 20, 1)), 0)

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


For your reference, I've attached my code:
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