Nesting ,Ands

=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

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

"teresa" <teresa@discussions.microsoft.com> wrote in message
news:EA173E85-1941-464A-A70D-CBE2BDFA05A1@microsoft.com...
> =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
>
>

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" wrote:

> 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
>
> "teresa" <teresa@discussions.microsoft.com> wrote in message
> news:EA173E85-1941-464A-A70D-CBE2BDFA05A1@microsoft.com...
> > =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
> >
> >
>
>
>

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

"teresa" <teresa@discussions.microsoft.com> wrote in message
news:2C61502A-D174-4374-9B11-F771DABE7F23@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> "teresa" <teresa@discussions.microsoft.com> wrote in message
>> news:EA173E85-1941-464A-A70D-CBE2BDFA05A1@microsoft.com...
>> > =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
>> >
>> >
>>
>>
>>