SOLVED

VBA Loop Code

%3CLINGO-SUB%20id%3D%22lingo-sub-2309729%22%20slang%3D%22en-US%22%3EVBA%20Loop%20Code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2309729%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20student%20struggling%20with%20some%20coding%2C%20I'm%20trying%20to%20run%20a%20loop%20inside%20of%20a%20loop%20so%20that%20the%20first%20loop%20goes%20down%20and%20the%20second%20goes%20across.%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%20me%20fix%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20code%20below%3A%3C%2FP%3E%3CP%3EDim%20Sem1Total%20As%20Variant%2C%20Sem2Total%20As%20Variant%2C%20TotalCredits%20As%20Variant%3CBR%20%2F%3EDim%20RQBnumCol%20As%20Variant%2C%20OptionCode%20As%20Variant%3CBR%20%2F%3EDim%20RQBnumRow%20As%20Variant%3CBR%20%2F%3EDim%20RQBNum%20As%20Range%2C%20ELBNum%20As%20Range%3C%2FP%3E%3CP%3ESet%20RQBNum%20%3D%20RWS.Range(%22A2%22)%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20ELBNum%20%3D%20ErrorLogWS.Range(%22A2%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20Until%20RQBNum%20%3D%20%22%22%20'Go%20down%20till%20nothing%3CBR%20%2F%3EDoEvents%3CBR%20%2F%3ERQBnumRow%20%3D%200%3CBR%20%2F%3EOn%20Error%20Resume%20Next%26nbsp%3B%3CBR%20%2F%3ERQBnumRow%20%3D%20WorksheetFunction.Match(RQBNum%2C%20ErrorLogWS.Columns(%22A%22)%2C%200)%20'MATCH%3CBR%20%2F%3EOn%20Error%20GoTo%200%26nbsp%3B%3CBR%20%2F%3ESet%20OptionCode%20%3D%20RQBNum.Cells(1%2C%202)%3CBR%20%2F%3ESet%20ELBNum%20%3D%20ErrorLogWS.Range(%22A2%22)%3CBR%20%2F%3ESet%20Sem1Total%20%3D%20ELBNum.Cells(1%2C%205)%3CBR%20%2F%3ESet%20Sem2Total%20%3D%20ELBNum.Cells(1%2C%206)%3CBR%20%2F%3ESet%20TotalCredits%20%3D%20ELBNum.Cells(1%2C%207)%3CBR%20%2F%3EDo%20Until%20OptionCode%20%3D%20%22%22%3CBR%20%2F%3EDoEvents%3CBR%20%2F%3EIf%20OptionCode%20Like%20%22SBC1%5B0-2%5D0%22%20Then%20'1st%20Semester%2020cred%3CBR%20%2F%3ESem1Total.Cells%20%3D%20Sem1Total%20%2B%2020%3CBR%20%2F%3EElseIf%20OptionCode%20Like%20%22SBC1%5B3-6%5D0%22%20Then%20'1st%20Semester%2010cred%3CBR%20%2F%3ESem1Total.Cells%20%3D%20Sem1Total%20%2B%2010%3CBR%20%2F%3EElseIf%20OptionCode%20Like%20%22SBC1%5B7-9%5D0%22%20Then%20'2nd%20Semester%2020cred%3CBR%20%2F%3ESem2Total.Cells%20%3D%20Sem2Total%20%2B%2020%3CBR%20%2F%3EElseIf%20OptionCode%20Like%20%22SBC2%5B0-3%5D0%22%20Then%20'2nd%20Semester%2010cred%3CBR%20%2F%3ESem2Total.Cells%20%3D%20Sem2Total%20%2B%2010%3CBR%20%2F%3EElseIf%20OptionCode%20%3D%20%22SBC240%22%20Then%20'Dissertation%3CBR%20%2F%3ETotalCredits.Cells%20%3D%20Sem1Total%20%2B%20Sem2Total%20%2B%2030%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ESet%20OptionCode%20%3D%20OptionCode.Cells(1%2C%202)%3CBR%20%2F%3ELoop%3CBR%20%2F%3ESet%20RQBNum%20%3D%20RQBNum.Cells(2%2C%201)%20'Moving%201%20B%20num%20down%3CBR%20%2F%3ELoop%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2309729%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2309944%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Loop%20Code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2309944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041592%22%20target%3D%22_blank%22%3E%40TSmith98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20problem%20you're%20having%20with%20the%20code%3F%3C%2FP%3E%0A%3CP%3EIt%20might%20help%20if%20you%20attached%20a%20copy%20of%20the%20workbook%20(without%20sensitive%20information)%20-%20it%20is%20hard%20to%20follow%20what%20the%20code%20does%20without%20seeing%20what%20it%20operates%20on.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310019%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Loop%20Code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310019%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20attached%20the%20file%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310178%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Loop%20Code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041592%22%20target%3D%22_blank%22%3E%40TSmith98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20change%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFor%20i%20%3D%202%20To%20MCLastRow%0A%20%20%20%20If%20Cells(i%2C%202).Value%20%3D%20%22SBUB10%22%20Then%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'Code%20not%20recognising%20this%20'Change%20to%20ending%20with%2010%0A%20%20%20%20ErrorLogWS.Range(%22c%22%20%26amp%3B%20i).Value%20%3D%2040%0A%20%20%20%20ErrorLogWS.Range(%22D%22%20%26amp%3B%20i).Value%20%3D%2030%0A%20%20%20%20ElseIf%20Cells(i%2C%202).Value%20%3D%20%22SBUB20%22%20Or%20Cells(i%2C%202).Value%20%3D%20%22SBUB30%22%20Then%0A%20%20%20%20ErrorLogWS.Range(%22c%22%20%26amp%3B%20i).Value%20%3D%2030%0A%20%20%20%20ErrorLogWS.Range(%22D%22%20%26amp%3B%20i).Value%20%3D%2040%0A%20%20%20%20Else%0A%20%20%20%20End%20If%0ANext%20i%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%20%20%20%20For%20i%20%3D%202%20To%20MCLastRow%0A%20%20%20%20%20%20%20%20Select%20Case%20ErrorLogWS.Cells(i%2C%202).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22SBUB10%22%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'Code%20not%20recognising%20this%20'Change%20to%20ending%20with%2010%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ErrorLogWS.Range(%22c%22%20%26amp%3B%20i).Value%20%3D%2040%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ErrorLogWS.Range(%22D%22%20%26amp%3B%20i).Value%20%3D%2030%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22SBUB20%22%2C%20%22SBUB30%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ErrorLogWS.Range(%22c%22%20%26amp%3B%20i).Value%20%3D%2030%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ErrorLogWS.Range(%22D%22%20%26amp%3B%20i).Value%20%3D%2040%0A%20%20%20%20%20%20%20%20End%20Select%0A%20%20%20%20Next%20i%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20makes%20Cells(i%2C%202)%20explicitly%20refer%20to%20ErrorLogWS.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20code%20still%20doesn't%20want%20what%20you%20do%2C%20please%20provide%20detailed%20information%20about%20the%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310181%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Loop%20Code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310181%22%20slang%3D%22en-US%22%3EHey%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20this%2C%20but%20that%20part%20is%20working.%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20is%20the%20section%20after%20that%20part%20that%20starts%20with%3A%3CBR%20%2F%3E''Code%20to%20find%20each%20semester%20and%20total%20credits'''%3CBR%20%2F%3Ewhere%20there%20are%20two%20do%20loops%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310229%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Loop%20Code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041592%22%20target%3D%22_blank%22%3E%40TSmith98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20what%20exactly%20is%20the%20problem%20there%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, 

 

I am a student struggling with some coding, I'm trying to run a loop inside of a loop so that the first loop goes down and the second goes across.

Can someone please help me fix it?

 

See code below:

Dim Sem1Total As Variant, Sem2Total As Variant, TotalCredits As Variant
Dim RQBnumCol As Variant, OptionCode As Variant
Dim RQBnumRow As Variant
Dim RQBNum As Range, ELBNum As Range

Set RQBNum = RWS.Range("A2")

Set ELBNum = ErrorLogWS.Range("A2")

 

Do Until RQBNum = "" 'Go down till nothing
DoEvents
RQBnumRow = 0
On Error Resume Next 
RQBnumRow = WorksheetFunction.Match(RQBNum, ErrorLogWS.Columns("A"), 0) 'MATCH
On Error GoTo 0 
Set OptionCode = RQBNum.Cells(1, 2)
Set ELBNum = ErrorLogWS.Range("A2")
Set Sem1Total = ELBNum.Cells(1, 5)
Set Sem2Total = ELBNum.Cells(1, 6)
Set TotalCredits = ELBNum.Cells(1, 7)
Do Until OptionCode = ""
DoEvents
If OptionCode Like "SBC1[0-2]0" Then '1st Semester 20cred
Sem1Total.Cells = Sem1Total + 20
ElseIf OptionCode Like "SBC1[3-6]0" Then '1st Semester 10cred
Sem1Total.Cells = Sem1Total + 10
ElseIf OptionCode Like "SBC1[7-9]0" Then '2nd Semester 20cred
Sem2Total.Cells = Sem2Total + 20
ElseIf OptionCode Like "SBC2[0-3]0" Then '2nd Semester 10cred
Sem2Total.Cells = Sem2Total + 10
ElseIf OptionCode = "SBC240" Then 'Dissertation
TotalCredits.Cells = Sem1Total + Sem2Total + 30
End If
Set OptionCode = OptionCode.Cells(1, 2)
Loop
Set RQBNum = RQBNum.Cells(2, 1) 'Moving 1 B num down
Loop

8 Replies

@TSmith98 

What is the problem you're having with the code?

It might help if you attached a copy of the workbook (without sensitive information) - it is hard to follow what the code does without seeing what it operates on.

Hello,

I have attached the file

@TSmith98 

I would change

For i = 2 To MCLastRow
    If Cells(i, 2).Value = "SBUB10" Then                      'Code not recognising this 'Change to ending with 10
    ErrorLogWS.Range("c" & i).Value = 40
    ErrorLogWS.Range("D" & i).Value = 30
    ElseIf Cells(i, 2).Value = "SBUB20" Or Cells(i, 2).Value = "SBUB30" Then
    ErrorLogWS.Range("c" & i).Value = 30
    ErrorLogWS.Range("D" & i).Value = 40
    Else
    End If
Next i

to

    For i = 2 To MCLastRow
        Select Case ErrorLogWS.Cells(i, 2).Value
            Case "SBUB10"                      'Code not recognising this 'Change to ending with 10
                ErrorLogWS.Range("c" & i).Value = 40
                ErrorLogWS.Range("D" & i).Value = 30
            Case "SBUB20", "SBUB30"
                ErrorLogWS.Range("c" & i).Value = 30
                ErrorLogWS.Range("D" & i).Value = 40
        End Select
    Next i

This makes Cells(i, 2) explicitly refer to ErrorLogWS.

 

If the code still doesn't want what you do, please provide detailed information about the problem.

Hey,

Thank you for this, but that part is working.

It is the section after that part that starts with:
''Code to find each semester and total credits'''
where there are two do loops

@TSmith98 

And what exactly is the problem there?

My goal is for the number of credits to be added to each B number but instead
All of the credits are being added to the first B number
best response confirmed by TSmith98 (Occasional Contributor)
Solution

@TSmith98 

Is this better? Please check carefully.

Thank you, this is useful, I had to change a few things but was good!