Selecting rows with content from a list and inserting them into the correct spots in a preexisting list located above it.

[JEddl]

I have never worked in VBA before and have only used Excel for the basics. I am trying to make a script that can be used to interact with several hundred different spreadsheets all of the same format. Other items exist in the spread sheets, but my task is to take 2 numbered lists, one of process steps, and one of expected results and put a * in front of process step numbers and + in front of the expected results numbers. In cases where the expected result text is not blank I need to insert that row under the coresponding number in the process steps list. The first list always starts on row 25 and the second always starts two rows under where the first list ends both are numbered in column C with the text in column D and may be of varying lengths. Column D in expected results is more often than not blank. Also there is a 3rd list under expected results in column C and D, but it is to be left alone. Any help would be appreciated.

Essentially I am trying to make a script that will change this.

Process Steps List


1 Step1
2 Step2
3 Step3
4 Step4
5 Step5
6 Step6
7 Step7
8 Step8
9 Step9
10 Step10
11 Step11
12 Step12
13 Step13
14 Step14
15 Step15


Expected Results
1
2
3 Expected ResultA
4
5
6
7 Expected ResultB
8
9
10
11
12 Expected ResultC
13
14
15 Expected ResultD


To something like this.
Process Steps List
*1 Step1
*2 Step2
*3 Step3
+3 Expected ResultA
*4 Step4
*5 Step5
*6 Step6
*7 Step7
+7 Expected ResultB
*8 Step8
*9 Step9
*10 Step10
*11 Step11
*12 Step12
+12 Expected ResultC
*13 Step13
*14 Step14
*15 Step15
+15 Expected ResultD

It really is a simple thing to do manually but I have had no luck figuring out how to make it go through the second list and find the items without going into the (not pictured) 3rd list and then place them correctly in the first list

 

[JLGWhiz]

You might want to re-think the type of symbols you use in this scenario. Excel is pretty finicky about how its math operators are utilized.

Sub combineStuff()
Dim c As Range, cl As Range, fn As Range
    With ActiveSheet
    For Each c In .Range("C25", .Cells(25, 3).End(xlDown))
        c = "*" & c.Value
    Next
    Set cl = .Range("C25").End(xlDown)(3)
    For Each c In .Range(cl, cl.End(xlDown))
        c = "'+" & c.Value
        If c.Offset(, 1) <> "" Then
            c.Resize(1, 2).Copy
            Set fn = .Range("C25", .Cells(25, 3).End(xlDown)).Find("*" & Mid(c.Value, 2), , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fn.Offset(1, 0).Resize(1, 2).Insert
                End If
                Set fn = Nothing
        End If
    Next
    End With
    Application.CutCopyMode = False
End Sub

 

[JEddl]

Thanks JLGWhiz!! From reading the code (as best I can in VBA) it looks very much like what I am after and way ahead of anything I had come up with. However when I applied it to a mock-up I got a "runtime error 13 type mismatch" with the yellow highlight bar on this line c = "*" & c.Value I have not had a chance to look up what is behind that yet.

 

[JEddl]

The error was the result of one of the numbers in the spread sheet being a result of a formula, not because of the code. I am going tackle that and run it on a bunch of them tomorrow to QC, but this problem looks solved. Thanks!!!

 

[JLGWhiz]

The error was the result of one of the numbers in the spread sheet being a result of a formula, not because of the code. I am going tackle that and run it on a bunch of them tomorrow to QC, but this problem looks solved. Thanks!!!

You're welcome,
Regards, JLG