How to put this formula using VBA

[tanglk]

i want to put this formula to column F using VBA where the row number
will change accroding to row number

=IF(E7="CB",A7&(D7*-1),A7&D7)

Hope you all can help me.

regards

 

[Dave Peterson]

Sometimes formulas get a lot simpler when you use .formulaR1C1 style:

Option Explicit
Sub testme1()
Dim myRng As Range
Set myRng = ActiveSheet.Range("f92")
myRng.FormulaR1C1 = "=IF(RC[-1]=""CB"",RC[-5]&(RC[-2]*-1),RC[-5]&RC[-2])"
End Sub

In fact, if you wanted to do lots of cells all at once:

Option Explicit
Sub testme2()
Dim myRng As Range
Set myRng = ActiveSheet.Range("f2:F1002")
myRng.FormulaR1C1 = "=IF(RC[-1]=""CB"",RC[-5]&(RC[-2]*-1),RC[-5]&RC[-2])"
End Sub

 

[Tom Ogilvy]

Does Dave's formula look simpler to you? <g> It doesn't to me Especially
when:

Range("F7:F100").Formula = _
"=IF(E7=""CB"",A7&(D7*-1),A7&D7)"

should enter the correct formula in cells F7 to F100 (adjust to suite) and
all you had to do was add a few double quotes.

this might be one of those times when using R1C1 notation doesn't make the
formula simpler.

Now I will admit that in my approach the formula has to be specifically
written as if it were to be entered in the first cell of the range (which is
why I started my range at F7), while Dave's doesn't need to be adjusted, but
in this case, I don't see that as much of a problem.

 

[Dave Peterson]

I read the original question where that formula would be put in any single cell
in column F (like F92).

This may have been an imaginative interpretation, though <vbg>.

 

[Dave Peterson]

ps to the OP: I put the original A1 reference style in F7.

Then I turned on R1C1 reference style. (Tools|options|General tab)
Then I stole the formula from the formula bar.
Pasted into the VBE window and added the extra double quotes.

(It wasn't really that difficult <vvbg>.)

ooh. I then I toggled back to A1 reference style.

 

[Tom Ogilvy]

i want to put this formula to column F using VBA where the row number

will change accroding to row number

I think it was an imaginative interpretation <g>

 

[Dave Peterson]

Do you want to know the color of the sky where I live?

It's rose colored!