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!
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!