Using Index match in VBA returns wrong value

[purple_ninja]

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 value for the Activecell(-1,-3) rather than the Activecell(0,-3)

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")




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

[Bob Phillips]

You are missing an offset. Activecell(0,3) is the same as Activecell.Offset(-1,2) not ACtivecell.Offset(0,3)


Sub updated_tech_dept()
Application.ScreenUpdating = False
Worksheets("MODTRACKER DATA").Select
Range("AF2").Select

With ActiveCell

Do While .Offset(0, -12).Value <> ""
.Value = Application.Index(Sheets("Total Hours Booked").Range("L2:L2000"), Application.Match(.Offset(0, -3), Sheets("Total Hours Booked").Range("A2:A2000"), 0))
.Offset(1, 0).Select
End With
MsgBox ("All Complete")
Application.ScreenUpdating = True
End Sub

[purple_ninja]

Ah!!!! Thank you so much!! it's all working now - albeit very slowly (I thought replacing the formulas in the cells would improve the processing speed but it would appear not )

Out of curiosity why is Activecell(0,3) the same as Activecell.offset(-1,2)?

[Bob Phillips]

Activecell(0,3) is actually Activecell.Cells(0,3). Activecell is Activecell.Cells(1,1), so (0,3) is 1 row less, two columns more than the Activecell, which is exactly what Activecell.Offset(-1,2) is

[Bob Phillips]

BTW, to speed it up, you could load the formula into those cells then copy/paste values.

[purple_ninja]

Ah that might do the trick! Any hints on how I might do that?

[Bob Phillips]

Like this


Sub updated_tech_dept()
Dim lastrow As Long

Application.ScreenUpdating = False

With Worksheets("MODTRACKER DATA").Range("AF2")

With .Offset(0, -12)

lastrow = .End(xlDown).Row

With .Resize(lastrow - .Row + 1)

.FormulaR1C1 = "=INDEX('Total Hours Booked'!R2C12:R2000C12,MATCH(RC[3],'Total Hours Booked'!R2C1:R2000C1,0))"
.Value = .Value
End With
End With
End With

MsgBox ("All Complete")
Application.ScreenUpdating = True
End Sub

[purple_ninja]

Crikey that's fast!

While it's great that it works can you walk me through the formula so that I can understand and replicate it in other columns? My understanding is as follows:



Sub resource_pool_Check()
Dim lastrow As Long 'declares the variable that holds the value for the last row

Application.ScreenUpdating = False 'Switches off screen updating

With Worksheets("MODTRACKER DATA").Range("AB2") 'selects the start range AB2 on worksheet MODTRACKER DATA

With .Offset(0, -12) 'checks for the last row of 12 columns to the left of the active cell

lastrow = .End(xlDown).Row

With .Resize(lastrow - .Row + 1) 'not sure what this does?

.FormulaR1C1 = "=INDEX('Total Hours Booked'!R2C12:R2000C12,MATCH(RC[3],'Total Hours Booked'!R2C1:R2000C1,0))" 'enters the formula into which cell? AB2? what does Match(RC[3] mean?
.Value = .Value
End With
End With
End With

MsgBox ("All Complete")
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Sub resource_pool_Check()
Dim lastrow As Long 'declares the variable that holds the value for the last row

Application.ScreenUpdating = False 'Switches off screen updating

With Worksheets("MODTRACKER DATA").Range("AB2") 'selects the start range AB2 on worksheet MODTRACKER DATA
'[RP] no it doesn't select, there is no need to select it, selecting is very slow. _
It just sets a pointer your starting position - this is to avoid repeatedly referencing the cell

With .Offset(0, -12) 'checks for the last row of 12 columns to the left of the active cell
'[RP] again that is setting a pointer 12 cells left - we could actually roll the two With statements into a singleton, I prefer two

lastrow = .End(xlDown).Row
'[RP] Calculate the lastrow of your data in column P (i.e. 12 columns left of AB), which is referenced in the previous two With statements

With .Resize(lastrow - .Row + 1) 'not sure what this does?
'[RP] Our previous With statemnents just referenced P2, but we want to work on all the data ows. We know where it ends, lastrow, _
so we set a reference to all of those rows (e.g. if lastrow is 27, our start cell is P2, we are resing our range by 27-2+1=26 rows, _
so we will affect all 26 cells at one

.FormulaR1C1 = "=INDEX('Total Hours Booked'!R2C12:R2000C12,MATCH(RC[3],'Total Hours Booked'!R2C1:R2000C1,0))" 'enters the formula into which cell? AB2? what does Match(RC[3] mean?
'[RP] No into P2:P<lastrow-row+1> _
RC[3] is looking at the current row 3 cells right. We could have used A1 notation of "=INDEX('Total Hours Booked'!L2:L2000,MATCH(S2,'Total Hours Booked'!A2:A2000,0)) _
I just prefer the flexibility of R1C1 notation
.Value = .Value
'[RP] then we effectively do a copy>paste values
End With
End With
End With

MsgBox ("All Complete")
Application.ScreenUpdating = True
End Sub

[purple_ninja]

Thanks so much :) Now to work out how to put in more complicated formulas! It's working beautifully :)

[snb]

Sub M_snb()
sn = Sheets("MODTRACKER DATA").Columns(29).SpecialCells(2)
sp = Sheets("Total Hours Booked").Range("A2:L2000")

For j = 2 To UBound(sn)
For jj = 1 To UBound(sp)
If sn(j, 1) = sp(jj, 1) Then
sn(j, 1) = sp(jj, 12)
Exit For
End If
Next
Next

Sheets("MODTRACKER DATA").Range("AF1").Resize(UBound(sn)) = sn
End Sub

[purple_ninja]

I have no idea what that code is doing :(

Continuing along the inserting a formula theme... I've got the code working for just index matches and for the example I gave previously... the issue I'm getting now is an Error 13 when I try and add in a more complicated formula :

IF(AND(ISNUMBER(SEARCH("*Offshore*",AH:AH)),ISNUMBER(SEARCH("*Electric*",AE:AE))),INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0)),IF(AND(ISNUMBER(SEAR CH("*Offshore*",AH:AH)),ISNUMBER(SEARCH("*NPD*",CTYPE))),CONCATENATE("NPD - ",INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0))),IF(AND(ISNUMBER(SEARCH("*Offshore*",AH:AH)),ISNUMBER(SEARCH("*CME",CTYPE))),CONCATENATE("CME - ",INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0))),INDEX('Total Hours Booked'!R:R,MATCH(RPCHECK,RPNAME,0)))))

I know the " need to be changed to ""

I can get the first part of the code to work if I put in the value to enter rather than looking it up

IF(AND(ISNUMBER(SEARCH("*Offshore*",AH:AH)),ISNUMBER(SEARCH("*Electric*",AE:AE))),"Electric")

Have I missed or misplaced a bracket??

[Bob Phillips]

Did you change the cell property to Formula rather than FormulaR1C1?

[purple_ninja]

Did you change the cell property to Formula rather than FormulaR1C1?

I'm not sure how but I got it all working - Thanks so much for your assistance - you've helped me reduced processing time on my worksheet and secured all my formulas in VBA so no one can mess with them when I'm out of the office!