我可以循环吗?
有一段时间循环这个; D2是列表开始的地方。我想让它跑到d3,d4,d5,d6 ....直到空白。另外,我将把数据放入E列,这也需要像列D一样增加; E2,E3,E4,E5,E6 ...我可以循环吗?
Sub james() 'Main Program
Dim celltxt As String
celltxt = ActiveSheet.Range("D2").Value
DELETE_EJ
If InStr(1, celltxt, "Christy", vbTextCompare) Then
Range("E2").Value = "Christy"
ElseIf InStr(1, celltxt, "Kari", vbTextCompare) Then
Range("E2").Value = "Kari"
ElseIf InStr(1, celltxt, "Sue", vbTextCompare) Then
Range("E2").Value = "Sue"
ElseIf InStr(1, celltxt, "Clayton", vbTextCompare) Then
Range("E2").Value = "Clayton"
是的,定义范围内循环过来,然后你可以使用内循环在名称列表中做这样的:
Sub foo() 'Main Program
Dim nmArr()
Dim i as Long
Dim loopRange as Range
Dim cl As Range
'## This is the range you will loop over
Set loopRange = ActiveSheet.Range("D2:D6") '## Modify as needed
'## This is the list of names built as an array
nmArr = Array("Christy", "Kari", "Sue", "Clayton")
DELETE_EJ
For Each cl in loopRange.Cells
For i = LBound(nmArr) to Ubound(nmArr)
If Instr(1, cl.Value, nmArr(i), vbTextCompare) Then
cl.Offset(0,1).Value = nmArr(i)
Exit For
End If
Next
Next
End Sub
以上需要硬编码的范围内,但如果你只是需要做,直到它找到一个空白单元格, Ť母鸡修改像这样:
Option Explicit
Sub foo() 'Main Program
Dim nmArr()
Dim i As Long
Dim cl As Range
Set cl = ActiveSheet.Range("D2") '## This is the STARTING cell
'## This is the list of names built as an array
nmArr = Array("Christy", "Kari", "Sue", "Clayton")
DELETE_EJ
Do
For i = LBound(nmArr) To UBound(nmArr)
If InStr(1, cl.Value, nmArr(i), vbTextCompare) Then
cl.Offset(0, 1).Value = nmArr(i)
Exit For
End If
Next
'## Get a handle on the NEXT cell
Set cl = cl.Offset(1, 0)
Loop Until Trim(cl.Text) = vbNullString
End Sub
第二条本办法经测试&工作产生类似的输出:
我得到一个编译错误结束如果没有块如果? –
对不起,第二个'End If' *应该是'Next'。我将修改:) –
现在运行时错误'91'对象变量或块变量未设置?我在哪里可以读到类似这样的内容?我真的很感谢你的帮助@davidzemens –
是的,你可以把名字中的数组,然后遍历数组:
Sub james() 'Main Program
Dim celltxt As String
Dim nmArr()
nmArr = Array("Christy", "Kari", "Sue", "Clayton")
celltxt = ActiveSheet.Range("D2").Value
DELETE_EJ
For i = LBound(nmArr) To UBound(nmArr)
If InStr(1, celltxt, nmArr(i), vbTextCompare) Then
Range("E2").Value = nmArr(i)
Exit For
End If
Next i
End Sub
哇,这比单独列出所有名称的代码更好。我怎样才能让D2和E2循环来运行下面的行? D3信息到E3? –
@JSt见戴维的回答。我错过了第二回合。 –
谢谢@scottcramer! –
'有一个时间looping'赫克,因为你没有循环呢? – findwindow
@findwindow你今天在滚动;) – mrbungle
@mrbungle lol只是说明了明显的XD – findwindow