VBA代码 [英] VBA code
问题描述
1.如何根据以下要求推荐vba代码
1.how to approch vba code for below requirement
2.sheet1具有国家/地区名称。一旦循环通过了临时名称值将在相应的表单名称列"A"中返回。 retun值为"1"
2.sheet1 having country names. once loop gone through counrty name value will be return in respective sheet name column "A" retun value is "1"
输出文件
推荐答案
请尝试以下代码。如果国家/地区名称的工作表不存在,则会添加工作表。
Try the following code. If the worksheet for the country name does not exist then it will add the worksheet.
Sub TestLoop()
Dim rngToProcess As Range
Dim cel As Range
Dim wsCtry As Worksheet
使用工作表("Sheet1") '编辑'Sheet1"列表名称为
设置rngToProcess = .Range(.Cells(2," A"),。Cell(.Rows.Count," A")。End(xlUp))
结束与$
对于每个cel in rngToProcess
设置wsCtry = Nothing
On Error Resume Next
设置wsCtry =工作表(cel.Value)
On Error GoTo 0
如果wsCtry没什么,那么 如果工作表尚不存在,则无效.b
'添加工作表(如果它尚不存在)
设置wsCtry = Sheets.Add(After:= Sheets(Sheets.Count)))
wsCtry.Name = cel.Value
结束如果
使用wsCtry为
.Cells(.Rows.Count," A")。End(xlUp).Offset(1,0)= 1
结束与$
下一个cel
End Sub
Sub TestLoop()
Dim rngToProcess As Range
Dim cel As Range
Dim wsCtry As Worksheet
With Worksheets("Sheet1") 'Edit "Sheet1" to name of sheet with list
Set rngToProcess = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each cel In rngToProcess
Set wsCtry = Nothing
On Error Resume Next
Set wsCtry = Worksheets(cel.Value)
On Error GoTo 0
If wsCtry Is Nothing Then 'Will be nothing if sheet does not already exist
'Add the worksheet if it does not already exist
Set wsCtry = Sheets.Add(After:=Sheets(Sheets.Count))
wsCtry.Name = cel.Value
End If
With wsCtry
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = 1
End With
Next cel
End Sub
这篇关于VBA代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!