PDA

View Full Version : Macro Inserts Lines, but disturbs referencing cells



nirvehex
11-14-2014, 02:17 PM
Hi,

I have a code that runs a count on rows in one tab, Services Export and inserts that many lines below row 3 on the Recommendations tab.

Here's the code:

Sub CleanRecommendations()
'1. Select Export, Select A2, control down, count filled rows, store count
Dim NumberOfRows As Long
Sheets("Services Export").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
NumberOfRows = Selection.Count
'2. Select Recommendations, Select row 4, insert count + 2 down, copy formulas from row 3 down to fourth row from bottom
Sheets("Recommendations").Select
Rows("4:4").Select
Rows("4:" & NumberOfRows + 2).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3:DG3").Select
Selection.AutoFill Destination:=Range("A3:DG" & NumberOfRows + 2), Type:=xlFillDefault
'Copies array formula
Application.CutCopyMode = False
Selection.FormulaArray = "=IFERROR(VLOOKUP(RC[1],'Yardage Calculator'!C[-42],1,FALSE), _
INDEX('Yardage Calculator'!R9C1:'Yardage Calculator'!R197C1, _
MATCH(MIN(ABS('Yardage Calculator'!R9C1:'Yardage Calculator'!R197C1-RC[1])), _
ABS('Yardage Calculator'!R9C1:'Yardage Calculator'!R197C1-RC[1]),0)))"
Selection.Copy
Range("AQ4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End Sub


The problem is, cells that reference these cells don't change with how many lines are inserted. For example, vlookups on other tabs that are set to:


=VLOOKUP($L2,Recommendations!$F$3:$AV$3,43,FALSE)


won't expand with the number of rows inserted. The F3:AV3 should go to F3:Number of Rows Inserted. Or something like that. I'm struggling with a way to fix this as there are various references to this tab all over other sheets that need to auto expand to the number of rows inserted.

Any ideas?

Thank you!

Roger Govier
11-15-2014, 05:01 AM
Hi

You should either use Tables for your data, which would automatically expand, or create a Dynamic Named range.
For example, instead of referencing Recommendations!$F$3:$AV$3 in your Vlookups, create a named range with a name like "Recomms"
Insert > Name > Define
Name Recomms
Refers to = Recommendations!$F$3:Index(Recommendations!$AV:$AV,COUNTA(Recommendations!$ F$F))

Then do a search and replace in your code to replace Recommendations!$F$3:$AV$3 with Recomms, so the formula like you show would become
=VLOOKUP($L2,Recomms,43,FALSE)