使用。find找到要粘贴数据的行和列

I am trying to find the a row and column number to paste data in that cell. The rows are the various metrics to look for, the columns the dates. I prefer to use a function for it, so I can simply call the function an pass the different parameter.

我试图找到行号和列号，以便在该单元格中粘贴数据。行是要查找的各种指标，列是日期。我更喜欢使用函数，所以我可以简单地调用函数an传递不同的参数。

It is the reverse from this thread: How to find cell value based on row and Column ID in excel VBA. I want to find the address of a cell.

这与此线程相反:如何在excel VBA中基于行和列ID查找单元格值。我想找到一个单元格的地址。

The code I have so far:

到目前为止，我的代码是:

Sub FindMyCell()
    Dim myDate As String
    Dim myMetric As String
    Dim foundRange As Range
    Dim pasteRange As Range
    Dim r As Range, c As Range

    Worksheets("Sheet1").Range("B20").Select

    myDate = Worksheets("Sheet1").Range("B20").Value
    myMetric = Worksheets("Sheet1").Range("B21").Value
    FindCell(myMetric,myDate)
    Inputcell = foundRange.Address


End Sub

Function FindCell(myMetric As String, myDate As String) As String

    With ActiveCell
        r = .Columns("B").Find(myMetric).row
        c = .Rows("3").Find(myDate).Column

        If r = Nothing Or c = Nothing Then
            'errorcount = errorcount + 1
            'Exit Function
        End If

        Set pasteRange = .Cells(r, c).Address
    End With

End Function

I keep getting: Compile error: Argument not optional in the line:

我不断地得到:编译错误:参数不是可选的行:

Set foundRange = FindCell(myDate & myMetric)

1 个解决方案

#1

You are concatenating the two parameters. Use a comma to separate them.

您正在连接这两个参数。用逗号把它们分开。

Set foundRange = FindCell(myDate, myMetric)
'the line also has a typo
Inputcell = foundRange .Address

You are aware that with B20 selected, ActiverCell.Columns("B") is actually column C on the worksheet and ActiverCell.Rows(3) is actually row 22 on the worksheet...?

您知道，选择了B20后，ActiverCell.Columns(“B”)实际上是工作表上的C列，而activercell.3实际上是工作表上的22行…?

Functions should never use ActiveCell and you are offsetting the search ranges. Dates can be tricky in .Find. Try this alternative.

函数不应该使用ActiveCell，而您正在抵消搜索范围。日期在。find中可能很棘手。试试这个选择。

Option Explicit

Sub FindMyCell()
    Dim myDate As Long
    Dim myMetric As String, inputCell As String

    With Worksheets("Sheet1")
        myDate = .Range("B20").Value2
        myMetric = .Range("B21").Value2

        inputCell = FindCell(.Name, myMetric, myDate)
        Debug.Print inputCell
    End With

End Sub

Function FindCell(wsn As String, myMetric As String, myDate As Long) As String
    Dim r As Variant, c As Variant

    With Worksheets(wsn)
        r = Application.Match(myMetric, .Columns("B"), 0)
        c = Application.Match(myDate, .Rows(3), 0)

        If IsError(r) Or IsError(c) Then
            'errorcount = errorcount + 1
            'Exit Function
        End If

        FindCell = .Cells(r, c).Address
    End With

End Function

#1