Simple Excel VBA Loop needed

Question

I'm trying to make a simple loop in Excel VBA that grabs info from a row and copies that same info into the next following row (or insert a new one) depending on the value on a specific cell.

For example, row 1 has these values (house 1a, 3 levels)

then create the result on cell H5 like this

(house 1a, bottom level)
(house 1a, mid level)
(house 1a, top level)

meaning each level of house 1a was split into 3 rows. then imagine I would have a list of ten more houses with different levels up to 3, randomly.

sorry about the parenthesis but this editor keeps thinking i'm writing code.

any help would be greatly appreciated.

Are you sure you need VBA and not a formula that can pull this info? — BruceWayne
– BruceWayne, Commented Jan 19, 2016 at 21:14

jspek · Accepted Answer · 2016-01-20 02:02:09Z

I'm assuming that the source column is column A (1), and the output row is column B (2).

One possible solution to problem is using a regular expression and a while loop.

To enable regular expression in VBA go into the editor (VBE).

Click on Tools Tab.
Click on References
Check Microsoft VBScript Regular Expressions 5.5

To run the code create a separate module and insert this:

Sub splitCol()

    'Variables used in the regular expression
    Dim re As New RegExp
    Dim mc As MatchCollection

    'Variables used for row looping
    Dim sourceRow As Integer
    Dim targetRow As Integer

    'Variables used for output string
    Dim numberOfLevels As Integer
    Dim currentLevel As Integer
    Dim houseName As String

    'Regular expression patter
    re.Pattern = "(.+)?\,+\s+?(\d+)+\s+?levels"

    'With the asumption that Row 1 are headers I'm starting at row 2
    sourceRow = 2
    targetRow = 2
    numberOfLevels = 0

    'Loop through input row until blank is found
    While (Cells(sourceRow, 1).Value <> "")
        'Execute the regular expression
        Set mc = re.Execute(Cells(sourceRow, 1).Value)

        'Only create output string if regular expression was able to
        'executed without a problem
        If mc.Count > 0 Then
            houseName = mc(0).SubMatches(0)
            numberOfLevels = mc(0).SubMatches(1)

            currentLevel = 1
            While currentLevel <= numberOfLevels
                Cells(targetRow, 2).Value = houseName & " , Level " & CStr(currentLevel)
                targetRow = targetRow + 1
                currentLevel = currentLevel + 1
            Wend
        End If
        sourceRow = sourceRow + 1
    Wend

End Sub

thanks a lot, i'll give it a try tomorrow! sorry for the noob formatting or lack there of, first poster here, long time lurker.

Collectives™ on Stack Overflow

Simple Excel VBA Loop needed

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related