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

Question

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)

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...? Exactly WHERE are you looking for the string and the date? — user4039065
– user4039065, Commented Apr 9, 2017 at 14:40
The 'myMetric' is in column B, the 'myDate' is in row 3. The values are declared on cells B20 & B21 — Chis.Wisselo
– Chis.Wisselo, Commented Apr 9, 2017 at 14:43
No, that is not what I meant. Where are you expecting the find to return the values from? — user4039065
– user4039065, Commented Apr 9, 2017 at 14:43
Ok. What I have in mind is this: Search column B to find the metric. Fetch the row number. Then search row 3 for the correct date, fetch the column number. Then I those to values to create an .address, where I can post a value in. — Chis.Wisselo
– Chis.Wisselo, Commented Apr 9, 2017 at 14:45

score 1 · Accepted Answer · 2017-04-09 14:55:22Z

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

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

edited Apr 9, 2017 at 14:55

answered Apr 9, 2017 at 14:35

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

Chis.Wisselo Over a year ago

Jeeped, this worked. I tried several different myMetrics and myDates and it found the intersecting cell address beaturifully!

Chis.Wisselo Over a year ago

Can you explain the wsn and value2? So I can learn for future reference?

user4039065 Over a year ago

wsn is the name of the worksheet. Value2 is the raw underlying number, not complicated with a date's regional information. Today's .Value2 is 42,834 which is the number of days since Dec 31, 1899.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related