=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

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

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

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

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

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

1. Nesting IF's and AND's - a bit of help please...

2. Nested nested nested NOT EXISTS - simplification possible?

3. SUMPRODUCT with AND's and OR's?

4. WMP11 BUG: Advanced Search doesn't work with ANDs

6. Need help sorting out ANDs and ORs in parameter query

9. Max OR's and AND's in a WHERE clause

10. two questions: and's inside if's and checking the return code of a program

11. Problem with calculations and multiple AND's in criteria

12. how to combine and's with or's in a if statement

13. Wireless Authentication ands Ecryption Question

14. i start computer i get a override message ands puter wont start

15. IFs ANDs and ORs

4 post • Page:**1** of **1**