Inserting =if(...) into a cell via VBA

[GeneralShamu]

How can I insert an if statement into a cell via VBA? It will be checking two columns for equality...

I know the column # to paste to and the row # will be based on a for loop (same formula in every row of this column).

 

[VoG]

You need to be more specific.

 

[Saagar]

You can use the formula property to insert formulas in to cells via VBA. Here is an example from Excel VB Help:<code>
Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"</code>

Hope this helps.

 

[GeneralShamu]

I am getting close...

sheetName.Cells(#,#).Formula = "=IF(Cells(" & # & ", " & # & ") ...

It output the results with the word 'Cells' instead of the cell reference to runt he equality test.

 

[Saagar]

Here is the code. You may modify it to fit your needs:

Sub test()

For j = 1 To 10 'j = loop counter for rows
With Worksheets("Sheet1")
.Range("L" & j).Formula = "=$A$4+$A$10"
End With
Next

End Sub

 

[GeneralShamu]

Sorry I do not understand. The column # I output this formula to will change dynamically based on the amount of data I extract from before we enter the Excel world.

Ergo I need the Cells reference since you can refer to columns numerically.

The output into the cell from my formula is as follows:
=IF(Cells(#, ##) = ...

instead of the value in Cells(#,##)

How do I code getting that value if I'm referring to it via Cells (in a string nonetheless).

 

[Saagar]

The formula "=$A$4+$A$10" in my code is just an example.

Send us the exact "IF" formula for one row without using the "#" signs. In your initial post you said "(same formula in every row of this column)". Do you want the cell references for "rows" in each cell to change for each row? In your formula, highlight which parameters are same for all rows and which parameters need to change.

 

[GeneralShamu]

The formula "=$A$4+$A$10" in my code is just an example.

Send us the exact "IF" formula for one row without using the "#" signs. In your initial post you said "(same formula in every row of this column)". Do you want the cell references for "rows" in each cell to change for each row? In your formula, highlight which parameters are same for all rows and which parameters need to change.

"=IF(Cells(" & 3 & ", " & 12 & ") = "
outputs
=IF(Cells(3, 12) =
instead of
=IF(valueInThisCell =

 

[Saagar]

General - I am confused. Let's use an example.

A1 = 1, A2 = "One", A3="Not One". In A4, lets say we want a formula, =If(A1=1,A2, A3).

Using VBA, do you want to insert the answer "Not One" in A4 or do you want to insert the formula =If(A1=1,A2, A3)?

 

[GeneralShamu]

General - I am confused. Let's use an example.

A1 = 1, A2 = "One", A3="Not One". In A4, lets say we want a formula, =If(A1=1,A2, A3).

Using VBA, do you want to insert the answer "Not One" in A4 or do you want to insert the formula =If(A1=1,A2, A3)?

I want to insert the formula that will populate the answer.

It's alright though...I just did an if-else statement and pasted the value into the cell/column. There were no formulas pasted into Excel itself.