Macro to Find Date in Range, Select Column with Date Match & Paste Data in Column As Values

[Production Planner]

Hi Everyone,

I am having some issues when running the macro I have put in place to look up a date within a range and copy / paste the data in the column as a value once found (this is due to the data being looked up via a formula - this is to be done on 3 different worksheets.

For some reason it is not finding the date.

The Date value is on Worksheet "Weekly Report Input Values" Cell K2

The Date to find is located withing cells "e2:gg2" on the other 3 sheets (FV04 , FV05 & AWAF Data Input Sheets)

The Data to copy and paste as values below the found date on the 3 sheets above is located in rows 5 through 51

I'm sure there are other errors to the code below but finding a date match is the first step.

Here is the code below.

 Dim rFndCell As Range
    Dim strData As String
    Dim stFnd As String
    Dim fCol As Integer
    Dim sh As Worksheet
    Dim ws As Worksheet
    
    Dim rFndCell1 As Range
    Dim strData1 As String
    Dim stFnd1 As String
    Dim fCol1 As Integer
    Dim sh1 As Worksheet
    Dim ws1 As Worksheet
    
    Dim rFndCell2 As Range
    Dim strData2 As String
    Dim stFnd2 As String
    Dim fCol2 As Integer
    Dim sh2 As Worksheet
    Dim ws2 As Worksheet
     
    Set ws = Sheets("Weekly Report Input Values")
    Set sh = Sheets("FV 04 Data Input Sheet")
    stFnd = ws.Range("K2").Value
     
    With sh
        Set rFndCell = sh.Range("e2:gg2").Find(stFnd, LookIn:=xlValues)
        If Not rFndCell Is Nothing Then
            fCol = rFndCell.Column
            sh.Range("5:51").Copy
            sh.Cells(5, fCol).PasteSpecial xlPasteValues
        Else 'Can't find the item
            MsgBox "Date Not Found"
        End If
    End With
Set ws1 = Sheets("Weekly Report Input Values")
    Set sh1 = Sheets("FV 05 Data Input Sheet")
    stFnd1 = ws1.Range("K2").Value
     
    With sh1
        Set rFndCell1 = sh1.Range("e2:gg2").Find(stFnd1, LookIn:=xlValues)
        If Not rFndCell1 Is Nothing Then
            fCol1 = rFndCell1.Column
            sh1.Range("5:51").Copy
            sh1.Cells(5, fCol1).PasteSpecial xlPasteValues
        Else 'Can't find the item
            MsgBox "Date Not Found"
        End If
    End With
    
    Set ws2 = Sheets("Weekly Report Input Values")
    Set sh2 = Sheets("AWAF Data Input Sheet")
    stFnd2 = ws2.Range("K2").Value
     
    With sh2
        Set rFndCell2 = sh2.Range("e2:gg2").Find(stFnd2, LookIn:=xlValues)
        If Not rFndCell2 Is Nothing Then
            fCol2 = rFndCell2.Column
            sh2.Range("5:51").Copy
            sh2.Cells(5, fCol2).PasteSpecial xlPasteValues
        Else 'Can't find the item
            MsgBox "Date Not Found"
        End If
    End With

There is further code beyond this that creates a hard value copy of the workbook for distribution.


Any help on getting this working would be appreciated.

Cheers Guys.

 

[JoeMo]

Are the dates in sh2 numeric or text?

 

[Production Planner]

Are the dates in sh2 numeric or text?

Hi Joe,

They are entered as for example:

03/08/2013

The cell is currently formatted to d-mmm-yyyy

 

[JoeMo]

Hi Joe,

They are entered as for example:

03/08/2013

The cell is currently formatted to d-mmm-yyyy

In any empty cell enter: =ISNUMBER(Cell with Date)
by entering =ISNUMBER( and then clicking on one of the cells containing a "date". Does this return TRUE or FALSE?
Do the same for the source cell (K2) in your input sheet - TRUE or FALSE?

 

[Production Planner]

In any empty cell enter: =ISNUMBER(Cell with Date)
by entering =ISNUMBER( and then clicking on one of the cells containing a "date". Does this return TRUE or FALSE?
Do the same for the source cell (K2) in your input sheet - TRUE or FALSE?

It has returned TRUE in both instances

 

[JoeMo]

It has returned TRUE in both instances

Have you verified that the date in K2 is actually in the range E2:GG2 when the find fails? Have you stepped through the code using the F8 key to see if rFndCell is Nothing? Do you get the msgbox "Date Not Found" when you run the code? If yes, then try copying the value in K2 and pasting it to a cell in the range E2:GG2 on the sheet you are searching and run the code again.

 

[Production Planner]

Have you verified that the date in K2 is actually in the range E2:GG2 when the find fails? Have you stepped through the code using the F8 key to see if rFndCell is Nothing? Do you get the msgbox "Date Not Found" when you run the code? If yes, then try copying the value in K2 and pasting it to a cell in the range E2:GG2 on the sheet you are searching and run the code again.

The date in K2 is definately in the range E2:GG2


I have used the F8 key to run through the code and have gotten the msg box stating that the "Date Not Found"

I just tried copy and pasting the K2 Cell into the range E2:GG2 as suggested, and ran the code using F8 and have the same come up with the same Date Not Found result.

 

[JoeMo]

The date in K2 is definately in the range E2:GG2


I have used the F8 key to run through the code and have gotten the msg box stating that the "Date Not Found"

I just tried copy and pasting the K2 Cell into the range E2:GG2 as suggested, and ran the code using F8 and have the same come up with the same Date Not Found result.

Have you tried changing lookin:= xlvalues to lookin:= xlformulas?