VBA index/match blank cell

Question

I have a list of accounts & codes, and would like to populate a column with results from a different sheet using index match. I can get it to work using the formula: =index(rngB,match(BCode,rngM,0),55)

but can't translate it to vba. I have tried:

sub compare() 

Dim BudgetResult As Long
Dim var1 As Long
Dim rngB, rngM As Range
Dim CompSH, ActSH, BudSH As Worksheet
Dim BCode As Variant

Set CompSH = Sheets.Add(After:=Sheets(Sheets.Count))
Set ActSH = Sheets(2)
Set BudSH = Sheets(3)
Set rngB = BudSH.Range("B11:BF50")
Set rngM = BudSH.Range("B:B")
Set BCode = CompSH.Range("A2")

BudSH.Select
Range("B10:E76").Select
Selection.Copy
CompSH.Select
ActiveSheet.Paste
Range("F1").Select
ActiveCell.FormulaR1C1 = "Budget"
Range("F2").Select

With Application.WorksheetFunction

var1 = .Match(BCode, rngM, 0)
BudgetResult = .Index(rngB, var1, 55)

End With

I get a blank cell. no result in the sheet. Also, I don't know how to continue it down. Can anyone help?

are you set on translating the Excel Formula into VBA ? if you already decided to use VBA, why not use it's extra capabilities ? — Shai Rado

user3598756 user3598756 · Accepted Answer · 2017-02-07T08:45:48

you may be after something like follows

Option Explicit

Sub compare()
    Dim rngB As Range, rngM As Range, cell As Range
    Dim CompSH As Worksheet, ActSH As Worksheet, BudSH As Worksheet
    Dim AW As WorksheetFunction: Set AW = Application.WorksheetFunction

    Set CompSH = Sheets.Add(After:=Sheets(Sheets.count))
    Set ActSH = Sheets("ActSH") 'Sheets(2)
    Set BudSH = Sheets("BudSH") 'Sheets(3)

    With BudSH
        Set rngB = .Range("B11:BF50") '<--| warning: your "index" range has 40 rows
        Set rngM = .Range("B:B")
        .Range("F1").Value = "Budget"
        .Range("B10:E76").Copy CompSH.Range("A1") '<--| warning: your "copied" range has 67 rows
    End With

    With CompSH
        For Each cell In .Range("A2", .Cells(.Rows.count, 1).End(xlUp))
            cell.Offset(, 5).Value = AW.Index(rngB, AW.Match(cell, rngM, 0), 55) '<--| this will error when trying to access from 'rngB' range 41th rows on
        Next
    End With
End Sub

where you only have to adjust the range sizes in the statements marked with <--| Warning...

VBA index/match blank cell

1 Answers