CODEWITHSUNDEEP
vbaexcelfunctionfind
Chis.Wisselo
Chis.Wisselo

Reputation: 79

Excel VBA Using .find to find row and column to paste data

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.

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.

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)

Upvotes: 1

Views: 4907

Answers (1)

user4039065
user4039065

Reputation:

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...?

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

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

Upvotes: 1

Related Questions