[已解决][求助]Excel查找工式

dengaiquan

以下为查找对象是下一行单元格,如果要找下一列单元格要怎么样改公式??

Sub chazhao()
On Error Resume Next
Dim i, c, cc, r, rr, arr As Integer
Sheet2.Range("b:b").ClearContents

cc = Sheet1.UsedRange.Columns.Count
rr = Sheet1.UsedRange.Rows.Count

For c = 1 To cc
For i = 1 To rr
arr = InStr(Sheet1.Cells(i, c), Sheet2.Range("a1"))
If arr > 0 Then Sheet2.Range("b" & r) = Sheet1.Cells(i + 1, c)
r = Sheet2.[b65535].End(xlUp).Row + 1
Next i
Next c
End Sub

有请高手指教,QQ709516650在线等

[此贴子已经被作者于2008-12-15 1:48:31编辑过]

最佳答案

月排行榜 / 总排行榜

hingkk

2008-12-15 02:04

Sub chazhao()
On Error Resume Next
Dim i, c, cc, r, rr, arr As Integer
Sheet2.Range("b:b").ClearContents

cc = Sheet1.UsedRange.Columns.Count
rr = Sheet1.UsedRange.Rows.Count

For c = 1 To cc
For i = 1 To rr
arr = InStr(Sheet1.Cells(i, c), Sheet2.Range("a1"))
If arr > 0 Then Sheet2.Range("b" & r) = Sheet1.Cells(i , c+1)
r = Sheet2.[b65535].End(xlUp).Row + 1
Next i
Next c
End Sub

跳转到最佳答案楼层

hingkk

Sub chazhao()
On Error Resume Next
Dim i, c, cc, r, rr, arr As Integer
Sheet2.Range("b:b").ClearContents

cc = Sheet1.UsedRange.Columns.Count
rr = Sheet1.UsedRange.Rows.Count

For c = 1 To cc
For i = 1 To rr
arr = InStr(Sheet1.Cells(i, c), Sheet2.Range("a1"))
If arr > 0 Then Sheet2.Range("b" & r) = Sheet1.Cells(i , c+1)
r = Sheet2.[b65535].End(xlUp).Row + 1
Next i
Next c
End Sub

zxy999

学习

rbftong

查找可以试下用 find 方法

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Pattern = xlPatternGray50
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

lily0327

路过