CODEWITHSUNDEEP
excelvbaexcel-formula
GoneFishin
GoneFishin

Reputation: 49

Loop multiple split functions

I'm new to VBA, so thank you in advance for your patience. I wrote a sub that takes the part number (PN) in Range C2 and performs three different split and left functions to fill in the columns to the left and right of the PN with extracted portions of the PN string. Here is a screenshot of the columns and what it fills in.

Here is my code so far:

Sub PN_Autotfill1()
Dim PN As Range
Dim SCPort_Type As Range
Dim SCPort_Size As Range
Dim Start_FittingSize As Range
Dim PN_String As String
Dim PN_1 As Variant
Dim PN_2 As Variant
Dim PN_3 As Variant

Set PN = Range("C2")
Set SCPort_Type = PN.Offset(, -2)
Set SCPort_Size = PN.Offset(, -1)
Set Start_FittingSize = PN.Offset(, 1)
PN_String = PN.Value
If InStr(PN_String, "Flange") > 0 Then
    'Splits PN into SC Port Type
    PN_1 = Split(PN_String, "#")(1)
    PN_2 = Left(PN_1, 2)
    SCPort_Type.Value = "#" & PN_2 & "Flange"
    
    'Splits PN into SC Port Size, Start, and End Fitting
    PN_3 = Split(PN_1, "-")(1)
    SCPort_Size = PN_3
    Start_FittingSize = PN_3
End If
End Sub

Now I want to make a loop that applies these functions to each cell containing a PN in column C. I've found some good examples on Stackoverflow and a VBA tutorial website that create loops for a single split function, but not for multiple split functions. It looks like two For loops will come into play: LastRow = Cells(Rows.Count, "C").End(xlUp).Row with For a = 2 To LastRow, and For i = 1 To UBound(Unsure what goes here). Does anyone have tips or example code for how to go about this? Thank you in advance for any help!

Here is the code with Jamheadart's answer integrated in:

Sub PN_Autotfill_Functions(PN As Range)
Dim SCPort_Type_Size As Range
Dim Start_FittingSize As Range
Dim PN_String As String
Dim LastRow As Single
Dim PN_1 As Variant
Dim PN_2 As Variant
Dim PN_3 As Variant

Set SCPort_Type_Size = PN.Offset(, -1)
Set Start_FittingSize = PN.Offset(, 1)
PN_String = PN.Value

LastRow = Cells(Rows.Count, "C").End(xlUp).Row

    If InStr(PN_String, "Flange") > 0 Then
        'Splits PN into SC Port Type and Size, then combines results
        PN_1 = Split(PN_String, "#")(1)
        PN_2 = Left(PN_1, 2)
        PN_3 = Split(PN_1, "-")(1)
        SCPort_Type_Size.Value = "#" & PN_2 & " Flange" & ", -" & PN_3
    
        'Fills in Start and End Fitting Size based on previous Split of PN
        Start_FittingSize = PN_3
    End If
End Sub

Sub PN_Autofill_Loop()
Dim a As Long
Dim PN As Range

Set PN = ActiveCell
    For a = 2 To 11
        PN_Autotfill_Functions Range("C" & a)
    Next a
End Sub

Upvotes: 1

Views: 194

Answers (1)

jamheadart
jamheadart

Reputation: 5313

You don't need multiple loops, you just need to run your sub in a loop - and each time you run it, it will take in a range (e.g. C2)

So change your routine first line to this:

Sub PN_Autotfill1(PN as Range)

And get rid of these two lines:

Dim PN As Range
Set PN = Range("C2")

This means PN is now a parameter for the routine, instead of it being defined in the routine itself.

You could then call it for a few ranges, like this:

Sub Testing()
PN_Autotfill1 Range("C2")
PN_Autotfill1 Range("C4")
PN_Autotfill1 Range("C7")
End Sub

And finally if you want to loop through say ten rows you could call it in a loop with a different sub routine:

Sub LoopingExample
Dim i As Long
For i = 2 to 11
    PN_Autotfill1 Range("C" & i)
Next i
End Sub

It's worth noting that this ease is only possible because your original code is constructed quite well (e.g. it's using Offset instead of hard-coded ranges etc.)

Upvotes: 1

Related Questions