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

[mortgageman]

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

 

[Jon Peltier]

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]

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]

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.

 

[mortgageman]

That did it! Perfect. Thanks.