Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Can anybody see my fault here? I am trying to add a vlookup formula to a cell (t)

    t.Offset(0, 2).Value = Application.VLookup(t.Value, Admin!$A$2:$B$19, 2, False)

    Thanks

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, with my brain back in:

    t.Offset(0, 2).Formula = Application.VLookup(t.Offset(0, -1).Value, q, 2, 0)

    where q is set as the range of "Admin!$A$2:$B$19"

    Only one problem, I'm getting the value of the formula, but I want the actual formula. ?

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When using the .formula, the information after the = is what you would actually type into the cell for example the following would enter =B1+B2 into the currently selected cell.

    selection.formula="=b1+b2"

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It would look something like this -assuming that your q was explicitly designated.

    t.Offset(0, 2).Formula = "=VLookup(" & t.Offset(0, -1).Value & ",Admin!$A$2:$B$19, 2, 0)"

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you wanted the lookup value to refer to the cell as a reference rather than a hardcoded value:
    Code:
    t.Offset(0, 2).FormulaR1C1 = "=VLookup(RC[-3],Admin!R2C1:R19C2,2,0)"
    should work
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='791945' date='03-Sep-2009 09:33']
    Code:
    t.Offset(0, 2).FormulaR1C1 = "=VLookup(RC[-3],Admin!R2C1:R19C2,2,0)"
    [/quote]

    t.Offset(0, 2).FormulaR1C1 = "=VLookup(RC[-3],Admin!R2C1:R19C2,2,0)"

    Thanks Rory, that does work as I require. However, is it possible to set a reference to Admin!R2C1:R19C2 and then use that in the formula? The reason that I ask is that I need to use this several times over, so would be easier for future maintenance.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='791917' date='03-Sep-2009 03:42']It would look something like this -assuming that your q was explicitly designated.

    t.Offset(0, 2).Formula = "=VLookup(" & t.Offset(0, -1).Value & ",Admin!$A$2:$B$19, 2, 0)"[/quote]

    Thanks Mike, a few hours kip was required after spending far too long trying to sort this. Thankfully Rory has provided a working solution. I appreciate all of your help!

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes, you can just store it as a string variable:
    Code:
    Dim strLookupRange as String
    strLookupRange = "Admin!R2C1:R19C2"
    t.Offset(0, 2).FormulaR1C1 = "=VLookup(RC[-3]," & strlookuprange & ",2,0)"
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory, perfect.

    I'm having a mare of a time trying to create these formulas 'on the fly'. Could I please trouble you for some further help.

    t = range (one cell)
    x = range (range of cells)
    w = long (last row number)

    What I am attempting to do is: for each cell in a range of cells, add formula's to 3 columns that would look like the following (based on row 5) in the actual formula's

    Code:
    	For Each t In x
    		't.Offset(0, 12).Formula = "=S5-Z5"
    		't.Offset(0, 13).Formula = "=RANK(S5,S5:S & w)"
    		't.Offset(0, 15).Formula = "=SUMPRODUCT((B5=B5:B & w)*(S5<S5:S & w))+1"
    	Next t
    Thanks

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Which column is the x range in?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='791960' date='03-Sep-2009 11:40']Which column is the x range in?[/quote]

    Code:
    w = ply.Cells(Rows.Count, 2).End(xlUp).Row	  'Last row number
    	Set x = ply.Range("H5:H" & w)					  'Range of rows 5 to last row

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    So column H then.

    Try this:
    Code:
    	For Each t In x
    		t.Offset(0, 12).FormulaR1C1 = "=RC[-1]-RC[6]"
    		t.Offset(0, 13).Formula = "=RANK(RC[-2],R5C[-2]:R" & w & "C[-2])"
    		t.Offset(0, 15).Formula = "=SUMPRODUCT((RC[-21]=R5C[-21]:R" & w & "C[-21])*(RC[-4]<R5C[-4]:R" & w & "C[-4]))+1"
    	Next t
    I've taken a guess that you wanted the rows 5:w bits to be fixed - i.e. the same for each row the formula is entered into.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13

Posting Permissions

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