VBA Find Using a Variable Issue

[skigreg]

Hello, I have been working on this Function for awhile and can't seem to get it to work, nor have I been able to find any posts that have helped solve my particular issue.


What I am trying to accomplish is to be able to find dates that could be in any of the columns A:G from Sheet4, using the Date value that is one cell to the left of my active cell in Sheet9.


The below code is what I have so far that worked when I had static values like (1/1/16) in my search, but does not work now that I am trying to use a variable. On the 2nd line of my with statement I just had .Select when I had static search terms and it worked, but once I added a variable I got the error "Object variable or With Block not Set", so I added ActiveCell in front of it, but that now just makes it pick whatever cell my cursor happens to be in. Any help on this would be greatly appreciated.

    Sub test()


    Dim rFoundCell As Range
    Dim vDate As Variant
    Dim Expense As Worksheet
    Dim Data As Worksheet


    Set Expense = Sheet9
    Set Data = Sheet4
    Set rFoundCell = Range("A1")
    Set vDate = ActiveCell.Offset(0, -1)




            Set rFoundCell = Data.Range("A:G").find(What:=vDate, After:=rFoundCell, _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)


             Sheet4.Activate
            
             With rFoundCell
                ActiveCell.Select
                ActiveCell.Offset(1, 0).Copy
                Sheets("Expense Data").Select
                Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
                ActiveCell.Offset(1, 0).Select
             End With
/Code

 

[yky]

Try:

If Not rFoundCell Is Nothing Then
rFoundCell.Activate
With rFoundCell
........
........
........

Else
'put your warning message here
End If

 

[skigreg]

[Thanks for the response. That did not work, other than to let me know that it can't find my date. Even though I have both dates formatted as dates it is still not finding the date.]Try:

If Not rFoundCell Is Nothing Then
rFoundCell.Activate
With rFoundCell
........
........
........

Else
'put your warning message here
End If

[/QUOTE]

 

[dmt32]

Hi,
not tested but see if this update to your code help:

Sub test()
    
    Dim rFoundCell As Range
    Dim vDate As Variant
    Dim Data As Worksheet
    
    Set Data = Sheet4
    
    Sheet9.Activate
    vDate = ActiveCell.Offset(0, -1).Value
    
    
    If Not IsDate(vDate) Then Exit Sub
    
    On Error Resume Next
    Set rFoundCell = Data.Range("A:G").Find(What:=DateValue(vDate), After:=Data.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
                                            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    On Error GoTo exitsub
    
    If Not rFoundCell Is Nothing Then
    
        rFoundCell.Offset(1, 0).Copy
        
        Sheets("Expense Data").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
        
        Application.CutCopyMode = False
        
    Else
        
        MsgBox vDate & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If
exitsub:
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Idea is just a suggestion & should be adjusted as required.

Dave

 

[skigreg]

I am unable to get this code to work as well. The issue with this one, similar to the suggestion by yky, is that the vDate variable works, the find executes, but when it gets to the part where I want it to copy and paste the data to my other sheet, it skips over this and goes to the Else MsgBox of "Not Found".

 

[dmt32]

try changing:

LookIn:=xlValues

to this:

LookIn:=xlFormulas

If this does not help then post copy of your workbook to a dropbox if possible.

Dave

 

[yky]

I am unable to get this code to work as well. The issue with this one, similar to the suggestion by yky, is that the vDate variable works, the find executes, but when it gets to the part where I want it to copy and paste the data to my other sheet, it skips over this and goes to the Else MsgBox of "Not Found".

Press F8 to go through the code line by line and see in the watch window what the variables are when the If statement is executed.

 

[skigreg]

This change fixed the statement and now works, thanks for your help.