Results 1 to 13 of 13
Thread: Add formula to cell (vba)

20090902, 20:16 #1
 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

20090902, 21:21 #2
 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. ?

20090902, 21:33 #3
 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"

20090902, 21:42 #4
 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)"

20090903, 03:33 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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)"
Regards,
Rory
Microsoft MVP  Excel

20090903, 03:55 #6
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='rory' post='791945' date='03Sep2009 09:33']
Code:t.Offset(0, 2).FormulaR1C1 = "=VLookup(RC[3],Admin!R2C1:R19C2,2,0)"
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.

20090903, 03:58 #7
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='mbarron' post='791917' date='03Sep2009 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!

20090903, 03:59 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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

20090903, 05:16 #9
 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 = "=S5Z5" '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

20090903, 05:40 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 Posts
Which column is the x range in?
Regards,
Rory
Microsoft MVP  Excel

20090903, 05:50 #11
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='rory' post='791960' date='03Sep2009 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

20090903, 06:01 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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
Regards,
Rory
Microsoft MVP  Excel

20090903, 18:26 #13
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts