Vlookup loop vba

**mzang7** · 07-01-2012, 03:11 PM

Hello,

I'm relatively new to using vba. But, basically what I have now is a list of dates in column A on sheet 1. In every seventh column there is another list of dates (i.e. G, M,... etc.). I would like to write something in vba that will pull rates from column B in sheet 2 (for which there are dates in column A), and enter them in sheet 1 two columns to the right of the associated dates (C, I, O,... etc). I know this might sound a bit unclear so please ask me any questions that might clear it up. Thank you.

**zbor** · 07-01-2012, 03:13 PM

Can you upload example workbook with your inputs and desired output?

**mzang7** · 07-01-2012, 03:43 PM

Here's an example of what the file looks like. Unfortunately I don't have the actual file on this computer which will have significantly more data

**zbor** · 07-01-2012, 04:33 PM

Here, try this:

Sub transfer_date()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Integer
Dim j As Integer
Dim LastCol As Integer
Dim LastRow As Integer


Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

LastCol = ws1.Range("IV1").End(xlToLeft).Column

For i = 0 To LastCol
    If ws1.Range("A1").Offset(0, i).Value = "DATE" Then
            LastRow = ws1.Range("A1").Offset(65535, i).End(xlUp).Row
            For j = 1 To LastRow
                ws1.Range("A1").Offset(j, i + 2).Value = Application.SumIf(ws2.Range("A1:A65535"), ws1.Range("A1").Offset(j, i).Value, ws2.Range("B1:B65536"))
            Next j
    End If
Next i

End Sub

**mzang7** · 07-12-2012, 03:13 PM

Sorry for taking so long to get back but is there a way I could alter this to work for dates that don't match up exactly? I'd like it to still pull the data if the dates are witihin three days of each other ideally