Add formula to cell (vba)

Topic

New Reply

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

Reply | Quote

Replies

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. ?

Reply | Quote

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″

Reply | Quote

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)”

Reply | Quote

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)”

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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)"

Reply | Quote

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

Reply | Quote

Which column is the x range in?

Reply | Quote

Which column is the x range in?

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

Reply | Quote

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.

Reply | Quote

Rory, Thanks a lot for your help.

Reply | Quote