Using array instead of different subs in vba

Question

I have developed a vba for many systems. I am giving examples for 2 systems here:

Private Sub Macro1()

Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\System1.xls")

With x.Sheets("System1")

Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues, 
LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

.Range(aCell1, 
.Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
ThisWorkbook.Sheets("System1").Range("A2")
End With

Private Sub Macro2()

Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\System2.xls")

With x.Sheets("System2")

Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues, 
LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

.Range(aCell1, 
.Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
ThisWorkbook.Sheets("System1").Range("A2")
End With

Is there a way through which I can mention all the system name in an array or list instead of writing different subs for different systems? Since the only thing that is getting changed is the system number

Rory · Accepted Answer · 2017-08-23 11:37:37Z

3

You just need to refactor your code:

Private Sub Macro1()

GetData 1
End Sub

Private Sub Macro2()

GetData 2
End Sub

Sub GetData(systemNum as long)
Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\System" & systemNum & ".xls")

With x.Sheets("System" & systemNum)

Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues, 
LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

.Range(aCell1, 
.Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
ThisWorkbook.Sheets("System1").Range("A2")
End With
End Sub

answered Aug 23, 2017 at 11:37

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vityata · Accepted Answer · 2017-08-23 13:16:57Z

Try to add the system as a parameter. In the example below it is an optional one:

Option Explicit

Private Sub Macro1(Optional strParam As String = "System1")

    Set x = Workbooks.Open("C:\Users\****\Desktop\New folder\" & strParam & ".xls")

    With x.Sheets(strParam)

        Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues,
        LookAt:=xlWhole, _MatchCase:=False, SearchFormat:=False)

        .Range(aCell1,
        .Cells(.Rows.Count,aCell1.Column).End(xlUp)).Offset(2,0).Copy_
        ThisWorkbook.Sheets(strParam).Range ("A2")
    End With

End Sub

And this is how you call it:

Public Sub TestMe()

    Macro1 'same as Macro1 "System1"
    Macro1 "System1"
    Macro1 "System2"

End Sub

Edit: As far as you want array in the original, here is a possible solution, that you can fix using the Macro1 sub from above:

Public Sub TestMe()

    Dim myArr           As Variant
    Dim lngCounter      As Long

    myArr = Array("System1", "System2", "System3")

    For lngCounter = LBound(myArr) To UBound(myArr)
        Macro1 myArr(lngCounter)
    Next lngCounter

End Sub

Collectives™ on Stack Overflow

Using array instead of different subs in vba

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related