如何转到单元格以更改值

Question

我做了一些'更改'按钮，需要转到在Vlookup中找到的单元格，如下所示。

我只能找到进入给定范围的代码。

Private Sub CommandButton6_Click()
Dim status As Variant
status = WorksheetFunction.VLookup( _
        (Sheets("Gegevens").Range("B3")), _
         Sheets("bestand totaal").Range("J2:W9996"), 14, False)
End Sub

Answer 1

也许你在追求这个

Private Sub CommandButton6_Click()
    Dim status As Variant
    status = Application.Match(Sheets("Gegevens").Range("B3"), _
                               Sheets("bestand totaal").Range("J2:J9996"), False) '<--| try and get the index of 'Sheets("Gegevens").Range("B3")' value in 'Sheets("bestand totaal").Range("J2:J9996")' range
    If Not IsError(status) Then '<--| if value successfully found
        With Sheets("bestand totaal") '<--| reference "target" sheet
            .Activate '<--| activate it
            .Range("W2:W9996").Cells(status, 1).Select <--| select corresponding value in column "W"
        End With
    End If
End Sub

Answer 2

找到status后，通过VLookup，您可以使用Find函数搜索找到该值的VLookup范围。

我添加了 2 个Range变量，Rng 个用于VLookup范围，RngFind 个用于Find。

法典

Private Sub CommandButton6_Click()
Dim status As Variant
Dim Rng As Range
Dim RngFind As Range
Set Rng = Sheets("bestand totaal").Range("J2:W9996")
status = WorksheetFunction.VLookup((Sheets("Gegevens").Range("B3").Value), Rng, 14, False)
Set RngFind = Rng.Find(What:=status, LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
RngFind.Parent.Activate ' activate the worksheet, in case the VLookup Range is not the active sheet
RngFind.Select ' select the range (cell) where Find was set to true
End Sub