Apr 30 2021 01:49 AM - edited Apr 30 2021 03:09 AM
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
Apr 30 2021 02:48 AM
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.
Apr 30 2021 03:47 AM
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.
Apr 30 2021 03:50 AM
Apr 30 2021 04:00 AM
Apr 30 2021 06:55 AM
SolutionIs this better? Please check carefully.
May 01 2021 07:11 AM
Apr 30 2021 06:55 AM
Solution