Nesting ,Ands

[Guest]

=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
Cells(i, 10) = ".5"

End If
End If

End Sub

 

[IC]

It's unclear exactly what you are trying to do.

The code does not match the formula. There is no reference to J2 in the
formula and no reference to DIRECT in the code. Also, there is no "next" to
go with the "For".

Can you elaborate, then maybe you will get some useful help.

Ian

 

[Guest]

The formula is actually in thre J column in my worksheet - i just want to
extend
it to all J cells, the code I have given is a start but I want it like the
original formula

Thanks a lot

 

[IC]

If I've understood correctly then I think this will work, though I don't
know where you got your 0.5 in your code.
For i = 1 To 12
If Cells(i, 2) = "Amount" Then
If Cells(i, 3) <> 0 Then
Cells(i, 10) = Cells(i, 3) 'If B=Amount,C<>0 then J=C
ElseIf Cells(i, 8) <> 0 Then
Cells(i, 10) = "DIRECT" 'If B=Amount,C=0 then J=DIRECT
Else
Cells(i, 10) = 9 'If B=AmountC=0,H=0 then J=9
End If
Else
Cells(i, 10) = 9 'If B<>Amount then J=9
End If
Next

Ian