Forum Discussion

TSmith98's avatar
TSmith98
Copper Contributor
Apr 30, 2021
Solved

VBA Loop Code

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

Macros and VBA

8 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 30, 2021

    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.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 30, 2021

        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.

Resources