Loop multiple split functions

Question

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

jamheadart · Accepted Answer · 2020-06-20 17:03:44Z

1

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.)

edited Jun 20, 2020 at 17:03

answered Jun 20, 2020 at 16:56

jamheadart

5,3737 gold badges37 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GoneFishin Over a year ago

Wonderful! Thank you @jamheadart . I'll give this a try and see how it goes.

GoneFishin Over a year ago

That works!!! I implemented the loop, and the only thing I added was Dim PN As Range and Set PN = ActiveCell in the loop sub. Thank you so much.

jamheadart Over a year ago

Glad to help, have fun with VBA - I got hooked a few years ago and I'm still loving it :D

Collectives™ on Stack Overflow

Loop multiple split functions

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related