CODEWITHSUNDEEP
excelvba
user10434129
user10434129

Reputation: 47

Iterative Variable Value Assignment in For Loop VBA

I am stuck with a VBA code and badly need your help. Here's what I am trying to achieve -

Step 1 : I have to iteratively capture strings in column D (the count of which is mentioned in C2 which will change depending on the data set)

Step 2 : Each of these strings will be split based on "_" and I have to re-combine them since I only need the 2nd, 1st, 3rd, 5th part (in that particular order) - represented further down in column D under 'Output'

Step 3 : I have to join the newly made string in column D and strings in column F and create all possible combinations - represented further down in column F under 'Output'

Step 4 : Supply all created combinations to range C in a different sheet (stuck at this step) :(

Below is the code I was trying to create, which for some reason is not looping through i. It just keeps giving me value 4. Is it because we can't dynamically assign and flush values of variables in a loop? temp_string in my case. I have tried all the other varieties of loop but no help. I think I am fundamentally missing something here. Any advise is highly appreciated!

Sub Test()
Dim a_count As Integer, i As Integer
Dim temp_string As String, temp_substring As String
Dim o_array() As String

a_count = Sheet7.Range("C2")

For i = 2 To a_count
   temp_string = Sheet7.Range("D" & i)
   o_array = Split(temp_string, "_")
   temp_substring = o_array(1) & "_" & o_array(0) & "_" & o_array(2) _
   & "_" & o_array(4)
Next i

Debug.Print i, temp_substring
End Sub

Input & Output

Upvotes: 0

Views: 2153

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

This is how I'd do it.

Sub Test()

    Dim a_count As Long
    Dim iet As Range
    Dim fet As Range
    Dim rCell As Range
    Dim rCell1 As Range
    Dim array_size As Long
    Dim o_Array() As String
    Dim o_FinalOutput() As String
    Dim temp_substring As String
    Dim x As Long

    With Sheet7

        'Change so it returns the last row with data, not the count of data.
        a_count = .Range("C2")
        'or
        'a_count = .Cells(.Rows.Count, 4).End(xlUp).Row

        'These two variables will reference the full range of figures
        'in column D and column F.
        Set iet = .Range("D4", .Cells(a_count, 4))
        Set fet = .Range("F4", .Cells(.Rows.Count, 6).End(xlUp))

    End With

    'Figure out the size of the final array.
    array_size = iet.Cells.Count * fet.Cells.Count
    ReDim o_FinalOutput(1 To array_size)

    'Step through each cell in column D.
    For Each rCell In iet
        o_Array = Split(rCell, "_")
        temp_substring = o_Array(1) & "_" & _
                         o_Array(0) & "_" & _
                         o_Array(2) & "_" & _
                         o_Array(4) & "_"

        'Step through each cell in column F and stick it to column D text.
        For Each rCell1 In fet
            x = x + 1
            o_FinalOutput(x) = temp_substring & rCell1
        Next rCell1

    Next rCell

    'Dump the whole array into Sheet2 starting at cell C1.
    With Sheet2
        .Range("C1", .Cells(array_size, 3)) = WorksheetFunction.Transpose(o_FinalOutput)
    End With

End Sub

You may want to read up on Cells which is basically the same as Range except it looks at a single cell and uses row and column numbers.

You'll also need to adjust some figures to put data on the correct rows.

E.g.
.Range("C1", .Cells(array_size, 3)) - array_size will only work if you're starting on row 1. To start on row 2 you'll need to add 1 to the array_size: .Range("C2", .Cells(array_size+1, 3))

Upvotes: 1

Related Questions