PDA

View Full Version : Compare two date column in different sheets, return adjacent value in matched dates



abs7
02-27-2019, 10:09 AM
I have zero coding experience and am new to VBA, so I don’t even know basics, but giving it a shot. I have a Workbook, with multiple sheet in it. The one that I care about are 2 sheets called DG, and Asp. DG has a button that grabs raw data from a server and populates the sheets ( multiple date columns with data value in adjacent cells). Asp has a button that grabs data as well but on a 30-day avg so every day in a month (columns A in Asp). This is the same case with DG sheet, but DG has data from different dates in a month, because it is not a 30 day pull. So that sets an image for you, now what I want to do is create a button, with a code that can go through a date column in DG and match it with a date from asp date and if there is a match, then copy and paste the adjacent cells values in DG to asp. oh and date format for DG is yyyy-mm-dd - hh:min while Asp date format is dd-mmm-yyyy-hh:min. I am okay with changing the format in Asp to match DG if it makes code easier, and time can be ignored. i just want the data the dates match to be copied to Asp
here is what i have so far from help on other forums.
https://www.ozgrid.com/forum/filedata/fetch?filedataid=74072&type=full
https://www.ozgrid.com/forum/filedata/fetch?filedataid=74073&type=full

Private Sub CommandButton2_Click()


Sheets("Aspen").Range("A2", "A150").NumberFormat = "yyyy-mm-dd"


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lrow2 As Long
Dim lrow1 As Long
Dim firstDataRow1 As Double
Dim firstDataRow2 As Double


Dim matchRange As Range
Dim matchRow As Long


Dim i As Long


'Set up your Worksheet variables
Set ws1 = ThisWorkbook.Worksheets("DG")
Set ws2 = ThisWorkbook.Worksheets("Asp")


'You used A5 several times, so I will assume dates are in Col A and start at row 5
'Set your row with first data, maybe you need two, if they are the same on both sheets you don't
firstDataRow1 = 11
firstDataRow2 = 5
'find the last row on each sheet, using column A, the date col
lrow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
lrow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row


'In your mind you now have two data ranges, one for each sheet, rows 5-last dat rows


'Pick one sheet, the one with less dates would be more efficient, you mention DG has less
'So this might be built backwards from what you are thinking, since we are iterating each row in the col
'You want to use the shrter call, IF you know its shorter (your comments)


'Loop through each row trying to find a match on your other sheet
For i = firstDataRow1 To lrow1
For i = firstDataRow2 To lrow2

If ws1.Cells(i, "A") <> "" Then 'Test for empty


'Here is the premise of Find/Match over Vlookup
Set matchRange = ws2.Range("A" & firstDataRow & ":A" & lrow2).Find(ws1.Cells(i, "A"))


On Error Resume Next
matchRow = matchRange.Row 'Returns row number or nothing


If (Not matchRange Is Nothing) Then
'we have a row number matched on Asp, for our search item on DG
'perform the "Copy", this can be done differently but here I am going to introduce you to a way
'that can later be used with offsets and col #s, so that you may skip columns, data is not always adjacent
ws2.Cells(matchRow, "C") = ws1.Cells(i, "B")

Else 'DO NOTHING
End If
Else 'DO NOTHING
End If


Next i


MsgBox "Search and Copy is complete.", vbInformation, "Completed"




End Sub

Leith Ross
02-27-2019, 08:23 PM
Hello abs7,

Since you are new to VBA and you got help with the code you posted, why not contact the person who helped you with this code?

abs7
03-01-2019, 08:19 AM
I got help so far and got me as far as I did, but I feel im imposing too much on that person for my problem, so I'm looking to get a fresh set of eyes on this....:(