If Vlookup

mezzy01

Board Regular
Joined
Nov 7, 2005
Messages
54
Hi

I've got a bit of a problem. Being new to VBA and the board, I'd appreciate some guidance

I've got 2 spreadsheets.

1, with a date and a row of data
2, a list of consective dates in a column

When todays date is input to sheet 1, I need to look up the date in sheet 2 and copy and paste a range to it from sheet 1 however, for Friday I need to paste it to 3 rows!!!!

dazed & confused

Would appreciate some guidance on how to achieve this
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
HI

Bit more detail please. Which 3 rows??? What data goes into which row? Can you give an example with sample data, what you would expect to see where, etc.


Tony
 
Upvote 0
If vlookup

I've got a date in Sheet1 Cell A1. I need to take this date and look for it in a range of consecutive dates in Sheet 2 Column A. When it's found i need to copy a range from Sheet 1 to the cells to the right of the date found in Sheet 2. If the Sheet a1 date is a Friday, i need to copy the to the following 2 rows

I tried several times but I'm having no joy

pls help
 
Upvote 0
Problem

Can anyone help with this??

Do you need more info???

Would appreaciate some direction
 
Upvote 0
Vlookup Date

Here's some sample data

In Sheet1 you have a date in A1

25/11/05

In Sheet 1 Cells B1:D1 you have

1 2 3

in 3 seperate cells.

In Sheet 2 you have a list of dates in Column A e.g

23/11/05
24/11/05
25/11/05
26/11/05
27/11/05

What I want to do is look for the date input to Sheet1 A1 i.e

25/11/05

in column A in Sheet 2

Once it is found, I need to copy the cells B1:D1 from Sheet1 i.e

1 2 3

and paste it to Sheet2 to the right of the date found i.e


23/11/05
24/11/05
25/11/05 1 2 3
26/11/05
27/11/05

If the date is a Friday like 25/11/05 is I need to copy it to the following 2 days e.g

23/11/05
24/11/05
25/11/05 1 2 3
26/11/05 1 2 3
27/11/05 1 2 3

I only need to copy to a single row if the Date is Monday to Thursday. Saturday & Sunday will require no copying

Hope there is enuff info here

thanks
 
Upvote 0
Something like this? I'm still a bit foggy on what you need sorry :rolleyes:

Sheet 1
Book2
ABCD
111/25/2005123
2
3
4
Sheet1


Sheet 2
Book2
ABCD
111/23/20050
211/24/20050
311/25/20051 2 3
411/26/20050
511/27/20050
Sheet2
 
Upvote 0
Vlook up

You're on the right track however on Sheet2, 1 needs to be pasted into B3, 2 into C3 and 3 into D3. I wanted to do this in VBA because if Sheet1 A1 = Friday, I need to copy and paste the 1, 2 & 3 to the 26 & 27 e.g

25/11/05 1 2 3
26/11/05 1 2 3
27/11/05 1 2 3

For Monday to Thursday I copy a single row. For Saturday & Sunday there is no copying e.g if Sheet1 A1 = 24/11/05 result would be as follows

24/11/05 1 2 3
25/11/05
26/11/05

thanks for your help
 
Upvote 0
Hi

Try
Code:
Sub aaa()
 Set outplace = Sheets("sheet2").Range("a:a").Find(what:=Sheets("sheet1").Range("a1"))
 If Not outplace Is Nothing Then
  Sheets("sheet1").Range("b1:d1").Copy Destination:=outplace.Offset(0, 1)
  If Weekday(outplace) = 6 Then
   Sheets("sheet1").Range("b1:d1").Copy Destination:=outplace.Offset(1, 1)
   Sheets("sheet1").Range("b1:d1").Copy Destination:=outplace.Offset(2, 1)
  End If
 End If
End Sub


Tony
 
Upvote 0
Vlookup date

Magic Tony

Certainly fits the bill. I'll certainly be studying that bit of code

thanxs
 
Upvote 0

Forum statistics

Threads
1,207,109
Messages
6,076,596
Members
446,215
Latest member
userds5593

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top