orangebloss
Board Regular
- Joined
- Jun 5, 2013
- Messages
- 51
- Office Version
- 365
- Platform
- Windows
Hi
I thought I'd managed to work out how to do an index match in VBA and it does work...to an extent.... For some reason it's returning the index value for the Activecell(-1,-3) rather than the Activecell(0,-3) and I'm getting a lot of #N/A values even when the data is available
I've tried named ranges, using the whole column, specifying a subset of the data to check but still getting the error. Can anyone suggest why this might be happening? Or a better way to produce the Index match?
VBA code is below but what I'm trying to replicate is the following Index match statement
=IF(TDATE>0,INDEX(TECHDEPT,MATCH(RPCHECK,RPNAME,0)),"")
where
TDATE = Activecell.offset(0,-12)
TECHDEPT= Sheets("Total Hours Booked").Range("L:L")
RPCHECK = Application.match(ActiveCell(0, -3)
RPNAME= Sheets("Total Hours Booked").Range("A:A")
Thanks in advance
I thought I'd managed to work out how to do an index match in VBA and it does work...to an extent.... For some reason it's returning the index value for the Activecell(-1,-3) rather than the Activecell(0,-3) and I'm getting a lot of #N/A values even when the data is available
I've tried named ranges, using the whole column, specifying a subset of the data to check but still getting the error. Can anyone suggest why this might be happening? Or a better way to produce the Index match?
VBA code is below but what I'm trying to replicate is the following Index match statement
=IF(TDATE>0,INDEX(TECHDEPT,MATCH(RPCHECK,RPNAME,0)),"")
where
TDATE = Activecell.offset(0,-12)
TECHDEPT= Sheets("Total Hours Booked").Range("L:L")
RPCHECK = Application.match(ActiveCell(0, -3)
RPNAME= Sheets("Total Hours Booked").Range("A:A")
Code:
Sub updated_tech_dept()
Application.ScreenUpdating = False
Worksheets("MODTRACKER DATA").Select
Range("AF2").Select
Do While ActiveCell.Offset(0, -12).Value <> ""
ActiveCell.Value = Application.Index(Sheets("Total Hours Booked").Range("L2:L2000"), Application.match(ActiveCell(0, -3), Sheets("Total Hours Booked").Range("A2:A2000"), 0))
ActiveCell.Offset(1, 0).Select
Loop
MsgBox ("All Complete")
Application.ScreenUpdating = True
End Sub
Thanks in advance