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(SEARCH( "*Offshore*",AH:AH)),ISNUMBER(SEARCH("*NPD*",CTYPE))),CONCATENATE("NPD - ",INDEX(TECHDEPTALIAS,MATCH(RPCHECK,RPNAME,0))),IF(AND(ISNUMBER(SEARCH("*Of fshore*",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!