I'm trying to put a formula in a cell thru VBA

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
I am trying to put a formula in a cell through VBA. I was trying to use what I learned here, but I keep getting an error. Any help would be appreciated. My code follows. (gen_vlookup is a udf similar to vlookup).

It is bombing on the last line.

fs = "gen_vlookup(E" & far & ",customers,2,5)+0.5*(gen_vlookup(E " & far & ",customers,2,9)"
fs = fs & "*--(gen_vlookup(E" & far & ",customers,2,10)<=A" & far & "))"

'Put formula into proper place
'Sheets("Checking").[d & far].Formula = fs
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
1. What's the formula supposed to look like? Doesn't it start with an equals sign? You have to include it.

fs = "=gen_lookup(E" & blah blah

2. I wish MS never allowed the use of [ ] as a shortcut to properly referencing objects. I assume 'far' in brackets is a numerical variable; does 'd' stand for column D? Also, use Worksheets instead of Sheets. I know it takes more typing, but you should really do it correctly:

ActiveWorkbook.Worksheets.Range("D" & far).Formula = fs
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Jon, I tried what you said. It is still bombing on the last line - the error is "object doesn't support property or method. Yes far is a numerical variable. I have included all the code (it isn't really a lot) this time. Thanks for your continued help.


Private Sub CommandButton1_Click()
Dim far As Double 'far = First Available Row
Dim fs As String 'fs = Formula String


'Get First empty row, Use Date Column (A) since it will always be the last row used
'(not true with other columns like debit and credit)
With Sheets("checking")
far = .Range("A" & .Rows.Count).End(xlUp).row + 1
End With

'Enter today's date in Date Column
Range("a" & far).Value = Now

'Copy formula for Balance down in balance column (B)
Range("B" & far - 1).Copy
Range("B" & far).Select
ActiveSheet.Paste

'Create formula string. We can't just copy credit down, because there may not be a
'formula in the last cell (i.e. row # far-1).
fs = "=gen_vlookup(E" & far & ",customers,2,5)+0.5*(gen_vlookup(E " & far & ",customers,2,9)"
fs = fs & "*--(gen_vlookup(E" & far & ",customers,2,10)<=A" & far & "))"

'Put formula into proper place
ActiveWorkbook.Worksheet.Range("checking!d" & far).Formula = fs
End Sub
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Change this:

ActiveWorkbook.Worksheet.Range("checking!d" & far).Formula = fs

to this:

ActiveWorkbook.Worksheets("checking").Range("d" & far).Formula = fs

My bad, I left out the sheet name in my earlier post.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,162
Members
410,667
Latest member
Gaexel
Top