Formulae
 

I have the following formula in j2

=IF(AND(B2="Amount",C2<0),C2,IF(AND(B2="Amount",C 2=0,H2<0),"DIRECT",9))

Hi, Im trying to incorporate the above formula into a macro (below) but am
having some problems with the elses and nesting.

For i = 1 to 100

f Cells(i, 2) = "Amount" Then
If Cells(i, 3) < 0 Then
Cells(i, 10) = Cells(i, 3)
Else
If .......


End If
End If
Next
End Sub

Formulae
 

If Cells(i,2)="Amount" Then
If Cells(i,3)<0 Then
Cells(i,10)= Cells(i,3)
Else
If Cells(i,8)<0 Then Cells(i,10) = "DIRECT" Else Cells(i,10) = 9
End If
End If

"teresa" wrote:

I have the following formula in j2

=IF(AND(B2="Amount",C2<0),C2,IF(AND(B2="Amount",C 2=0,H2<0),"DIRECT",9))

Hi, Im trying to incorporate the above formula into a macro (below) but am
having some problems with the elses and nesting.

For i = 1 to 100

f Cells(i, 2) = "Amount" Then
If Cells(i, 3) < 0 Then
Cells(i, 10) = Cells(i, 3)
Else
If .......


End If
End If
Next
End Sub

Formulae
 

Hi Teresa,
A much faster way that looping through all the cells is to:
- Write the formula in the cells in a single statement:
Range("A2:A101").Formula = "=IF(.....)"
This writes the formula in the whole range A2:A101.
The formula expression should be made based on the first cell in the
Range (here, A2); Excel will then adjust automatically.
- Then Copy /Paste As Value range A2:A101

So in your example:
Sub test()

'Put formula in cells in a single statement
Range("A2:A101").Formula = _
"=IF(AND(B2=""Amount"",C2<0),C2," & _
"IF(AND(B2=""Amount"",C2=0,H2<0),""DIRECT"",9 ))"

'Copy/paste As Value
Range("A2:A101").Copy
Range("A2:A101").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

I hope this helps,
Sebastien

"teresa" wrote:

I have the following formula in j2

=IF(AND(B2="Amount",C2<0),C2,IF(AND(B2="Amount",C 2=0,H2<0),"DIRECT",9))

Hi, Im trying to incorporate the above formula into a macro (below) but am
having some problems with the elses and nesting.

For i = 1 to 100

f Cells(i, 2) = "Amount" Then
If Cells(i, 3) < 0 Then
Cells(i, 10) = Cells(i, 3)
Else
If .......


End If
End If
Next
End Sub

Formulae
 

Thank You both - very helpful

"sebastienm" wrote:

Hi Teresa,
A much faster way that looping through all the cells is to:
- Write the formula in the cells in a single statement:
Range("A2:A101").Formula = "=IF(.....)"
This writes the formula in the whole range A2:A101.
The formula expression should be made based on the first cell in the
Range (here, A2); Excel will then adjust automatically.
- Then Copy /Paste As Value range A2:A101

So in your example:
Sub test()

'Put formula in cells in a single statement
Range("A2:A101").Formula = _
"=IF(AND(B2=""Amount"",C2<0),C2," & _
"IF(AND(B2=""Amount"",C2=0,H2<0),""DIRECT"",9 ))"

'Copy/paste As Value
Range("A2:A101").Copy
Range("A2:A101").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

I hope this helps,
Sebastien

"teresa" wrote:

I have the following formula in j2

=IF(AND(B2="Amount",C2<0),C2,IF(AND(B2="Amount",C 2=0,H2<0),"DIRECT",9))

Hi, Im trying to incorporate the above formula into a macro (below) but am
having some problems with the elses and nesting.

For i = 1 to 100

f Cells(i, 2) = "Amount" Then
If Cells(i, 3) < 0 Then
Cells(i, 10) = Cells(i, 3)
Else
If .......


End If
End If
Next
End Sub