JChaggar February 2016

VBA Loop within a loop

im currently looking to work in this data

say i have

4 rows the first allways starts with UO2 in column A below that will be S72's (i.e the reason for the UO2) what i need to do is have a reference for the S72s as us currently stands the only way to link the s72 to the uo2 is knowing that the s72 are directly below the u02 untill the next uo2

what i need to do is duplicate the UO2 entry for every line that the s72 relates to then shift the s72 across so i dont have duplicates

╔════╦══════════════╦══════╗
║ tr ║  Community   ║ 210  ║
╠════╬══════════════╬══════╣
║UO2 ║ -----------  ║ 5636 ║
║S72 ║ Reason       ║  148 ║
║S72 ║ reason       ║  101 ║
║S72 ║ reason       ║  959 ║
╚════╩══════════════╩══════╝

what i need the macro to do is

╔════╦══════════════╦══════╗
║ tr ║  Community   ║ S72  ║
╠════╬══════════════╬══════╣
║UO2 ║ -----------  ║Reason║
║UO2 ║ -----------  ║Reason║
║UO2 ║ -----------  ║Reason║
║UO2 ║ -----------  ║Reason║
╚════╩══════════════╩══════╝

there can be multiple reasons for the s72 and the only reference to stop is the delcaration of another UO2

Answers


JMichael February 2016

If I understand, you're basically looking to turn Column A into a column only for UO2's and move the S72 data over 2(?) columns to Column C.

If that's right, you just need two Long variables to control your looping, one for the current row being looked at, and the other as the reference for the UO2 to use. Ex:

Dim i as Long
Dim j as Long

'Start them at the same point, assumes row 1 is header
i = 2
j = 2

'Assuming that once Column A is blank, we have no more data to process
Do While Range("A" & i).value <> ""
    'Check for UO2
    If Range("A" & i).value = "UO2" 'Adjust condition appropriately
        'Update j
        j = i
    Else
        'Shift the data over (If there is data to the right of Column C already you'll
        ' need to tweak this code to not overwrite it
        Range("C" & i).value = Range("B" & i).value

        'Copy down the UO2 info
        Range("A" & i).value = Range("A" & j).value
        Range("B" & i).value = Range("B" & j).value
    End if

    'Increment i no matter the case
    i = i + 1
Loop

'You then need to loop through and delete the rows that have only UO2 data (i.e. Column C
' would be blank), but that shouldn't be too difficult

Post Status

Asked in February 2016
Viewed 1,030 times
Voted 7
Answered 1 times

Search




Leave an answer