VBA Absolute Reference: #REF Errors

DDePoy

New Member
Joined
Jan 8, 2017
Messages
9
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top