VLOOKUP works but gets #REF when macro changes formulas to values
 

I have a range of templates using VLOOKUP to get data from a separate file
and enter it in columns C and E, within the range A18:E1018. Macro code as
follow
--------------------------------------------------------------------------------
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("D8:E9").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
Range("A18:A1018").SpecialCells(xlCellTypeBlanks). EntireRow.Delete
Columns("A:E") = Columns("A:E").Value
ActiveSheet.Shapes("Picture 14").Delete
Columns("A:F").Interior.ColorIndex = xlNone
Range("commission").ClearContents
Sheets("Instructions").Select
ActiveSheet.Name = "Terms&Conditions"
Rows("1:32").Clear
Rows("1:32").Delete
ActiveSheet.Shapes("Object 1").Delete
Range("A1").Select
Sheets("Quotation").Select
Range("qdata1").Select
Dim vbCom As Object

Call logquote

Application.ScreenUpdating = True

On Error Resume Next

'Set vbCom = Application.VBE.ActiveVBProject.VBComponents
Set vbCom = ActiveWorkbook.VBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module3")

vbCom.Remove VBComponent:= _
vbCom.Item("Module4")

On Error GoTo 0

End Su
--------------------------------------------------------------------------------
One user (and only one) can see the data fine but when the macro is run the
data in column E changes to #REF rather than the value, column C converts to
the value OK.

Unfortunately, the user seems unable to tell me what they are doing
differently or give me an example :-(

From looking at the code can anyone see why this might be happening?

VLOOKUP works but gets #REF when macro changes formulas to values
 

"Code Numpty" wrote:

I have a range of Excel 2003 templates using VLOOKUP to get data from a separate file
and enter it in columns C and E, within the range A18:E1018. Macro code as
follows
--------------------------------------------------------------------------------
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("D8:E9").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
Range("A18:A1018").SpecialCells(xlCellTypeBlanks). EntireRow.Delete
Columns("A:E") = Columns("A:E").Value
ActiveSheet.Shapes("Picture 14").Delete
Columns("A:F").Interior.ColorIndex = xlNone
Range("commission").ClearContents
Sheets("Instructions").Select
ActiveSheet.Name = "Terms&Conditions"
Rows("1:32").Clear
Rows("1:32").Delete
ActiveSheet.Shapes("Object 1").Delete
Range("A1").Select
Sheets("Quotation").Select
Range("qdata1").Select
Dim vbCom As Object

Call logquote

Application.ScreenUpdating = True

On Error Resume Next

Set vbCom = ActiveWorkbook.VBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module3")

vbCom.Remove VBComponent:= _
vbCom.Item("Module4")

On Error GoTo 0

End Sub
--------------------------------------------------------------------------------
One user (and only one) can see the data fine but when the macro is run the
data in column E changes to #REF rather than the value, column C converts to
the value OK.

Unfortunately, the user seems unable to tell me what they are doing
differently or give me an example :-(

From looking at the code can anyone see why this might be happening?