VBA For Each with nested For Each

Question

I have written a For each loop that I want to go through the range and test for the first condition and add the value to a combobox. ONLY If it cannot find that condition then I want it to execute the second for each loop and add all the values that match the condition.

I have written the below code and it says

Else without If

even though there is an IF

 Dim Keys As Range, cell As Range


 Set Keys = ThisWorkbook.Worksheets("keyHistory").Range("A2:A20000")

   For Each cell In Keys
     If cell.Value = WindowsUserName And cell.Offset(0, 4) = 1 Then Me.ComboBox1.AddItem cell.Value

   Else
      For Each cell In Keys
      If cell.Offset(0, 4).Value = 1 And cell.Offset(0, 3) = "" Or cell.Value = "Spare" And cell.Offset(0, 4) = 1 Then Me.ComboBox1.AddItem cell.Value

   Next cell

 Next cell
 Set Keys = Nothing

Please read about If-Then-Else Statements.

Scott Holtzman
– Scott Holtzman

2017-11-13 15:56:01 +00:00
Commented Nov 13, 2017 at 15:56 — Scott Holtzman
– Scott Holtzman, Commented Nov 13, 2017 at 15:56

braX · Accepted Answer · 2017-11-13 15:51:37Z

1

Split this line into 2 lines like this

 If cell.Value = WindowsUserName And cell.Offset(0, 4) = 1 Then
     Me.ComboBox1.AddItem cell.Value

You will also need to use End If somewhere - something like this gets the structure right, but as for the functionality, I'm not sure what you are trying to do, so I guessed.

Dim Keys As Range, cell As Range, cell2 as Range


 Set Keys = ThisWorkbook.Worksheets("keyHistory").Range("A2:A20000")

   For Each cell In Keys
     If cell.Value = WindowsUserName And cell.Offset(0, 4) = 1 Then 
         Me.ComboBox1.AddItem cell.Value
     Else
        For Each cell2 In Keys
            If cell2.Offset(0, 4).Value = 1 And cell2.Offset(0, 3) = "" Or cell2.Value = "Spare" And cell2.Offset(0, 4) = 1 Then Me.ComboBox1.AddItem cell.Value
        Next
     End IF
 Next
 Set Keys = Nothing

answered Nov 13, 2017 at 15:51

braX

11.9k5 gold badges24 silver badges38 bronze badges

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

2 Comments

Leighholling Over a year ago

I need an else clause in there though as I need it to go down a sheet and look for a value match and only if it doesn't find WindowsUserName And cell.Offset(0, 4) = 1. I only want either the result of this condition of the results of the other condition not both

Leighholling Over a year ago

What I have is a table and a combobox I want it to check for the first condition, if that is met then add only that value to the combobox. If this conditions is not found then I want it to populate the combobox with all the values that match condition 2

Leighholling · Accepted Answer · 2017-11-13 16:50:24Z

For anyone that's interested I fixed this by adding a do while loop to the beginning that tested for the 1st condition this populated the combobox exited the sub if found or ran the for each loop and adds all the matched values.

    Dim Keys As Range, cell As Range, userName As Variant


userName = WindowsUserName

ThisWorkbook.Worksheets("keyHistory").Range("A2").Select

Do While ActiveCell.Value <> ""
        If ActiveCell.Value = userName And ActiveCell.Offset(0, 2) <> "" And ActiveCell.Offset(0, 4) = 1 Then
        Me.ComboBox1.AddItem ActiveCell.Value
        Set userName = Nothing
        Exit Sub

        Else
        ActiveCell.Offset(1, 0).Select
        End If
Loop


Set Keys = ThisWorkbook.Worksheets("keyHistory").Range("A2:A20000")

For Each cell In Keys
If cell.Offset(0, 4).Value = 1 And cell.Offset(0, 3) = "" Or cell.Value = "Spare" And cell.Offset(0, 4) = 1 Then Me.ComboBox1.AddItem cell.Value
Next cell


 Set Keys = Nothing

 Set userName = Nothing

 Set cell = Nothing

Collectives™ on Stack Overflow

VBA For Each with nested For Each

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related