Solved How to add dynamic range to a vlookup formula using VBA code

February 7, 2018 at 10:52:51
Specs: Windows 7
Hi All,

Can you please share how to incorporate dynamic range in the vlookup function using VBA macros.

I am in a similar situation where I want vlookup summary range to be dynamic as my data range changes from supplier to supplier.

this is my current vlookup formula;

"=VLOOKUP(RC[-15],Summary!R1C1:R321C29,MATCH(RC[-8],Summary!R1C1:R1C29,0),FALSE)"

here instead of taking a specific data range for the summary range element of Vlookup, I want to apply dynamic range formula.

Can anyone give example for both the situations when the column is static and not static?


thanks for any help
-V

message edited by Vm


See More: How to add dynamic range to a vlookup formula using VBA code

Reply ↓  Report •

#1
February 7, 2018 at 12:38:24
✔ Best Answer
Have you considered using a Dynamic Named Range?

A Dynamic Named Range changes it's "Refers to" value as the size of the range changes.

Here is just one of many links that discuss Dynamic Named Ranges. A Google search will show many more.

http://www.excel-easy.com/examples/...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
February 7, 2018 at 13:08:12
Hello

@DerbyDad03

thank you for the response.

Can you please elaborate how the dynamic name range will be useful in the scenario I have explained above?

Do you mean that I define the dynamic name range and then re-record the macro so that VBA creates a code and includes dynamic named range as the summary range?

Below is the current code I have created

Sub ValidationFormulasSpends()
'
' ValidationFormulasSpendTracker Macro
' Groups chains, concatenates and adds the validation formulas
'

'

Dim LastRow As Long

Worksheets("Spends").Select

Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Unique Identfier"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Grouping values"
Range("A1:B1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]=""VONS"",""ALBERTSONS INC"",RC[1])"
Range("B2").Select
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("B2:B" & LastRow).FillDown
Range("A2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[3])"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A2:A" & LastRow).FillDown
Cells.Select
Cells.EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("O1").Select
ActiveCell.FormulaR1C1 = "Monthly Total Spends"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Total Spends"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Match"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Variance"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-2],C[-14],RC[-14],C[-7],RC[-7])"
Columns("O:P").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O1656")
Range("O2:O1656").Select
Range("P2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-15],Summary!R1C1:R321C29,MATCH(RC[-8],Summary!R1C1:R1C29,0),FALSE)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("P2:P" & LastRow).FillDown
Range("Q2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=EXACT(RC[-2],RC[-1])"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("Q2:Q" & LastRow).FillDown
Range("R1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]"
Range("R2").Select
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("R2:R" & LastRow).FillDown
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("A1").Select
End Sub

thank you
V



Reply ↓  Report •

#3
February 7, 2018 at 19:07:23
First, let me offer a couple of tips:

1 - Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

2 - Recorded code is really bloated and cumbersome to read. You should learn how to clean it up to eliminate all of the useless instructions that the macro recorder creates. Not only will your code be more efficient, but you'll learn a lot about writing VBA code so that you don't have to rely on the recorder.

For example, look at all of the times your code Selects a cell. There are very few occasions when VBA needs to select a cell (or Range) in order to perform an action on it.

Every pair of instructions that looks like this:

Range("O2").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-2],C[-14],RC[-14],C[-7],RC[-7])"

can be shorten to this:

Range("O2").FormulaR1C1 = "=SUMIFS(C[-2],C[-14],RC[-14],C[-7],RC[-7])"

One instruction and no "physical selection" of the cell when the code is running.

Text entries don't need the FormulaR1C1 notation. Just set the cell equal to the text value:

Range("Q1") = "Match"

Look at all the times you have wasted instructions such as:

Range("O2:O1656").Select
Range("P2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-15],Summary!R1C1:R321C29,MATCH(RC[-8],Summary!R1C1:R1C29,0),FALSE)"

All of that can be shorten to one instruction:

Range("P2").FormulaR1C1 = _
"=VLOOKUP(RC[-15],Summary!R1C1:R321C29,MATCH(RC[-8],Summary!R1C1:R1C29,0),FALSE)"

All of the ActiveWindow.ScrollColumn instructions appear to serve no purpose.

I say all this because it's really hard to read recorded code because of all the bloat. When you ask someone to help you with your code, you are more likely to get help if the code is easier to read and we don't have to dig through all the bloat.

Why don't you clean up your code to the best of your ability (we're not asking for perfection, you'll get better as time goes along) and then repost it after reading the following How-To link? In the meantime, I'll see if I can come up with a solution to your question.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
February 8, 2018 at 05:41:18
While I would still like to see a repost of your code after it has been cleaned up and posted with the pre tags (per the How-To link below) I believe that I have come up with a solution. Obviously it would need to be tested with your data set to ensure that it will work for you. There are multiple ways to accomplish your goal via VBA, but most of them can't be accomplished via the recorder.

It looks to me like there is some non-recorded instructions in your code so I'm curious as to how they got there. e.g. I don't think these instructions can be "recorded" so can you explain how they ended up in your code? Are you capable of writing code directly within the VBA editor?

LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("Q2:Q" & LastRow).FillDown

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#5
February 8, 2018 at 09:10:16
@DerbyDad03

Thank you for all the tips, I think this is the push I needed to get to next level with VBA :)

I actually found the resolution to my question. I am inspired by how the code can be cleaner and more efficient as you have mentioned above.

Although I feel like posting the solution I figured right away, I would like to clean up the data and structure it better and learn to link it so it is better for the forum.

Yes, I took some course on Udemy for VBA basics, hence I have learned to do very basic coding in VBA editor. But I want to get better at this and be able to code more without using macro recorder.

I will be posting soon.

Thank you very much!


Reply ↓  Report •

#6
February 8, 2018 at 09:46:00
I don't know if this link will help, but I'm sure it won't hurt: ;-)

https://www.computing.net/howtos/sh...

I would also be interested in seeing your solution. Since there are often many ways to accomplish a goal, especially when it comes to VBA, I like to see other people's methods. I often steal...errr...I mean get my best ideas from code that other people write.

message edited by DerbyDad03


Reply ↓  Report •

#7
February 9, 2018 at 10:23:07
@DerbyDad03

This is the final code I have for my routine activity at work. I have included the Dynamic range in the vlookup and recorded that as a part of the code as well.


---------------------------------------------------------------------------------------------------------------------------------------
Option Explicit
'Final version with cleaner code

Public Sub DoEverything()

Dim LastRow As Long

AddSheetNAme
GroupAndConcatenate
SummaryAddColumnsAndValues
RemoveColumnsSpend
ValidationFormulasSpend

End Sub
Sub AddSheetNAme()

' AddSheetNAme Macro
' Add sheet names

Sheets("Sheet1").Name = "Summary"
Sheets("Sheet2").Name = "Spend "
Range("A2").Select

End Sub
Sub GroupChainsAndConcatenate()
'
' GroupChainsAndConcatenate Macro
' Adds the columns to group and concatenate

Dim LastRow As Long

Worksheets("Summary").Select

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("A1") = "Unique Identifier"
Range("B1") = "Group Chains"
Range("A1:B1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("B2").FormulaR1C1 = "=IF(RC[1]=""ABC"",""BBC"",IF(RC[1]=""EFG"",""GGH"",RC[1]))"
Range("B2").Select
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("B2:B" & LastRow).FillDown
Range("A2").FormulaR1C1 = "=CONCATENATE(RC[1],RC[3])"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A2:A" & LastRow).FillDown
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

Sub SummaryAddColumnsAndValues()
'
' SummaryAddColumnsAndValues Macro
' ActualsColumnsAndValues Macro ' Adds empty columns next to each month of actuals and populates the values

Dim LastRow As Long

Worksheets("Summary").Select
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Columns("T:T").Select
Selection.Insert Shift:=xlToRight
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Columns("X:X").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.NumberFormat = "@"
Range("F1") = "JAN 2017"
Range("H1") = "FEB 2017"
Range("J1") = "MAR 2017"
Range("L1") = "APR 2017"
Range("N1") = "MAY 2017"
Range("P1") = "JUN 2017"
Range("R1") = "JUL 2017"
Range("T1") = "AUG 2017"
Range("V1") = "SEP 2017"
Range("X1") = "OCT 2017"
Range("F2") = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("F2:F" & LastRow).FillDown
Range("H2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("H2:H" & LastRow).FillDown
Range("J2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("J2:J" & LastRow).FillDown
Range("L2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("L2:L" & LastRow).FillDown
Range("N2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("N2:N" & LastRow).FillDown
Range("P2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("P2:P" & LastRow).FillDown
Range("R2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("R2:R" & LastRow).FillDown
Range("T2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("T2:T" & LastRow).FillDown
Range("V2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("V2:V" & LastRow).FillDown
Range("X2").FormulaR1C1 = "=SUMIFS(C[-1],C1,RC1)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("X2:X" & LastRow).FillDown
Range("E2").Select

'Adds dynamic ranges
ActiveWorkbook.Names.Add Name:="SummaryDynamicRange", RefersToR1C1:="=Summary!R1C1:R464C29"
ActiveWorkbook.Names("SummaryDynamicRange").Comment = ""
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="HeaderSummary", RefersToR1C1:="=Summary!R1C1:R1C29"
ActiveWorkbook.Names("HeaderSummary").Comment = ""
Range("A2").Select

End Sub

Sub RemoveColumnsSpend()
'
' RemoveColumnsSpend Macro
' Removes unwanted columns in spend tracker before inserting formulas for validation

Dim LastRow As Long

Worksheets("Spend ").Select
Columns("C:C").Delete Shift:=xlToLeft
Columns("H:H").Delete Shift:=xlToLeft
Columns("I:I").Delete Shift:=xlToLeft
Columns("J:K").Delete Shift:=xlToLeft
Columns("L:O").Delete Shift:=xlToLeft

Range("C2").Select


End Sub

Sub ValidationFormulasSpendTracker()
'
' ValidationFormulasSpend
' Groups concatenates and adds the validation formulas
'

'

Dim LastRow As Long

Worksheets("Spend ").Select

Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "Unique Identfier"
Range("B1") = "Grouping Chains"
Range("A1:B1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("B2").FormulaR1C1 = "=IF(RC[1]=""VONS"",""ALBERTSONS INC"",RC[1])"
Range("B2").Select
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("B2:B" & LastRow).FillDown
Range("A2").FormulaR1C1 = "=CONCATENATE(RC[1],RC[3])"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A2:A" & LastRow).FillDown
Cells.Select
Cells.EntireColumn.AutoFit
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("O1") = "Spend Tracker Total Spends"
Range("P1") = "Rolling Total Spends"
Range("Q1") = "Match"
Range("R1") = "Variance"
Range("O2").FormulaR1C1 = "=SUMIFS(C[-2],C[-14],RC[-14],C[-7],RC[-7])"
Columns("O:P").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("O2").Select
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("O2:O" & LastRow).FillDown
Range("P2").FormulaR1C1 = "=VLOOKUP(RC[-15],SummaryDynamicRange,MATCH(RC8,HeaderSummary,0),FALSE)"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("P2:P" & LastRow).FillDown
Range("Q2").FormulaR1C1 = "=EXACT(RC[-2],RC[-1])"
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("Q2:Q" & LastRow).FillDown
Range("R1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.AutoFilter
Range("R2").Select
Columns("R:R").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-3]"
Range("R2").Select
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("R2:R" & LastRow).FillDown
Range("R2").Select
End Sub



Reply ↓  Report •

Ask Question