Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

worksheetfunction with match

0

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?

excel
MS Office Version: Office 365
posted by: baddog1016 (rep: 12) on Fri Jul 31, 2020 at 11:27 pm
Answer
Discuss

Answers

0
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.

posted by: don (rep: 1989) on Sat Aug 1, 2020 at 4:13 am
Discuss

Discussion

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: 12) 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: 1989) 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