Address Value in Macro based Vlookup
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Address Value in Macro based Vlookup

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm having problems getting the relative address into a VLOOKUP formula inside a macro. Below are the steps taken with explanation:

    Cells.Find(what:="LAB COST", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    This lets me find the column in the spreadsheet based on the header value no matter where is it put by the download program. We had added and removed so many columns that column addresses are not practical for the macro.
    myaddress = ActiveCell.Address(ReferenceStyle:=x1R1C1)
    In looking at the local variables my address does have the correct relative address in the field. R6C27

    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-1]>0,(RC[-1]*(VLOOKUP(myaddress,'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE))),"""")"
    Selection.Copy
    This is probably where my problem is with the coding of the myaddress within the formula. It is not evaluating the value but putting the ""myaddress"" into the formula.


    IF(Z8>0,(Z8*(VLOOKUP(myaddress,'Cost to Chg Ratios'!$A$8:$B$22,2,FALSE))),"")

    This is the result that gets put into the spreadsheet.
    Any help would be appreciate.
    Thanks
    Sammy

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 06:41, SLCantrell wrote:
    I'm having problems getting the relative address into a VLOOKUP formula inside a macro. Below are the steps taken with explanation:

    Cells.Find(what:="LAB COST", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    This lets me find the column in the spreadsheet based on the header value no matter where is it put by the download program. We had added and removed so many columns that column addresses are not practical for the macro.
    myaddress = ActiveCell.Address(ReferenceStyle:=x1R1C1)
    In looking at the local variables my address does have the correct relative address in the field. R6C27

    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-1]>0,(RC[-1]*(VLOOKUP(myaddress,'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE))),"""")"
    Selection.Copy
    This is probably where my problem is with the coding of the myaddress within the formula. It is not evaluating the value but putting the ""myaddress"" into the formula.


    IF(Z8>0,(Z8*(VLOOKUP(myaddress,'Cost to Chg Ratios'!$A$8:$B$22,2,FALSE))),"")

    This is the result that gets put into the spreadsheet.
    Any help would be appreciate.
    Thanks
    Sammy
    Try changing your command to:

    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-1]>0,(RC[-1]*(VLOOKUP(" & myaddress & ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE))),"""")"

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie,
    Tried it as you listed it.
    With the "&myaddress&", that actually gives a compile error.
    I tried with single quotes and that gives a run time error 1004, application-defined or object-defined error.
    Thanks
    Sammy

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 11:35, SLCantrell wrote:
    Barrie,
    Tried it as you listed it.
    With the "&myaddress&", that actually gives a compile error.
    I tried with single quotes and that gives a run time error 1004, application-defined or object-defined error.
    Thanks
    Sammy
    Sammy, make sure you have a space separating each element. Like this:

    "This text" & myaddress & "That text"

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie,
    Below is the statement as it now appears in the macro. It give me a compile error at the third " or just before ",'Cost the error is syntax error.

    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(" &myaddress& ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE)"

    Really Appreciate your help.
    Sammy

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 14:54, SLCantrell wrote:
    Barrie,
    Below is the statement as it now appears in the macro. It give me a compile error at the third " or just before ",'Cost the error is syntax error.

    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(" &myaddress& ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE)"

    Really Appreciate your help.
    Sammy
    Sammy, change the statement to read:

    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(" & myaddress & ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE)"

    Note the space before, and after, myaddress.


    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Barrie,
    Your great, it worked, I had left out one space.
    Thanks a lot.
    Sammy

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com