VBA for each loops

R

rharn

Board Regular
Joined
Jun 21, 2011
Messages
54
I am trying to write a sub that compares the values from one range to the values in another range, and to perform certain functions if it finds a match. However, if the corresponding value is "N/A" I want the loop to automatically skip to the next checked value "a" . I do not know how to skip to the next "a" however and I get an error that says it does not have a corresponding for in the program. Can someone help me with this? it should be a relatively easy solution but I am new to VBA still. The code is attached below.

Code: 
Sub solubility()
 
    Dim coeff As Range, groups As Range
    Dim anion As Range
    Dim a As Range
    Dim nextrow As Long
    Dim j As Range
    
    Worksheets("properties").Select
    Range("P7:P2000").Select
    Selection.ClearContents
 
    'solubility groups range
    groups = Worksheets("Solubility").Range("A2:A33")
    'group coefficients range
    coeff = Worksheets("Solubility").Range("B2:B33")
    anion = Worksheets("properties").Range("AB7:AB887")
 
   [COLOR=red] For Each a In anion
[/COLOR]       For Each j In groups
            If UCase(a.Value) = UCase(groups(j).Value) Then
                 If groups(j).Value = "" Or "N/A" Then
                    Worksheets("properties").Range("P" & a.Row).Value = "N/A"
                  [COLOR=red]  Next a
[/COLOR]                 Else
                    anvalue = coeff(j).Value * Range("AC" & a.Row).Value
                 End If
            End If
            If UCase(Range("AD" & a.Row).Value) = UCase(groups(j).Value) Then
                cavalue = coeff(j).Value * Worksheets("properties").Range("AE" & a.Row).Value
            If UCase(Range("AF" & a.Row).Value) = UCase(groups(j).Value) Then
                cb1value = coeff(j).Value * Worksheets("properties").Range("AG" & a.Row).Value
            End If
            If UCase(Range("AH" & a.Row).Value) = UCase(groups(j).Value) Then
                cb2value = coeff(j).Value * Worksheets("properties").Range("AI" & a.Row).Value
            End If
       Next j
            If UCase(Range("AD" & a.Row).Value) = UCase("[MIm]") Then
                cavalue = Range("AE" & a.Row) * Worksheets("solubility").Range("B2").Value + Range("AE" & a.Row) * Worksheets("solubility").Range("B7").Value
            End If
        nextrow = Worksheets("properties").Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
        Worksheets("properties").Range("P" & nextrow).Value = _
            anvalue + cavalue + cb1value + cb2value + Worksheets("solubility").Range("b34").Value
   [COLOR=red] Next a
[/COLOR]End Sub
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Code: 
Sub solubility()
 
    Dim coeff As Range, groups As Range
    Dim anion As Range
    Dim a As Range
    Dim nextrow As Long
    Dim j As Range
    
    Worksheets("properties").Select
    Range("P7:P2000").Select
    Selection.ClearContents
 
    'solubility groups range
    groups = Worksheets("Solubility").Range("A2:A33")
    'group coefficients range
    coeff = Worksheets("Solubility").Range("B2:B33")
    anion = Worksheets("properties").Range("AB7:AB887")
 
    For Each a In anion
       For Each j In groups
            If UCase(a.Value) = UCase(groups(j).Value) Then
                 If groups(j).Value = "" Or "N/A" Then
                    Worksheets("properties").Range("P" & a.Row).Value = "N/A"
                    Goto: Here
                 Else
                    anvalue = coeff(j).Value * Range("AC" & a.Row).Value
                 End If
            End If
            If UCase(Range("AD" & a.Row).Value) = UCase(groups(j).Value) Then
                cavalue = coeff(j).Value * Worksheets("properties").Range("AE" & a.Row).Value
            If UCase(Range("AF" & a.Row).Value) = UCase(groups(j).Value) Then
                cb1value = coeff(j).Value * Worksheets("properties").Range("AG" & a.Row).Value
            End If
            If UCase(Range("AH" & a.Row).Value) = UCase(groups(j).Value) Then
                cb2value = coeff(j).Value * Worksheets("properties").Range("AI" & a.Row).Value
            End If
       Next j
            If UCase(Range("AD" & a.Row).Value) = UCase("[MIm]") Then
                cavalue = Range("AE" & a.Row) * Worksheets("solubility").Range("B2").Value + Range("AE" & a.Row) * Worksheets("solubility").Range("B7").Value
            End If
        nextrow = Worksheets("properties").Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
        Worksheets("properties").Range("P" & nextrow).Value = _
            anvalue + cavalue + cb1value + cb2value + Worksheets("solubility").Range("b34").Value
Here:
    Next a
End Sub
 
Upvote 0
Hey Comfy,

Thanks for the speedy reply, however I am getting a syntax error when I try to implement the two lnes of 'Goto: Here' and 'Here:' isthere something else I need to add?
 
Upvote 0
If I do this now I am getting the 'Next without For' error for my line that has 'Next j'. These loops are confusing me terribly, I don't understand why it should be doing this.
 
Upvote 0
Again that's my fault.

The Here: should be infront of Next j and not Next a

Code: 
Sub solubility()
 
    Dim coeff As Range, groups As Range
    Dim anion As Range
    Dim a As Range
    Dim nextrow As Long
    Dim j As Range
    
    Worksheets("properties").Select
    Range("P7:P2000").Select
    Selection.ClearContents
 
    'solubility groups range
    groups = Worksheets("Solubility").Range("A2:A33")
    'group coefficients range
    coeff = Worksheets("Solubility").Range("B2:B33")
    anion = Worksheets("properties").Range("AB7:AB887")
 
    For Each a In anion
       For Each j In groups
            If UCase(a.Value) = UCase(groups(j).Value) Then
                 If groups(j).Value = "" Or "N/A" Then
                    Worksheets("properties").Range("P" & a.Row).Value = "N/A"
                    Goto Here
                 Else
                    anvalue = coeff(j).Value * Range("AC" & a.Row).Value
                 End If
            End If
            If UCase(Range("AD" & a.Row).Value) = UCase(groups(j).Value) Then
                cavalue = coeff(j).Value * Worksheets("properties").Range("AE" & a.Row).Value
            If UCase(Range("AF" & a.Row).Value) = UCase(groups(j).Value) Then
                cb1value = coeff(j).Value * Worksheets("properties").Range("AG" & a.Row).Value
            End If
            If UCase(Range("AH" & a.Row).Value) = UCase(groups(j).Value) Then
                cb2value = coeff(j).Value * Worksheets("properties").Range("AI" & a.Row).Value
            End If
Here:
       Next j
            If UCase(Range("AD" & a.Row).Value) = UCase("[MIm]") Then
                cavalue = Range("AE" & a.Row) * Worksheets("solubility").Range("B2").Value + Range("AE" & a.Row) * Worksheets("solubility").Range("B7").Value
            End If
        nextrow = Worksheets("properties").Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
        Worksheets("properties").Range("P" & nextrow).Value = _
            anvalue + cavalue + cb1value + cb2value + Worksheets("solubility").Range("b34").Value
    Next a
End Sub
 
Upvote 0
Hey Comfy, thanks for the help. But I am still receiving the same error message. I also think your first placement of 'Here:' is what I want. If the condition is met I want the loop to stop searching through the 'j' values and to move on to the next 'a' value. However the same error persists regardless of where I place the 'Here:'
 
Upvote 0
Ok can try something else.

If the j loop is skipped do you want to execute the code after the j loop or just start at the next a?
 
Upvote 0
Code: 
Sub solubility()
 
    Dim coeff As Range, groups As Range
    Dim anion As Range
    Dim a As Range
    Dim nextrow As Long
    Dim j As Range
    Dim Skipped As Boolean
    
    Worksheets("properties").Select
    Range("P7:P2000").Select
    Selection.ClearContents
 
    'solubility groups range
    groups = Worksheets("Solubility").Range("A2:A33")
    'group coefficients range
    coeff = Worksheets("Solubility").Range("B2:B33")
    anion = Worksheets("properties").Range("AB7:AB887")

    For Each a In anion
    Skipped = False
       For Each j In groups
            If UCase(a.Value) = UCase(groups(j).Value) Then
                 If groups(j).Value = "" Or "N/A" Then
                    Worksheets("properties").Range("P" & a.Row).Value = "N/A"
                    Skipped = True
                    Exit For
                 Else
                    anvalue = coeff(j).Value * Range("AC" & a.Row).Value
                 End If
            End If
            If UCase(Range("AD" & a.Row).Value) = UCase(groups(j).Value) Then
                cavalue = coeff(j).Value * Worksheets("properties").Range("AE" & a.Row).Value
            End If ' added this missing End if.
            If UCase(Range("AF" & a.Row).Value) = UCase(groups(j).Value) Then
                cb1value = coeff(j).Value * Worksheets("properties").Range("AG" & a.Row).Value
            End If
            If UCase(Range("AH" & a.Row).Value) = UCase(groups(j).Value) Then
                cb2value = coeff(j).Value * Worksheets("properties").Range("AI" & a.Row).Value
            End If
       Next j
       If Skipped = False Then
            If UCase(Range("AD" & a.Row).Value) = UCase("[MIm]") Then
                cavalue = Range("AE" & a.Row) * Worksheets("solubility").Range("B2").Value + Range("AE" & a.Row) * Worksheets("solubility").Range("B7").Value
            End If
        nextrow = Worksheets("properties").Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
        Worksheets("properties").Range("P" & nextrow).Value = _
            anvalue + cavalue + cb1value + cb2value + Worksheets("solubility").Range("b34").Value
        End If
    Next a
End Sub

I think I found the issue. You were missing an End if.

When evaluating object in Ifs, if your not using the FALSE part of the statement you can write IFS like this so that you dont need to type End if.

Code: 
If UCase(Range("AD" & a.Row).Value) = UCase(groups(j).Value) Then cavalue = coeff(j).Value * Worksheets("properties").Range("AE" & a.Row).Value
            If UCase(Range("AF" & a.Row).Value) = UCase(groups(j).Value) Then cb1value = coeff(j).Value * Worksheets("properties").Range("AG" & a.Row).Value
            If UCase(Range("AH" & a.Row).Value) = UCase(groups(j).Value) Then _
                cb2value = coeff(j).Value * Worksheets("properties").Range("AI" & a.Row).Value 'another example

Edit: Try the previous code that has been posted (using the GoTo) but add the missing End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Creating workbooks for a team of staff
Replies
1
Views
200
HighAndWilder
H
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
306
tixarah
T
R
  • Question Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
192
rsolanki
R
P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
481
Pi_Lover
P
S
  • Question Question
Copy and paste multiple matching (multiple sheet) then insert rows to a new sheet
Replies
1
Views
276
SamKhem
S

Forum statistics

Threads
1,223,956
Messages
6,175,607
Members
452,660
Latest member
Zatman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top