Macro Problem

[DP7]

I have a Marco that runs a series of VLookups from a button in the excel
sheet. The Macro is stored in the personal worksheet which is always hidden.
The workbook is used as a template that is stored on the server where it is
access by multiple users. My problem is that some how the macro is “losingâ€
the path to the “table array†sheet that is stored in a separate worksheet on
the server. When you hit the button for the Vlookup; a window for u to
navigate to the file that contains the sheet for the “table array†values
appears. This is very frustrating and perplexing to me. If anyone has any
ides as to how I can solve this issue I would be very grateful.

 

[Martin Fishlock]

Hi DP7:

It would be useful to see your code so that we could see if there is
something we could point out.

 

[DP7]

See my macro code below

Sub Fdrivetrim2()
'
' Fdrivetrim2 Macro
' Macro recorded 03/4/2008 by Dwayne
'

'
Range("J15").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],[TRIMPRICES.xls]FUSING!R1C1:R8C3,3,FALSE)"
Range("J17").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],[TRIMPRICES.xls]ZIPPERS!R1C1:R37C2,2,FALSE)"
Range("J19").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],[TRIMPRICES.xls]ELASTIC!R1C1:R11C4,4,FALSE)"
ActiveWindow.SmallScroll Down:=42
Range("M55").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],[TRIMPRICES.xls]POLYBAGS!R1C1:R16C2,2,FALSE)"
Range("M56").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],[TRIMPRICES.xls]HANGERS!R1C1:R35C5,5,FALSE)"
Range("M56").Select
Range("J20").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],[TRIMPRICES.xls]BUTTONS!R1C5:R53C6,2,FALSE)"
End Sub

 

[Martin Fishlock]

Hi DP7:
Try puting the full path to the workbook in the vlookup statement as in:
VLOOKUP("b",'C:\Documents and
Settings\Dad\Desktop\[Book2.xlsx]Sheet1'!$B$3:$C$5,2,FALSE)


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

See my macro code below

Sub Fdrivetrim2()
'
' Fdrivetrim2 Macro
' Macro recorded 03/4/2008 by Dwayne
'

'
Range("J15").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],[TRIMPRICES.xls]FUSING!R1C1:R8C3,3,FALSE)"
Range("J17").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],[TRIMPRICES.xls]ZIPPERS!R1C1:R37C2,2,FALSE)"
Range("J19").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],[TRIMPRICES.xls]ELASTIC!R1C1:R11C4,4,FALSE)"
ActiveWindow.SmallScroll Down:=42
Range("M55").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],[TRIMPRICES.xls]POLYBAGS!R1C1:R16C2,2,FALSE)"
Range("M56").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],[TRIMPRICES.xls]HANGERS!R1C1:R35C5,5,FALSE)"
Range("M56").Select
Range("J20").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],[TRIMPRICES.xls]BUTTONS!R1C5:R53C6,2,FALSE)"
End Sub

Hi DP7:

It would be useful to see your code so that we could see if there is
something we could point out.