Converting Excel Formula to VBA Code With Dynamic Reference

Question

I have in my code variable named MyCol that gets the number of the column by month that was selected in a userform. for example October is in column U and November is in column V. I have a formula that I recorded and the month that was chosen is part of it but it is a problem because RC format is with specific reference and my variable is an integer.

I want that the formula will be dynamic.

This is the formula (column U means month that was chosen):

=IFNA(IF(VLOOKUP(AA2,sheet1!F:F,1,0)=AA2,U2,0),0)

That is the relavant part of the code:

Dim MonthName As String
Dim myCol As Integer

MonthName = ListMonth.Value

With MainWB.Worksheets("sheet2")
    .Activate
    .Range("L1:W1").Find(MonthName, , xlValues, xlWhole).Activate
End With
ActiveCell.Select
myCol = Selection.Column
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
    "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[" & myCol & "],0),0)"
Range("AB2").AutoFill Destination:=Range("AB2:AB" & MLR), Type:=xlFillDefault

Shai Rado · Accepted Answer · 2017-11-12 09:49:34Z

3

Replace your :

Range("AB2").Select
ActiveCell.FormulaR1C1 = _
    "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[" & myCol & "],0),0)"

With:

Range("AB2").FormulaR1C1 = _
            "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[-" & Range("AB2").Column - myCol & "],0),0)"

However, if you want to make your code run faster, and also avoid all the unecessary Activate, ActiveCell, Select, try the code below:

Dim FindRng As Range

MonthName = ListMonth.Value

With MainWB.Worksheets("sheet2")
    Set FindRng = .Range("L1:W1").Find(MonthName, , xlValues, xlWhole)
End With

If Not FindRng Is Nothing Then
    myCol = FindRng.Column
Else ' find was not successful finding the month name
    MsgBox "Unable to find " & MonthName, vbCritical
    Exit Sub
End If

Range("AB2").FormulaR1C1 = _
            "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[-" & Range("AB2").Column - myCol & "],0),0)"

answered Nov 12, 2017 at 9:49

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ashleedawg Over a year ago

- oops I flagged this because I mistakenly thought the OP was the same person who posted the answer. I'm sure the mod who sees the flag will ignore it. Sry, instead I'll +1. :)

Collectives™ on Stack Overflow

Converting Excel Formula to VBA Code With Dynamic Reference

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related