How to put this formula using VBA
 

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

How to put this formula using VBA
 

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

" wrote:

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

How to put this formula using VBA
 

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.

--
Regards,
Tom Ogilvy




wrote in message
oups.com...
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

How to put this formula using VBA
 

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.

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

How to put this formula using VBA
 

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.

Dave Peterson wrote:

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.

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

--

Dave Peterson

How to put this formula using VBA
 

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



--
Regards,
Tom Ogilvy




"Dave Peterson" wrote in message
...
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.

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

How to put this formula using VBA
 

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

It's rose colored!



Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
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.

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

--

Dave Peterson