Multiple Variable For Loop

[JessP]

Hello,

I've written thefollowing code to look at each cell in column C of Sheet2 that equals each cellin column C of Sheet1. Sheet1 values will show up multiple times in Sheet2, soI want it to loop until either there aren't any instances of the values matchingand column G being blank, or it finds one and changes the value in column G.After it either loops through the options or fills in Sheet1 column G, itshould move on to the next row in Sheet1.

It gives me a "Next without For" error on the Next t after the first End If. But if I take that away, it doesn't seem to loop correctly through all instances of the first i before moving on.

[FONT=Calibri]Dim i As Long[/FONT]
[FONT=Calibri]Dim t As Long[/FONT]
[FONT=Calibri]Dim N As Long[/FONT]
[FONT=Calibri]Dim D As Long[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]N =Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Calibri]For i = 3 To N[/FONT]
[FONT=Calibri]D =Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Calibri]For t = 3 To D[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]IfSheets("Sheet2").Cells(t, 3) = Sheets("sheet1").Cells(i, 3)Then[/FONT]
[FONT=Calibri]    If Sheets("sheet2").Cells(t, 7) ="" Then[/FONT]
[FONT=Calibri]        Sheets("Sheet1").Cells(i,5).Value = Sheets("sheet2").Cells(t, 8)[/FONT]
[FONT=Calibri]        Exit For[/FONT]
[FONT=Calibri]    End If[/FONT]
[FONT=Calibri]    Next t[/FONT]
[FONT=Calibri]End If[/FONT]
[FONT=Calibri]Next i[/FONT]
[FONT=Calibri]Next t[/FONT]

Do I need a Do Whileloop instead of For? Something like this, perhaps? Once (t,3) and (i,3) nolonger match and i needs to increase, it ends the sub, which makes sense, butI'm not sure how to fix it without it increasing every loop. This is my firstattempt, so if it's what I need, I'll gladly take any advice you can offer.

[FONT=Calibri]Dim i As Integer[/FONT]
[FONT=Calibri]i = 3[/FONT]
[FONT=Calibri]Dim t As Integer[/FONT]
[FONT=Calibri]t = 3[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]Do WhileWorksheets("Sheet2").Cells(t, 3) =Worksheets("Sheet2").Cells(i, 3)[/FONT]
[FONT=Calibri]    If Sheets("Sheet2").Cells(t, 7) ="" Then[/FONT]
[FONT=Calibri]        Worksheets("Sheet1").Cells(i,5).Value = Worksheets("Sheet2").Cells(t, 8)[/FONT]
[FONT=Calibri]    End If[/FONT]
[FONT=Calibri]    t = t + 1[/FONT]
[FONT=Calibri]Loop[/FONT]

 

[Fluff]

You had 1 too many Next t & the 1st fo which was in the wrong place, try

Dim i As Long
Dim t As Long
Dim N As Long
Dim D As Long

N = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 3 To N
   D = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
   For t = 3 To D
      
      If Sheets("Sheet2").Cells(t, 3) = Sheets("sheet1").Cells(i, 3) Then
          If Sheets("sheet2").Cells(t, 7) = "" Then
              Sheets("Sheet1").Cells(i, 5).Value = Sheets("sheet2").Cells(t, 8)
              Exit For
          End If
      End If
   Next t
Next i

 

[JessP]

Oh, I see - thanks! Didn't realize switching the Next t and Next i would do that. Appreciate the help.

For efficiency purposes, is there a way to move on to the next i as soon as the values stop matching? The instances are all together, so it wouldn't miss any.

 

[Fluff]

There are certainly ways to speed things up, but as I don't fully understand you description of what is needed & it doesn't match what the code is doing.
Could you please have another go of explaining what you need?
Also are the col C values in sheet 1 unique?

 

[JessP]

Thanks - I discovered it was something else entirely that was taking the code a long time to run.

However, it's still not actually working as anticipated, I just didn't notice yesterday. In Sheet1 col C the values are unique. In Sheet2 col C almost all of them repeat one or more times. In Sheet2 col F and G are two sets of dates - col F is when something should be complete and G is the actual date of completion once it's done. Sheet2 is already sorted first alphabetically by col C and then earliest to latest by col F. What I want in Sheet1 col E is the next uncompleted task (found in Sheet2 col H).

Currently it works for every item where there are no completed tasks, but not for those where some tasks are complete and some incomplete. I've played around with it some without success. Do you have any ideas for what's going wrong and how I can tweak it? Thanks!

 

[Fluff]

What signifies that the task is incomplete? Is col H blank?

 

[JessP]

If the task is complete, G will have a date entered (the date completed).

 

[Fluff]

How about

Sub GetData()

   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2)
      Next Cl
      For Each Cl In Ws2.Range("C2", Ws2.Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) And Cl.Offset(, 4) = "" Then .Item(Cl.Value).Value = Cl.Offset(, 5).Value
      Next Cl
   End With

End Sub

 

[JessP]

That works - many thanks! How does CreateObject("scripting.dictionary") work? I've never seen that before.

 

[Fluff]

Glad to help & thanks for the feedback.
There's a good guide to dictionaries here
https://excelmacromastery.com/vba-dictionary/