Question

excel vba中find找数据，为什么只找一次啊

程序段如下：
Set findCell = Sheet1.Columns(coldw).Find(str2)
If Not findCell Is Nothing Then

firstAddress = findCell.Address

Do
dayInRow = findCell.Row
For i = 1 To day
mydate = DateSerial(year, month, i)
Sheet15.Cells(i + 1, 1) = mydate
j = 1
For lst = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lst) = True Then
str1 = ListBox1.List(lst)
Name1 = Left(str1, InStr(str1, "&") - 1)
col = Right(str1, Len(str1) - Len(Name1) - 1)
If CDate(Sheet1.Cells(dayInRow, col)) < mydate Then
Sheet15.Cells(i + 1, j + 1) = Sheet15.Cells(i + 1, j + 1) + 1
End If
j = j + 1
End If

Next
Next
Loop While Not findCell Is Nothing And firstAddress <> findCell.Address
'Loop Until findCell.Address = firstAddress
End If
运行的时候只走一次就退出来了，求哪位高手帮忙解决一下

Answer 1

是你的逻辑设计的原因，你的find()方法在循环体之外。你看一下你的结构:
Set findCell = Sheet1.Columns(coldw).Find(str2)
if then
do
for
for
.........
next
next
loop
endif
把它设置到循环体之内去就不会只找一次了。

Answer 2

你是想在某列查找str2吧，必须加上Findnext属性，即查找下一个：
举例：
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
此例在a1:a500查找数值为2的单元格，并将单元格的值重新赋值为5

追问

Columns对象没有FindNext方法啊

追答

我看不明白你的代码是做什么的，不如你把问题说清楚，让人家帮你写段代码，会更容易些。

追问

我只能用Range对象，而不能用Columns对象吗？我那个列对象是可变的啊！！555555

追答

用columns也是可以的，你研究一下下面例子的代码：
Sub aa()
str2 = "西瓜"
Set Rng = Sheets("sheet2").Columns(1)
Set findcell = Rng.Find(str2)
If Not findcell Is Nothing Then
firstaddress = findcell.Address
Do
Set findcell = Rng.FindNext(findcell)
MsgBox findcell.Address & ": " & findcell
Loop While Not findcell Is Nothing And findcell.Address firstaddress
End If
End Sub

Answer 3

Columns和Ranges都可以用findnext
Set findCell = Sheet1.Columns(coldw).Find(str2)
If Not findCell Is Nothing Then

firstAddress = findCell.Address

Do
dayInRow = findCell.Row
For i = 1 To day
mydate = DateSerial(year, month, i)
Sheet15.Cells(i + 1, 1) = mydate
j = 1
For lst = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lst) = True Then
str1 = ListBox1.List(lst)
Name1 = Left(str1, InStr(str1, "&") - 1)
col = Right(str1, Len(str1) - Len(Name1) - 1)
If CDate(Sheet1.Cells(dayInRow, col)) < mydate Then
Sheet15.Cells(i + 1, j + 1) = Sheet15.Cells(i + 1, j + 1) + 1
End If
j = j + 1
End If

Next
Next
set findCell = Sheet1.Columns(coldw).FindNext(findCell)
Loop While Not findCell Is Nothing And firstAddress <> findCell.Address
End If