Application.WorksheetFunction.Match

[mulholm]

Option Explicit


Sub TeamTotal()
Dim lngRow As Long

With Sheet1
'find "Team Total" position
lngRow = Application.WorksheetFunction.Match("Team Total", .Range("A:A"), 0)
'apply the sum formula
.Range("B" & lngRow).Formula = "=SUM(B1:B" & lngRow - 1 & ")"
End With


End Sub



is there any way to change this so that it runs the search on sheet2 but command button is on sheet1?

I have tried simply changing "With sheet1" to "With sheet2" but no joy.

 

[steve the fish]

Are you saying it makes no difference if you change With Sheet1 to With Sheet2?? It does. But it will then place your formula on Sheet2 also. So instead of just using the period you can say Sheet2.Range("A:A")

 

[mulholm]

Private Sub TeamTotal()

Dim lngRow As Long

With Sheet2

'find "Team Total" position
lngRow = Application.WorksheetFunction.Match("Team Total", .Range("A:A"), 0)

'apply the sum formula
.Range("B" & lngRow).Formula = "=SUM(B3:B" & lngRow - 1 & ")"
.Range("C" & lngRow).Formula = "=SUM(C3:C" & lngRow - 1 & ")"
.Range("E" & lngRow).Formula = "=SUM(E3:E" & lngRow - 1 & ")"

End With


End Sub



I have changed it to the above code and it debugs highlighting the bolded section.

i want the formula to only appear in sheet 2 instead of sheet 1 but the button to trigger the formula is on sheet 1

 

[steve the fish]

And the error is? 'Unable to get.....'? If so then Team Total is not found in column A of Sheet2.

 

[mulholm]

Got it .. thanks for your help.