Formulae

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following formula in j2

=IF(AND(B2="Amount",C2<>0),C2,IF(AND(B2="Amount",C2=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
 
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
 
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
 
Thank You both - very helpful

sebastienm said:
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
Click to expand...
 
Back
Top