• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FOR loop Index / Match with OFFSET search value

JCTalk

Member
Hi Everyone,

I'm having a hard time pinpointing what I'm doing wrong in this little chunk of VBA I'm trying to write. I want to use a FOR loop and an OFFSET value as the search string, but everything I've tried results in blank cells.

Here's my chunk of code...

Code:
For Each c In .Range("$N" & nextrow & ":$N" & lastrow)
    c.Value = WorksheetFunction.Index(Sheets("Lookup").Range("B:B"), WorksheetFunction.Match(c.Offset(0, -10).Value, Sheets("Lookup").Range("A:A"), 0))
Next c
I did read that OFFSET couldn't be used in WorksheetFunctions, so I also tried the recommended alternative c.Resize(, -10), but again nothing.

I think it's definitely just the OFFSET part that isn't working, because if I replace that with a static value, the index / match works fine. But I can't use it like that, it needs to be a cell in a column -10 columns to c's left every loop.

Can anyone please help show me where I'm going wrong on this OFFSET part, it's absolutely breaking me. I've been stuck on this for a couple of days.

Many thanks in advance for any help you can offer.
 

p45cal

Well-Known Member
I did read that OFFSET couldn't be used in WorksheetFunctions
Where did you read that? It can be.
A problem with using .Worksheetfunction in VBA is that if that function would return an error (in this case an exact match is not found) then that function by itself will cause VBA to throw an error. If instead of using Worksheetfunction, you substitute it for Application, the function will still throw an error, but not stop vba, but put an error into the variable (cell in this case).
So make that line:
Code:
c.Value = Application.Index(Sheets("Lookup").Range("B:B"), Application.Match(c.Offset(0, -10).Value, Sheets("Lookup").Range("A:A"), 0))
If you want to do something else if an exact match is not found you can do this sort of thing:
Code:
cValue = Application.Index(Sheets("Lookup").Range("B:B"), Application.Match(c.Offset(0, -10).Value, Sheets("Lookup").Range("A:A"), 0))
If IsError(cValue) Then c.Value = "Goofed up" Else c.Value = cValue
 

JCTalk

Member
So make that line:
Code:
c.Value = Application.Index(Sheets("Lookup").Range("B:B"), Application.Match(c.Offset(0, -10).Value, Sheets("Lookup").Range("A:A"), 0))
Thank you @p45cal that's done the trick! :D

I must admit I'm struggling to see why the version with Worksheetfunction produced all blanks though. There are one or two none matches resulting in an #N/A or two, but why does that stop those that did match from displaying. As its a loop, shouldn't those that match successfully be displayed then just the error's produce a blank? I guess I'm thinking of it as when I'm using the debug mode to step through it. It's approaching one iteration at a time. Or when it's executed does it just not work like that... so if there is one error brought back, none of them are populated, match or not?

Regarding where I found "OFFSET can't be used with Worksheetfunction". TBH I don't know. I've spent that long Googling the problem over the last few days it could have come from anywhere. :D

I'm very happy to have the problem solved though, so thank you @p45cal. I'd just like to understand the above as well if you have time to give your take on it?

Many thanks
 

JCTalk

Member
No thats OK @p45cal. As I say it is working fine now with your suggested amendment of Application.Index and Application.Match. I was just trying to rap my head around why it wasn't working at all with Worksheetfunction.

Many thanks for your help. I consider this query closed.
 
Top