VBA Help, Correct way to repeat code

[mrkambo]

Hi!!

Im wondering if anyone can help, below is the code ive written so far and as it is, works well but what i need is the route number to be a variable which can be read from a cell range ie if the route range is C1:C70, it uses the value of C1 then moves onto C2 then C3 etc, im not sure whats the correct way to achieve this.

also the pcell variable id like to do a similar thing with this, ie starts at 10, then at the end of the code + 1 then runs the code again with a new route from the range above

Im sure what i need is simple, i just can't seem to work out the best way to do it

Any help would be greatly appreciated!

    SCAM = 1041
    SCPM = 1042
    r300d = 0
    r300c = 0
    
    Route = 300 'Route Number
    pcell = 10  'Cell Paste Location
    
    For n = 8 To lastRow
    If (Left(Sheets("Source").Cells(n, 3), 7) = SCAM & Route) And (Left(Sheets("Source").Cells(n, 10), 3) = "Del") Or _
        (Left(Sheets("Source").Cells(n, 3), 7) = SCPM & Route) And (Left(Sheets("Source").Cells(n, 10), 3) = "Del") Then
        r300d = r300d + 1
    End If
    Next n
    
    For n = 8 To lastRow
    If (Left(Sheets("Source").Cells(n, 3), 7) = SCAM & Route) And (Left(Sheets("Source").Cells(n, 10), 3) = "Col") Or _
        (Left(Sheets("Source").Cells(n, 3), 7) = SCPM & Route) And (Left(Sheets("Source").Cells(n, 10), 3) = "Col") Then
        r300c = r300c + 1
    End If
    Next n
    
    For n = 8 To lastRow
    If (Left(Sheets("Source").Cells(n, 3), 7) = SCAM & Route) Then
    Sheets("results").Cells(6, 3).Value = Sheets("Source").Cells(n, 1).Value
    Sheets("results").Cells(pcell, 2).Value = Sheets("Source").Cells(n, 13).Value
    Sheets("results").Cells(pcell, 3).Value = r300c
    Sheets("results").Cells(pcell, 4).Value = r300d
    End If
    Next n

 

[Jeffrey Mahoney]

Please accept the code changes I made.

As far as your first request to turn Route into a variable, I have questions. For every iteration of:
"For n = 8 To lastRow"
you want to test if the combination of SCAM & Route meet the criteria of the cells? Are you then needing to increase the value of r300d each time the criteria is met?

Jeff

Sub IDunno()


  Dim Source As Worksheet
  Dim Rsht As Worksheet
  
  Set ssht = Sheets("Source")
  Set Rsht = Sheets("Results")
  
  SCAM = 1041
    SCPM = 1042
    r300d = 0
    r300c = 0
    
    Route = 300 'Route Number
    pcell = 10  'Cell Paste Location
    
    With ssht
      For n = 8 To lastRow
        
        If (Left(.Cells(n, 3).Value, 7) = SCAM & Route) And (Left(.Cells(n, 10).Value, 3) = "Del") Or _
            (Left(.Cells(n, 3).Value, 7) = SCPM & Route) And (Left(.Cells(n, 10).Value, 3) = "Del") Then
            r300d = r300d + 1
        End If
        If (Left(.Cells(n, 3).Value, 7) = SCAM & Route) And (Left(.Cells(n, 10).Value, 3) = "Col") Or _
            (Left(.Cells(n, 3).Value, 7) = SCPM & Route) And (Left(.Cells(n, 10).Value, 3) = "Col") Then
            r300c = r300c + 1
        End If
      Next n
    End With
    
    With Rsht
      For n = 8 To lastRow
        If (Left(ssht.Cells(n, 3).Value, 7) = SCAM & Route) Then
          .Cells(6, 3).Value = .Cells(n, 1).Value
          .Cells(pcell, 2).Value = ssht.Cells(n, 13).Value
          .Cells(pcell, 3).Value = r300c
          .Cells(pcell, 4).Value = r300d
        End If
      Next n
    End With
    
End Sub

 

[mrkambo]

Apologies for the late reply, i've only just seen the notification

I was playing around with it a lot last night and managed to get it to work the way i wanted to, not sure if the code is efficient but it does produce the results i need

SCAM = 1041
SCPM = 1042
r300d = 0
r300c = 0
pcell = 9
Lastrowa = Sheets("Admin").Cells(Rows.Count, "W").End(xlUp).Row + 1
    
    For q = 1 To Lastrowa
        r300d = 0
        For n = 8 To lastRow
            If (Left(Sheets("Source").Cells(n, 3), 7) = SCAM & Worksheets("Admin").Cells(q, 23).Value) And (Left(Sheets("Source").Cells(n, 10), 3) = "Del") Or _
            (Left(Sheets("Source").Cells(n, 3), 7) = SCPM & Worksheets("Admin").Cells(q, 23).Value) And (Left(Sheets("Source").Cells(n, 10), 3) = "Del") Then
            r300d = r300d + 1
            End If
        Next n
        r300c = 0
        For n = 8 To lastRow
            If (Left(Sheets("Source").Cells(n, 3), 7) = SCAM & Worksheets("Admin").Cells(q, 23).Value) And (Left(Sheets("Source").Cells(n, 10), 3) = "Col") Or _
            (Left(Sheets("Source").Cells(n, 3), 7) = SCPM & Worksheets("Admin").Cells(q, 23).Value) And (Left(Sheets("Source").Cells(n, 10), 3) = "Col") Then
            r300c = r300c + 1
            End If
        Next n
        
        For n = 8 To lastRow
            If (Left(Sheets("Source").Cells(n, 3), 7) = SCAM & Worksheets("Admin").Cells(q, 23).Value) Then
                Sheets("results").Cells(6, k).Value = Sheets("Source").Cells(n, 1).Value
                Sheets("results").Cells(pcell, j).Value = Sheets("Source").Cells(n, 13).Value
                Sheets("results").Cells(pcell, k).Value = r300c
                Sheets("results").Cells(pcell, l).Value = r300d
            End If
        Next n
        pcell = pcell + 1
    Next q