Nested if function not working, only 2 of the 4 if's work

[Huby00]

additional infor.. i broke it down to the rudimentary code, and still only two of the 4 work...

Sub craptest()

LastRow = Range("A65536").End(xlUp).Row

For k = 1 To LastRow Step 1
If Cells(k, 19) = "Rewash" Then
Cells(k, 26) = Cells(k, 19)
Else
If Cells(k, 19) = "Virgin" Then
Cells(k, 26) = Cells(k, 19)
Else
If Cells(k, 19) = "Rewash outlier" Then
Cells(k, 26) = Cells(k, 19)
Else
If Cells(k, 19) = "Virgin outlier" Then
Cells(k, 26) = Cells(k, 19)
End If
End If
End If
End If
Next k
End Sub

the virgin and the rewash do not work.


Hello, first off let me apologize in advance for my lower than novice style of coding.

What i'm trying to do is sort 2 columns containing 4 types of data break it down in to 4 separate sections on a different sheet (8 columns in all)

I separated the if statements and only the 3rd and 4th ifs ever get put to use. I triple checked to make sure that the cells(x,y) = "value" for my logical condition was correctly entered. Any help would be very much appreciated.

Sub transferpot()
LastRow = Range("A65536").End(xlUp).Row
Dim counter As Integer
Dim counter1 As Integer
Dim counter2 As Integer
Dim counter3 As Integer
counter = 1
counter1 = 1
counter2 = 1
counter3 = 1
Dim k As Integer
 
For k = 2 To LastRow Step 1
If Cells(k, 19) = "Rewash" Then
Sheets("sheet4").Cells(counter2, 5) = Sheets("Sheet1").Cells(k, 11)
Sheets("sheet4").Cells(counter2, 6) = Sheets("Sheet1").Cells(k, 12)
counter2 = counter2 + 1
Else
If Cells(k, 19) = "Virgin" Then
Sheets("sheet4").Cells(counter3, 7) = Sheets("Sheet1").Cells(k, 11)
Sheets("sheet4").Cells(counter3, 8) = Sheets("Sheet1").Cells(k, 12)
counter = counter + 1
Else
If Cells(k, 19) = "Rewash outlier" Then
Sheets("sheet4").Cells(counter3, 7) = Sheets("Sheet1").Cells(k, 11)
Sheets("sheet4").Cells(counter3, 8) = Sheets("Sheet1").Cells(k, 12)
counter3 = counter3 + 1
Else
If Cells(k, 19) = "Virgin outlier" Then
Sheets("sheet4").Cells(counter1, 3) = Sheets("Sheet1").Cells(k, 11)
Sheets("sheet4").Cells(counter1, 4) = Sheets("Sheet1").Cells(k, 12)
counter1 = counter1 + 1
End If
End If
End If
End If
 
Next k
 
End Sub

 

[alvinwlh]

Hi,

1) What do you mean by 2 out of 4 work?
2) which is not working?


Is the below "Second if" counter ok? :

If Cells(k, 19) = "Virgin" Then
Sheets("sheet4").Cells(counter3, 7) = Sheets("Sheet1").Cells(k, 11)
Sheets("sheet4").Cells(counter3, 8) = Sheets("Sheet1").Cells(k, 12)
[COLOR=red]counter = counter + 1
[/COLOR]Else

 

[shg]

1. Comparisons in VBA are case-sensitive by default. Do the cases match?

2. Got any spurious spaces in those cells?

3. Have you tried stepping through the code and watching it execute? This might be a good time to read http://www.cpearson.com/excel/debug.htm

 

[Huby00]

Thank you all for your replies. I was trying to take down the post but I went over the edit limit and had to wait.

BASICALLY the problem is that the cell that is being examined for the logical condition is a combination of two other cells.

So if cell1 = Rewash and Cell2 = outlier, then the cell was cell1 & " " & cell 2

It would work for Rewash outliers because it contained the space but for the Rewash I did not include the " ".

When I changed it to Rewash[space] it worked fine.

THanks again for the replies and sorry for wasting your time. I hope my learning impared struggle brought a smile to a couple faces at least.

 

[shg]

You're welcome, good luck. Pearson's page is a great place to start learning to debug; spend some time there when you get a chance.

 

[Huby00]

Roger that, will take a look so I save myself and all of you guys some trouble.



Joke for your troubles:

Why did the bird try to eat a paper with the following code?

Activesheet("sheetz1").selection

Because of debug