Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Copy rows into sheets depending on a condition in one column [Closed]

Report
-
 Blocked Profile -
Hi,

This thread has been really helpful. The first part of my code works and copies data that includes the term "EMAS" into the appropriate sheet. However, I have attempted to sort data into another 4 sheets but they will not populate. What am I doing wrong with the code? I am very new to this so it's hopefully very basic. I have arbitrarily given the other sheets letters, I don't know if that' okay.

Sub Button1_Click()
Set i = Sheets("MASTER")
Set e = Sheets("EMAS")
Set b = Sheets("EEAST")
Set c = Sheets("YAS")
Set f = Sheets("SWAST")
Set g = Sheets("WAST")

Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("D" & j))

If i.Range("D" & j) = "EMAS" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop

Do Until IsEmpty(i.Range("D" & j))

If i.Range("D" & j) = "EEAST" Then
d = d + 1
b.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop

Do Until IsEmpty(i.Range("D" & j))

If i.Range("D" & j) = "YAS" Then
d = d + 1
c.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop

Do Until IsEmpty(i.Range("D" & j))

If i.Range("D" & j) = "SWAST" Then
d = d + 1
f.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop

Do Until IsEmpty(i.Range("D" & j))

If i.Range("D" & j) = "WAST" Then
d = d + 1
g.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1

Loop
End Sub

Many Thanks!



1 reply

Answer 1 / 1

You are not Resetting the count after scrubbing for a value. So in other words, take a look here:

d = 1
j = 2


Do Until IsEmpty(i.Range("D" & j))

If i.Range("D" & j) = "EMAS" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop
At this point, J is greater than 2, and EEAST might have been the first entry, but you didn't check for it.

Then you are starting at D3 to look for EEAST, and it doesn't find it.

You need to run the whole loop before you increment to the next cell to check for logic, OR run a nested loop that will reset the counts to check for the new logic!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!