VBA Loop from another sheet

Question

I'm having trouble with my loop not running throughout my entire sheet 1. If the value in Sheet 1 "tests" exist in sheet 2 "cancer". Then i want the value in sheet 2 "cancer" to be placed into sheet 1 "Tests". The code works except for the loop. Currently it only applies to the first record in my first sheet then stops.

Sub Testing()

Dim x As Long
Dim y As Long

x = 2
y = 2

Do While Sheets("Cancer").Cells(y, 1).Value <> ""
     If LCase(Trim(Sheets("Cancer").Cells(y, 1).Text)) = LCase(Trim(Sheets("Tests").Cells(x, 3).Text)) Then
           If Sheets("Tests").Cells(x, 4).Value = "" Then
                 Cells(x, 4) = (Trim(Sheets("Cancer").Cells(y, 3).Text))
                 x = x + 1
           End If
     End If
     y = y + 1
Loop

End Sub

Have you stepped through the code to check what is going on? — SJR
– SJR, Commented Mar 30, 2017 at 15:14

lostinOracle · Accepted Answer · 2017-03-30 15:27:37Z

2

I would use two for loops

for y = 2 to 10000 'the range your values are found
  if Sheets("Cancer").Cells(y, 1).Value <> "" then
    for x = 2 to 10000 'the range your values are in
      If LCase(Trim(Sheets("Cancer").Cells(y, 1).Text)) = LCase(Trim(Sheets("Tests").Cells(x, 3).Text)) and Sheets("Tests").Cells(x, 4).Value = "" Then
            Cells(x, 4) = (Trim(Sheets("Cancer").Cells(y, 3).Text))
      End If
    next
  end if
next

The reason for the loop not running throughout the entire sheet 1 is because of these two lines: If LCase(Trim(Sheets("Cancer").Cells(y, 1).Text)) = LCase(Trim(Sheets("Tests").Cells(x, 3).Text)) and Sheets("Tests").Cells(x, 4).Value = "" If these conditionals aren't both true, then x will never loop to its next iteration, and you'll have gone through looping through each value of Sheet2 "Cancer" while checking only the same record of Sheet1 "Tests".

edited Mar 30, 2017 at 15:27

answered Mar 30, 2017 at 15:20

lostinOracle

4081 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CLR · Accepted Answer · 2017-03-30 15:18:15Z

1

You've almost qualified all of your ranges. You missed one. Try changing the line:

Cells(x, 4) = (Trim(Sheets("Cancer").Cells(y, 3).Text))

to

Sheets("Tests").Cells(x, 4) = (Trim(Sheets("Cancer").Cells(y, 3).Text))

answered Mar 30, 2017 at 15:18

CLR

12.3k1 gold badge13 silver badges31 bronze badges

3 Comments

SJR Over a year ago

Good spot, but not sure that can be the original problem if the code works the first time.

CLR Over a year ago

You're right, I assumed it was changing the active sheet somewhere but it's not. Is it me, or is this just a vba simulation of a lookup?

SJR Over a year ago

Not sure tbh! Something like that.

Collectives™ on Stack Overflow

VBA Loop from another sheet

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related