VBA Absolute Reference: #REF Errors

[DDePoy]

Hello,

I one last issue I am trying to solve to complete my project and I am at a loss on how to fix it.

Sheet1 = "Inventory Value Report" - I am using this as an "import tab" where a new value report is pasted every time it needs to be used.

My issue is that the first macro that runs deleted unnecessary columns, and this causes errors on subsequent macros because the column has been deleted and several #REF errors are inserted into the formulas.

Is there a way to run an INDEX:MATCH function where the ranges selected stay constant, no matter if the sheet is reformatted or data is deleted?

 

[shg]

For example, ...?

 

[DDePoy]

For example, ...?

I created a Button (Form Control) that runs a series of Macros. The first Macro deletes several columns to reformat the worksheet:

Sub Format_Value_Report()
'
' Format_Value_Report Macro
'


'
Sheets("Inventory Value Report").Select
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:G").Select
Selection.Delete Shift:=xlToLeft
End Sub

One of the following Macros used Index:Match to find matching product numbers on Sheets("Inventory Value Report") and populates the "On Hand" quantities on the Sheets("Pars") under Column G:

MACRO:
Sub On_Hand()
'
' On_Hand Macro
'


'
Range("G3").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX('Inventory Value Report'!C[-5],MATCH(Pars!RC[-6],'Inventory Value Report'!C[-6],0)),0)"
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G651"), Type:=xlFillDefault
Range("G3:G651").Select
End Sub

However after the Format_Value_Report Macro runs the following formula is in each cell in On Hand Column on the Sheets("Pars") worksheet.

=IFERROR(INDEX('Inventory Value Report'!A:A,MATCH(Pars!A3,'Inventory Value Report'!#REF!,0)),0)

Curious is there is a way that the formula will hold the necessary Columns even though the worksheet the formula references is reformatted and Columns are deleted.

 

[DDePoy]

I created a Button (Form Control) that runs a series of Macros. The first Macro deletes several columns to reformat the worksheet:

Sub Format_Value_Report()
'
' Format_Value_Report Macro
'


'
Sheets("Inventory Value Report").Select
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:G").Select
Selection.Delete Shift:=xlToLeft
End Sub

One of the following Macros used Index:Match to find matching product numbers on Sheets("Inventory Value Report") and populates the "On Hand" quantities on the Sheets("Pars") under Column G:

MACRO:
Sub On_Hand()
'
' On_Hand Macro
'


'
Range("G3").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX('Inventory Value Report'!C[-5],MATCH(Pars!RC[-6],'Inventory Value Report'!C[-6],0)),0)"
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G651"), Type:=xlFillDefault
Range("G3:G651").Select
End Sub

However after the Format_Value_Report Macro runs the following formula is in each cell in On Hand Column on the Sheets("Pars") worksheet.

=IFERROR(INDEX('Inventory Value Report'!A:A,MATCH(Pars!A3,'Inventory Value Report'!#REF!,0)),0)

Curious is there is a way that the formula will hold the necessary Columns even though the worksheet the formula references is reformatted and Columns are deleted.

I was able to find a work around. I apologize for any confusion.

 

[shg]

Good job!