Help with vlookup cell VBA

[Excelnoobisme]

Hi,

I have a worksheet name A, on cell A1 is the date.

If i open another worksheet name B, there are pre-defined date on Column A(i.e. Mar 1 to 30) where in between each date there are 10 empty row.
Hence Mar 1 on cell A1, Mar 2 on cell A11 and Mar 3 on cell A21........

How can i write a marco that if i run on worksheet A, it will see the date on cell A1 and lookup to Sheet B and select 2 rows below after the same date.
(example date on cell A1 in worksheet A is Mar 3, so it will select cell A23 on worksheet B)

any help will be greatly appreciated.

 

[VoG]

Hi. Try this

Sub test()
Dim Found As Range
Set Found = Sheets("B").Columns("A").Find(what:=Sheets("A").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Not found"
Else
    Application.Goto Found.Offset(2)
End If
End Sub

 

[Excelnoobisme]

Hi VOG, i need to copy data and paste 2 row after the date.

This is the original data in A.xlsx in Sheet(A).

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="height: 18px;"> <td style="text-align: right;">3-Mar</td> <td> </td> <td>Amount </td></tr> <tr style="height: 18px;"> <td> </td> <td> </td> <td style="text-align: right;">$50 </td></tr> <tr style="height: 18px;"> <td> </td> <td> </td> <td style="text-align: right;">$60
</td></tr> <tr style="height: 18px;"> <td> </td> <td> </td> <td style="text-align: right;">$70 </td></tr> <tr style="height: 18px;"> <td> </td> <td>
</td> <td style="text-align: right;">$80
</td></tr></tbody></table>
In B.xlsx in Sheet(B), i wish my end result to be like this

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="text-align: right;">1-Mar</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="text-align: right;">2-Mar</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td style="text-align: right;">3-Mar</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">24</td> <td style="text-align: right;">$50 </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">25</td> <td style="text-align: right;">$60 </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">26</td> <td style="text-align: right;">$70 </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">27</td> <td style="text-align: right;">$80 </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">28</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">29</td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30</td> <td> </td></tr></tbody></table>

 

[VoG]

Maybe

Sub test()
Dim Found As Range
Set Found = Sheets("B").Columns("A").Find(what:=Sheets("A").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Not found"
Else
    Sheets("A").Range("C2:C5").Copy Destination:=Found.Offset(2)
End If
End Sub

 

[Excelnoobisme]

run time error....

dont i need to

Windows("B.xlsx").Activate
or
Windows("A.xlsx").Activate

as it's in 2 difference workbook

 

[VoG]

You said sheets not books!

What are the sheets in A.xls and B.xls?

 

[Excelnoobisme]

sheet1 in A.xls and sheet2 in B.xls

sorry i didnt make myself clear

 

[VoG]

Try

Sub test()
Dim Found As Range
Set Found = Workbooks("B.xls").Sheets("Sheet2").Columns("A").Find(what:=Workbooks("A.xls").Sheets("Sheet1").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Not found"
Else
    Workbooks("A.xls").Sheets("Sheet1").Range("C2:C5").Copy Destination:=Workbooks("B.xls").Sheets("Sheet2").Range(Found.Address).Offset(2)
End If
End Sub

 

[Excelnoobisme]

Run-Time error "9"



Set Found = Workbooks("B.xls").Sheets("Sheet2").Columns("A").Find(what:=Workbooks("A.xls").Sheets("Sheet1").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)


was highlighted
</pre>

 

[VoG]

One or more of the items in red does not exist

Set Found = Workbooks("B.xls").Sheets("Sheet2").Columns("A").Find(what:=Workbooks("A.xls").Sheets("Sheet1").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)