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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
For example, ...?
 

DDePoy

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

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,129,458
Messages
5,636,390
Members
416,917
Latest member
koto1

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
Top