How to Merge Multiple VBA scripts as one?

[NeoSez]

I am trying to merge these two scripts together and it's not working. Ideally, I would like to merge multiple scripts together.
Also, do I insert these as a module or right in the VBA sheet?

Sub DataSplit_array_formulas()
    Dim sht As Worksheet
    Dim rng As Range
    Dim arrSrc As Variant, arrOut As Variant
    Dim lastRow As Long
    Dim splitCell As Variant, splitColNo As Long
    Dim maxLines As Long
    Dim i As Long, j As Long, iCol As Long, rowOut As Long
       Dim myRange As Range
  
    Set sht = Worksheets("Sheet1")  
    With sht
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = .Range(.Cells(4, "A"), .Cells(lastRow, "Z"))
        splitColNo = 19
        arrSrc = rng.FormulaR1C1
    End With
  
' Based on the number of Line Feeds get the count to dimension the output array
    For i = 1 To UBound(arrSrc)
   
        ' If delimiter is comma+space convert it to vbLF to standardise the delimiter.
        arrSrc(i, splitColNo) = Replace(arrSrc(i, splitColNo), ", ", vbLf)
        maxLines = maxLines + (Len(arrSrc(i, splitColNo)) - Len(Replace(arrSrc(i, splitColNo), vbLf, "")) + 1)
    Next i
  
    ReDim arrOut(1 To maxLines + UBound(arrSrc), 1 To UBound(arrSrc, 2))    ' Added UBound(arrSrc) to allow for blank rows
  
    For i = 1 To UBound(arrSrc)
        splitCell = Split(arrSrc(i, splitColNo), vbLf)
        For j = LBound(splitCell) To UBound(splitCell)
            rowOut = rowOut + 1
            For iCol = 1 To UBound(arrSrc, 2)
                arrOut(rowOut, iCol) = arrSrc(i, iCol)          ' should skip column 19 but it will get overwritten anyway
            Next iCol
            arrOut(rowOut, splitColNo) = splitCell(j)
        Next j
        rowOut = rowOut + 1                                     ' Insert Blank Row
    Next i
    
rng.Resize(rowOut, UBound(arrOut, 2)).Value = arrOut

Merge With

    
'   Find lastRow in column S
    lastRow = Cells(Rows.Count, "S").End(xlUp).Row

'   Set range to look at
    Set myRange = Range("S4:S" & lastRow)

'   Replace 007,
    myRange.Replace What:="007", Replacement:="JamesBond", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[StephenCrump]

... and it's not working

Can you please be a little more specific about what "not working" means?

- How are you putting the extra code snippet into your Sub?
- Are you getting a compile error? A run-time error? Or is the code just not doing what you want it to do?
- Is the Sub doing what it's supposed to?
- What about the extra code snippet? Is that "working" before you put it into the Sub?
- The code snippet includes unqualified references to Cells and Range, which will refer to the ActiveSheet. Is this what you intended, or do these references need to be qualified to a specific sheet?

 

[NeoSez]

Can you please be a little more specific about what "not working" means?

- How are you putting the extra code snippet into your Sub?
- Are you getting a compile error? A run-time error? Or is the code just not doing what you want it to do?
- Is the Sub doing what it's supposed to?
- What about the extra code snippet? Is that "working" before you put it into the Sub?
- The code snippet includes unqualified references to Cells and Range, which will refer to the ActiveSheet. Is this what you intended, or do these references need to be qualified to a specific sheet?

Thanks for looking at my post.
This two codes works fine by itself, but when I try to add another code onto the end of the previous code, it only runs the first code and not the second code snippet, when I insert it as a module.
I want to make it easier by adding a button to a different sheet in Workbook1 so anyone can move/copy the SHEET2 (sourceSheet) from another workbook2 (sourceWorkbook) into Workbook1 and run the codes by clicking on the button that is on the main sheet in Workbook1.
I hope this makes sense.
This code below is the one I want to add to the previous code and merge them together, but I don't know how to do it properly.
Is it possible to not refer to ActiveSheet and refer to the imported Sheet2? We can name it TARGET if it's easier.

Sub Remove()
    Dim lastRow As Long
    Dim myRange As Range
    
'   Find lastRow in column S
    lastRow = Cells(Rows.Count, "S").End(xlUp).Row

'   Set range to look at
    Set myRange = Range("S4:S" & lastRow)

'   Replace 007,
    myRange.Replace What:="007", Replacement:="JamesBond", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'   Replace "," ,
    myRange.Replace What:=",", Replacement:=", ", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Kaper]

Perhaps:
add this line near the top of merged macro, where othe declarations are (it is not required to have all declarations in one place, but reads better)

    Dim myRange As Range

then the part near bottom:

with  Worksheets("Sheet1") 
'   Find lastRow in column S
    lastRow = .Cells(.Rows.Count, "S").End(xlUp).Row

'   Set range to look at
    Set myRange = .Range("S4:S" & lastRow)

'   Replace 007,
    myRange.Replace What:="007", Replacement:="JamesBond", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'   Replace "," ,
    myRange.Replace What:=",", Replacement:=", ", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
end with

This is what Stephen ment by "using unqualified references"

 

[StephenCrump]

This two codes works fine by itself, but when I try to add another code onto the end of the previous code, it only runs the first code and not the second code snippet, when I insert it as a module.

This code below is the one I want to add to the previous code and merge them together, but I don't know how to do it properly.

It's still not clear what you mean by "merge"? If you put both pieces of code into one Sub, there's no reason why the first part should run, and the second part should not run.

But you could also keep the code as two separate Subs, and have your button call another Sub that does this:

Sub DoTwoThings()

    Call DataSplit_array_formulas
    Call Remove

End Sub

I want to make it easier by adding a button to a different sheet in Workbook1 so anyone can move/copy the SHEET2 (sourceSheet) from another workbook2 (sourceWorkbook) into Workbook1 and run the codes by clicking on the button that is on the main sheet in Workbook1.

@Kaper has explained above about qualifying the worksheet. If you want your macro to work with two different workbooks, you'll also need to qualify the workbook.

 

[NeoSez]

Perhaps:
add this line near the top of merged macro, where othe declarations are (it is not required to have all declarations in one place, but reads better)

    Dim myRange As Range

then the part near bottom:

with  Worksheets("Sheet1")
'   Find lastRow in column S
    lastRow = .Cells(.Rows.Count, "S").End(xlUp).Row

'   Set range to look at
    Set myRange = .Range("S4:S" & lastRow)

'   Replace 007,
    myRange.Replace What:="007", Replacement:="JamesBond", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'   Replace "," ,
    myRange.Replace What:=",", Replacement:=", ", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
end with

This is what Stephen ment by "using unqualified references"

Thank you for your explanation.

 

[NeoSez]

It's still not clear what you mean by "merge"? If you put both pieces of code into one Sub, there's no reason why the first part should run, and the second part should not run.

But you could also keep the code as two separate Subs, and have your button call another Sub that does this:

Sub DoTwoThings()

    Call DataSplit_array_formulas
    Call Remove

End Sub

@Kaper has explained above about qualifying the worksheet. If you want your macro to work with two different workbooks, you'll also need to qualify the workbook.

Thanks for this code. I couldn't get it to work for my script. Do I put both scripts in the same module or two separate modules? I want to merge the scripts together.

 

[StephenCrump]

Thanks for this code. I couldn't get it to work for my script. Do I put both scripts in the same module or two separate modules? I want to merge the scripts together.

Please post the code you are using, let us know which module(s) the code is in, and explain what you mean by "couldn't get it to work".