worksheetfunction with match


I am writing a macro to score an 11 race series with 5 different classes. I have it working with one class using: 

For i = 3 To 3 + X                     'this loop scores boats that raced

boatname = Sheets(ws_RaceX).Cells(i, 4) '

test = WorksheetFunction.Match(boatname, Range("D3:D13"), 0) 'this line is telling   'where in D3:D13 boat name is found

Sheets("Cumulative").Cells(test + 2, next_col).Value = i - 2

 Next i

I am hoping to make this a subroutine but to do that "D3:D13" needs to be a variable. is there a way to do this?



Selected Answer

If you mean to send the range to a macro, you would write the macro (sub) like this:

Sub myCoolMacro(myRange As Range)

    ' Sample code.
    MsgBox myRange.Count

End Sub

Now, myRange will hold the range that is supplied to it.

To call this macro you can do something like this:

Call myCoolMacro(Range("A1:C5"))

Call the macro from other code wherever you need it.

I hope I understood your question correctly.



I'm not sure I follow. how my code works is it matches the boat name on the cumulative sheet to the race results sheet. The boat that came in first is matched when "i" =1, 2nd place when "1" = 2, etc. the key line for doing this is the one I highlighted which works well for one class. In order for it to work for another class the range in that line needs to change by a calculated amount. I willl play with the "myrange" funtion and see what happens.
test = WorksheetFunction.Match(boatname, Range("D3:D13"), 0
baddog1016 (rep: 10) Aug 1, '20 at 7:39 am
It sounds like you need to just learn how to make dynamic range references in VBA. There are Many ways to do this and the easiest one to use depends on your calculation. Edit your question and include how you want to change the range specifically and it will probably take 1 or 2 lines of code to get it to work.
don (rep: 1835) Aug 3, '20 at 2:20 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login