How to search for matching dates in two different sheets and then copy corresponding data from source sheet to destination sheet

Question

I would like to search a reporting spreadsheet(A) for a date and then search for that date in a data source spreadsheet(B). Once found, I would like to copy and paste data (from the same row as the date) into the corresponding date in the reporting spreadsheet(A).

Some conditions:

Once used, data from the data source spreadsheet (B) cannot be reused and therefore I want to search for the next row with the same date...
this is the reason I am using a macro (otherwise I would just use a vlookup)

I am new to VBA so I have been trying to use some loops in combination but I am unsuccessful

I want to know if there is a simpler way to do it?

Sub DataToRegister()


Dim Row As Double 'row is the row variable for the destination spreadsheet
Dim i As Double
Dim x As Integer 'x is the row variable for the source spreadsheet


For Row = 1 To 825

    i = Sheets("Register Data Fields").Cells(Row, 1)

        While i <> DateSerial(1900, 1, 0)
        'DateSerial(1900, 1, 0) --> this is the default for no data in the field, i.e. i want to skip these

            For x = 1 To 825

                If Sheets("HANSON DATA").Cells(x, 2) = Sheets("Register Data Fields").Cells(Row, 1) Then
                Sheets("HANSON DATA").Select
                Cells(x, 1).Select
                Selection.Copy
                Sheets("Register Data Fields").Select
                Cells(Row, 22).Select
                ActiveSheet.Paste
                Application.CutCopyMode = False

                Next

            Next

        Wend

End If


End Sub

Errors that I am getting include:

Next without for x2
wend without while
i mismatch variable type

Indenting your code properly will greatly help for things like "next wihtout for" errors. Also, while this is not your problem, it is always a good idea to [avoid using Activate and Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in yout code — cybernetic.nomad, May 23 '19 at 23:03
For starters your `end if` needs to be before `next next`... I will edit your code so you can see the problems... — alowflyingpig, May 23 '19 at 23:09
Once the edit has been approved, review how the format affects your code and how easy it is to spot your problems. — alowflyingpig, May 23 '19 at 23:17
OK, thanks for the tips - the error is now: "run-time error '13': type mismatch" with respect to variable 'i' @cybernetic.nomad . Is this because I am refering to cell values incorrectly ( or should the variable type be date?) — Tom Foley, May 23 '19 at 23:44
You've defind `i` as `double`.. ie a number...I assume this should be `date`? — alowflyingpig, May 23 '19 at 23:56
yeah i defined 'i' as double but the error still occurs when I define it as date @alowflyingpig — Tom Foley, May 23 '19 at 23:58
I just tested and it works... I assume the date linkd to `.Cells(Row, 1)` isnt a true at? — alowflyingpig, May 24 '19 at 00:03
open a new work book and type todays date in A1. Then run the following code Sub test() Dim i As Date i = Cells(1, 1) If i <> DateSerial(1900, 1, 0) Then MsgBox "It works" Else MsgBox "Fail" End If End Sub — alowflyingpig, May 24 '19 at 00:04
msgbox should appear = it works"... now link `i` to your file where `.Cells(Row, 1)`.. what msg box do you get? if its a true ate it will work, else it will fail... — alowflyingpig, May 24 '19 at 00:06
Yeah that works, @alowflyingpig the macro ran but kept on repeating itself so excel crashes. Not sure where to go from here... — Tom Foley, May 24 '19 at 03:49
LOL, yea it was the `while` loop, it wasn't exitting. updated answer.. if this works can you please make sure you give it a tick. — alowflyingpig, May 24 '19 at 05:06
@alowflyingpig the only issue is: it finds matches but if there is more than one entry for the same date, (e.g. the same date repeated 6 times in each spreadsheet), the 1st entry will be replaced by the second which will both be replaced by the third and so on. i.e. by the end there will be the same data entered for all 6 date repetitions. — Tom Foley, May 29 '19 at 05:07
Yes that is because your code is telling it to do that... I suggest 2 things. 1) acknowledge my answer below as correct as it fixes your query/issue you raised in your question. 2) then start a new question, with the corrected code and we can help you resolve any other queries/question/issues your code has. — alowflyingpig, May 29 '19 at 05:42

alowflyingpig · Accepted Answer · 2019-05-24T05:06:18.257

Updated code with corrected format.. seems you understand where you are going wrong...

Sub DataToRegister()


    Dim Row As Long
    Dim i As Date
    Dim x As Long


    For Row = 1 To 825

        i = Sheets("Register Data Fields").Cells(Row, 1)

        If i <> DateSerial(1900, 1, 0) Then
        'DateSerial(1900, 1, 0) --> this is the default for no data in the field, i.e. i want to skip these

            For x = 1 To 825

                If Sheets("HANSON DATA").Cells(x, 2) = Sheets("Register Data Fields").Cells(Row, 1) Then
                    Sheets("HANSON DATA").Select
                    Cells(x, 1).Select
                    Selection.Copy
                    Sheets("Register Data Fields").Select
                    Cells(Row, 22).Select
                    ActiveSheet.Paste
                    Application.CutCopyMode = False
                End If

            Next x

        End If

    Next Row


End Sub

How to search for matching dates in two different sheets and then copy corresponding data from source sheet to destination sheet

1 Answers1