Set a formula in Excel via VBA 1

[Ham]

I'm using VBA with Excel 2000. I'm trying to store formulas in cells in one sheet to copy from another sheet. The formula I'm trying to store looks like this: =IF(Input!A$10="";"";Input!A$10). I get a run-time error (1004) when I try to do wks.Range.Formula = formula. If the formula is not conditional - =Input!A$10) - it works fine. Any clues?

Any help would be greatly appreciated. -- Ham Rutledge

 

[dsi]

Try:

wks.Range("??").FormulaR1C1 = formula

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[Ham]

The .FormulaR1C1 approach didn't work, - possibly because my Range is just one cell?

The folowing statement is what gives the error:
wks.Range(Cell).Formula = formula

If formula is =Input!A$10 it works. But, if
formula is =IF(Input!A$10="";"";Input!A$10) it doesn't work. I can type the formula into the cell and it works. I just can't set it in there with the VBA statements. The message says it's an application-defined or object-defined error, so maybe something is wrong with the conditional statement, but I sure can't see it. Of course I've been staring at it so long it could be quite obvious.

Any help would be appreciated. Thanks. -- Ham Rutledge

 

[dsi]

In case it's not a typo, you need to use a comma in the IF() statment, not a semicolon.

This works for me:

    Dim sFormula As String
    Dim wks As Worksheet
    
    sFormula = "=IF(Input!A$10=" & Chr(34) & Chr(34) & _
             "," & Chr(34) & Chr(34) & ",Input!A$10)"
    
    Set wks = ActiveSheet
    wks.Range("A1").Formula = sFormula

Hope this helps! DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[Ham]

I'm still having a problem. I've copied the actual code from my program and pasted it below. Tabl(Ncols,3) is a control table indicating what columns in 'Input' should be used to update what columns in 'Detail'. Column 1 in Tabl is the 'Detail' column, column 2 is the 'Input' column, and column 3 is the default if the 'Input' column is null. The row being added is Row. There are 2 strFormula statements. The one that is commented out is the one that blows. The other one sails through with no problem. I really do appreciate your help on this. -- Ham Rutledge

Add_Row:
Set wks = wkbForecast.Worksheets("Detail&quot
RowMsk = Format(Row, "###&quot
For i = 1 To Ncols
Range = Tabl(i, 1) & RowMsk
'strFormula = "=IF(Input!" & Tabl(i, 2) & "$" & RowMsk & "=" & Chr$(34) & Chr$(34) & ";" & Chr$(34) & Tabl(i, 3) & Chr$(34) & ";Input!" & Tabl(i, 2) & "$" & RowMsk & &quot"
strFormula = "=Input!" & Tabl(i, 2) & "$" & RowMsk
MsgBox (strFormula)
wks.Range(Range).Formula = strFormula
Next i
Set wks = wkbForecast.Worksheets("Input&quot
Return

 

[msinclair]

Hello,

I'll bet that you are having difficulty due to the quotation marks (I think you need to double them in the macro). I'd recommend that you use the Record Macro function and manually type the formula into the cells. Cut and paste this "Recorded" formula into your macro. This will work even better if you use relative references, not absolutes when you record the macro.

When you look at the "Recorded" formula, I'm guessing that you'll see a lot more quotation marks than you would expect.

Good Luck,

Marc

 

[VBmim]

Hey dsi

This comma-thing worked for me all-right

thanks!!

greetz

VBmim