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