Nesting ,Ands

Nesting ,Ands

Post by dGVyZXN » Tue, 08 Feb 2005 03:13:01


=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
 
 
 

Nesting ,Ands

Post by IC » Tue, 08 Feb 2005 03:40:20

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

 
 
 

Nesting ,Ands

Post by dGVyZXN » Tue, 08 Feb 2005 04:37:04

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
 
 
 

Nesting ,Ands

Post by IC » Tue, 08 Feb 2005 23:45:25

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