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.
[VBA]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 [/VBA]
Reply With Quote
