CODEWITHSUNDEEP
vbaexcel
Chinwobble
Chinwobble

Reputation: 652

Range.find not working with excel VBA

I am having trouble with range.find method in excel VBA.

I have two sheets. Sheet A has a format as follows (months are irregular intervals):

Dec-1997
Dec-1999
Dec-2000
Dec-2001
Dec-2002
Dec-2003
Dec-2004
Dec-2005
Dec-2006
Mar-2007
Dec-2007
Dec-2008
Dec-2009
Dec-2010
Dec-2011(a)
Dec-2013
Dec-2014

Sheet B has a column A where dates are laid out in regular quarterly intervals like below.

Jun-2011
Sep-2011
Dec-2011
Mar-2012
Jun-2012
Sep-2012
Dec-2012
Mar-2013
Jun-2013
Sep-2013
Dec-2013
Mar-2014
Jun-2014
Sep-2014
Dec-2014

I want my VBA code to iterate through each item in sheet A and find the associated row number in sheet B.

Here's the snippet from my code:

Sub sSearch(arr As Variant, j As Integer, rngSearch As Range, ws As Worksheet)
    Dim wsWrite As Worksheet
    Set wsWrite = ThisWorkbook.Sheets(1) ' Sheet B
    Dim rngResult As Range
    Dim intRows As Integer
    Dim rngRow As Range
    Dim strDate As String

    For i = 0 To UBound(arr)
        Set rngResult = rngSearch.Find(arr(i), LookIn:=xlValues)
        intRows = rngResult.End(xlDown).Row - rngResult.Row ' determine number of rows in Sheet A
        For k = 1 To intRows '
            strDate = Left(rngResult.Offset(k).Text, 9)
            set rngRow = wsWrite.Range("A:A").Find(What:=strDate, LookIn:=xlValues)
            wsWrite.Cells(intRow, i + j).Value = arr(i)
            wsWrite.Cells(intRow, i + j).Value = _
                ws.Cells( _
                rngResult.End(xlDown).Row, _
                rngResult.End(xlToRight).Column).Value
        Next
    Next

End Sub

I get an error on the following line

set rngRow = wsWrite.Range("A:A").Find(What:=strDate, LookIn:=xlValues)

I tried everything I could think of including looking through microsoft documentation, searching on here but no luck. rngRow returns nothing even though I can see that the value stored in strDate is a value in cell A2 of that sheet. Any help would be appreciated. Thanks!

Upvotes: 3

Views: 6249

Answers (2)

Cornel
Cornel

Reputation: 131

You need to convert array(i) value to date -> CDate(arr(i)). New Find line looks like this:

Set rngRow = wsWrite.Range("A:B").Find(What:=CDate(arr(i)), LookIn:=xlFormulas)

Without CDate(arr(i)) you can't find the data, you can manually test in excel, only with a search like "Dec-00" excel finds something

Check if date or string:

If IsDate(Arr(i)) Then
value_to_lookup = CDate(Arr(i))
Else
value_to_lookup = Arr(i)
End If
Set rngRow = Range("A:b").Find(What:=value_to_lookup, LookIn:=xlFormulas)

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

Wouldn't something like this be faster:

Function getRow(str As String, rng As Range) As Long
str = Left (str, 10)
getRow = Application.Match(str, rng, 0)
End Function

This will give the row as long which can be used to set a range...

Or to get the cell as ref:

Function getCell(str As String, rng As Range) As Range
str = Left (str, 10)
Set getCell = rng(1).Offset(Application.Match(str, rng, 0) - 1)
End Function

Asuming you use 1 column to look at (for both cases)

Upvotes: 3

Related Questions