VBA If Match Then

[hcparsons12]

Hey everyone,

Keep in mind that I am very new to VBA, but not Excel. I've been attempting to learn VBA and apply it to my projects only over the past week or so. Currently, I am trying to write a code that searches across the table headers in a "Per Game" Sheet for a match to a changing reference in the "Player Search" Sheet. I have gotten the loops to work in that the macro runs through the correct rows or columns in each Sheet until it finds a match, but I cannot figure out how to tell the macro what to do when it finds said match. When it finds the match in the "Per Game" Sheet, I need it to take the average of the 540 rows beneath it. Then, reset and move on to the next match and do the same until reaching the bottom of "J5:J24" range, where the value of the averages go, in the "Player Search" Sheet. I'll attach a picture of my code. When I run it, I usually get a "424" error for object required.

 

[johnnyL]

Since you are just starting to learn vba, let's fix your Dim statements.

There are a few lines that are not doing what I think you think they are doing. You are allowed to have more than one on each line but you have to declare each one of them you can't just declare the last one on the line and expect all the previous ones on the line to be declared the same.

For example: You have:

Dim rge, avg, sol, cell As Range

rge, avg, and sol are, by default, variants until you specifically declare them otherwise.

Same with:

Dim ws1, ws2 As Worksheet

ws1 is still a variant

If you want to properly combine those statements:

Dim ws1 As Worksheet, ws2 As Worksheet

 

[hcparsons12]

Since you are just starting to learn vba, let's fix your Dim statements.

There are a few lines that are not doing what I think you think they are doing. You are allowed to have more than one on each line but you have to declare each one of them you can't just declare the last one on the line and expect all the previous ones on the line to be declared the same.

For example: You have:

Dim rge, avg, sol, cell As Range

rge, avg, and sol are, by default, variants until you specifically declare them otherwise.

Same with:

Dim ws1, ws2 As Worksheet

ws1 is still a variant

If you want to properly combine those statements:

Dim ws1 As Worksheet, ws2 As Worksheet

I actually originally had them all separate, but changed it because I thought I was saving room. Changed them back but I still get Run Time Error 91 with the debug pointing to the rge = Application.Worksheet... line.

 

[JackDanIce]

avg is read as a variant, but you're trying to set a range object to it which I don't believe is possible.

I think you mean:

Dim avg as Range
Set avg = rge.Offset(1, 0).Resize(540,1_

If you're new to VBA, don't try to run before you can walk.

If you don't understand what variables are, how to declare them, what the implied syntax is then "saving space" it's going to trip you up.

You're trying to force a round hole into a square peg in the line of code that's erroring

 

[hcparsons12]

avg is read as a variant, but you're trying to set a range object to it which I don't believe is possible.

I think you mean:

Dim avg as Range
Set avg = rge.Offset(1, 0).Resize(540,1_

If you're new to VBA, don't try to run before you can walk.

If you don't understand what variables are, how to declare them, what the implied syntax is then "saving space" it's going to trip you up.

You're trying to force a round hole into a square peg in the line of code that's erroring

I just tried this, but I am still getting the error on the "rge =" line. I took some VBA classes years ago so felt confident enough to jump back in, but I was mistaken. I'm a fast learner and need this for my project to work.

 

[Alex Blakenburg]

Try this (including changing your Dim rge statement.

    Dim rge As Range
    Set rge = WorksheetFunction.Index(Range("H1:AD1"), WorksheetFunction.Match(ref, Range("H1:AD1"), 0))

rge needs to be defined as range
a range needs a "Set" in the assignment statement.
Match only returns a number, Index will return the actual Cell (range object)

 

[Marc L]

Hi,​

​

as per its type any Variant variable can be used as an object without any issue, just must be initialized with the Set statement …​

 

[hcparsons12]

Try this (including changing your Dim rge statement.

    Dim rge As Range
    Set rge = WorksheetFunction.Index(Range("H1:AD1"), WorksheetFunction.Match(ref, Range("H1:AD1"), 0))

rge needs to be defined as range
a range needs a "Set" in the assignment statement.
Match only returns a number, Index will return the actual Cell (range object)

Thank you so much for this. It has me so so close. If I comment out my "Set avg" statement beneath this as well as the "Dim avg" statement above, I do get a return of the correctly matched cell where I need it. What I need is the average of the cells beneath it (which i try to find using the offset and resize functions as you can see). My problem right now is that I am getting a type mismatch error on the WorksheetFunction.Average. Suggestions? Thank you so much. Here's where I am at:

 

[hcparsons12]

Thank you so much for this. It has me so so close. If I comment out my "Set avg" statement beneath this as well as the "Dim avg" statement above, I do get a return of the correctly matched cell where I need it. What I need is the average of the cells beneath it (which i try to find using the offset and resize functions as you can see). My problem right now is that I am getting a type mismatch error on the WorksheetFunction.Average. Suggestions? Thank you so much. Here's where I am at:
View attachment 42087

Was thinking the avg variable may need to be declared as a Double, but I don't know how that affects the formatting.

 

[johnnyL]

Double is correct declaration. Remove the 'Set' portion from your 'avg = ' line