Macro Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DP7 - 11 Mar 2008 14:38 GMT

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.

Reply to this Message

Martin Fishlock - 11 Mar 2008 15:26 GMT

Hi DP7:

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

Signature

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

> 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.
[quoted text clipped - 5 lines]
> 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.

Reply to this Message

DP7 - 11 Mar 2008 15:36 GMT

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:
>
[quoted text clipped - 10 lines]
> > 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.

Reply to this Message

Martin Fishlock - 11 Mar 2008 16:17 GMT

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)

Signature

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

> See my macro code below
>
[quoted text clipped - 41 lines]
> > > 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.

Reply to this Message