在vba中使用数组而不是不同的子 [英] Using array instead of different subs in vba

问题描述

我为许多系统开发了一个vba.我在这里提供2个系统的示例:

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

推荐答案

您只需要重构代码:

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

这篇关于在vba中使用数组而不是不同的子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！