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