Excel VBA中查找单元格地址的方法

x4shl7ld  于 2023-04-22  发布在  其他
关注(0)|答案(3)|浏览(627)

如何使用查找功能获取单元格地址。
这是密码

Dim Found As Range

Set Found = Worksheets("Sheet 1").Cells.Find(What:="test", LookAt:=xlWhole, MatchCase:=True)

If Not Found Is Nothing Then
    ' do something
End If

当我调试代码时,“Found”变量包含一个“字符串”而不是单元格地址。

roqulrg3

roqulrg31#

似乎你可以只使用found.address,即使它显示为字符串。下面的代码为我工作。

Sub findCellAddress()

    Dim ra As Range

    Set ra = Cells.Find(What:="fff", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    If ra Is Nothing Then
        MsgBox ("Not found")
        Else
        MsgBox (ra.Address)
    End If

End Sub
ws51t4hk

ws51t4hk2#

我在互联网上找不到这个。这个代码会给予你行和列。

Dim ThisPos As Range
With Range("A1:J100")
    Set ThisPos = .Find(What:="List_Position", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not ThisPos Is Nothing Then
        Cell_Add = Split(ThisPos.Address, "$")
        ThisCol = Cell_Add(1)
        ThisRow = Cell_Add(2)
    End If
End With
v09wglhw

v09wglhw3#

这个代码会给予你单元格地址的引用样式。

Dim SValue As Range
   With Range("D1:D100")

    Set SValue = .Find(What:="Searched Value", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    
    If Not SValue Is Nothing Then
        Cell_Split_R = Split(SValue.Address(ReferenceStyle:=xlR1C1), "R")
        Cell_Split_C = Split(Cell_Split_R(1), "C")
        SCol = Cell_Split_C(0)
        SRow = Cell_Split_C(1)
        
    End If
End With

相关问题