LOOKUP help possibly

H

Harrywatson

Board Regular
Joined
Jan 20, 2014
Messages
92
I would like to have a sheet in my document which brings up a row of information depending on if the row of data that is in say sheet one contains a date that is in between two dates such as 10/09/14 and 16/09/14 in column L.

For example I would like all rows which contain a date in between 10/09/14 and 16/09/14 in Column L on the Sheet named "ON" to then become visible in a list on this new sheet.

I feel this here is possibly an easier way of explaining rather than the first paragraph, both are quite difficult to understand.

I know its possible and you guys can do it im just struggling to explain what i want! Please feel free to ask questions, Thanks a lot.
The Rows of information are A-N wide with L containing the date...this is on the first sheet, that I would like to then appear on the second sheet depending on the value in column L (the date; is it inbetween 10/09/14 and 16/09/14)

Thanks Guys, please ask questions please help
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Code: 
sht1 = "Sheet1"
sht2 = "ON"
startDate = Format("10/09/2014", "dd/mm/yyyy")
endDate = Format("16/9/2014", "dd/mm/yyyy")
lastRow = Sheets(sht1).Range("L" & Rows.Count).End(xlup).Row
i = 2 'if you don't have headers, change i = 2 to i = 1
c = 2
Do Until i > lastRow
If Sheets(sht1).Range("L" & i).value >= startDate AND _
     Sheets(sht1).Range("L" & i).value <= endDate Then
     Sheets(sh2).Range("A" & c).value = Sheets(sht1).Range("A" & i).value
     Sheets(sh2).Range("B" & c).value = Sheets(sht1).Range("B" & i).value
     Sheets(sh2).Range("C" & c).value = Sheets(sht1).Range("C" & i).value
     Sheets(sh2).Range("D" & c).value = Sheets(sht1).Range("D" & i).value
     Sheets(sh2).Range("E" & c).value = Sheets(sht1).Range("E" & i).value
     Sheets(sh2).Range("F" & c).value = Sheets(sht1).Range("F" & i).value
     Sheets(sh2).Range("G" & c).value = Sheets(sht1).Range("G" & i).value
     Sheets(sh2).Range("H" & c).value = Sheets(sht1).Range("H" & i).value
     Sheets(sh2).Range("I" & c).value = Sheets(sht1).Range("I" & i).value
     Sheets(sh2).Range("J" & c).value = Sheets(sht1).Range("J" & i).value
     Sheets(sh2).Range("K" & c).value = Sheets(sht1).Range("K" & i).value
     Sheets(sh2).Range("L" & c).value = Sheets(sht1).Range("L" & i).value
     Sheets(sh2).Range("M" & c).value = Sheets(sht1).Range("M" & i).value
     Sheets(sh2).Range("N" & c).value = Sheets(sht1).Range("N" & i).value
     c = c + 1
End If
i = i + 1
Loop
This code does not do what you wanted. You have 2 sheets. Sheet 1 and a sheet called ON. Both sheets have the same data in them. If the dates in column L are between 2 dates, you want only those rows with those dates to be visible in your ON sheet and hide the other rows. I didn't make code to do that. The code I made inserts the rows that are between the 2 dates into the sheet named ON. The only thing I didn't put in the code that will prevent it to work properly is to clear the ON sheet of data before running the code. I didn't do this because I don't have that code memorized. What you need to do is enter clear code right after the "Sub Macro1()" and before the first line of code I wrote that says "sht1 = "Sheet 1"". Record a macro of you selecting columns A through N and press the delete button. Then end the macro recording. That is the easiest way to add clear code before pasting the code I wrote you.
 
Upvote 0
WarPiglet said:
Code: 
sht1 = "Sheet1"
sht2 = "ON"
startDate = Format("10/09/2014", "dd/mm/yyyy")
endDate = Format("16/9/2014", "dd/mm/yyyy")
lastRow = Sheets(sht1).Range("L" & Rows.Count).End(xlup).Row
i = 2 'if you don't have headers, change i = 2 to i = 1
c = 2
Do Until i > lastRow
If Sheets(sht1).Range("L" & i).value >= startDate AND _
     Sheets(sht1).Range("L" & i).value <= endDate Then
     Sheets(sh2).Range("A" & c).value = Sheets(sht1).Range("A" & i).value
     Sheets(sh2).Range("B" & c).value = Sheets(sht1).Range("B" & i).value
     Sheets(sh2).Range("C" & c).value = Sheets(sht1).Range("C" & i).value
     Sheets(sh2).Range("D" & c).value = Sheets(sht1).Range("D" & i).value
     Sheets(sh2).Range("E" & c).value = Sheets(sht1).Range("E" & i).value
     Sheets(sh2).Range("F" & c).value = Sheets(sht1).Range("F" & i).value
     Sheets(sh2).Range("G" & c).value = Sheets(sht1).Range("G" & i).value
     Sheets(sh2).Range("H" & c).value = Sheets(sht1).Range("H" & i).value
     Sheets(sh2).Range("I" & c).value = Sheets(sht1).Range("I" & i).value
     Sheets(sh2).Range("J" & c).value = Sheets(sht1).Range("J" & i).value
     Sheets(sh2).Range("K" & c).value = Sheets(sht1).Range("K" & i).value
     Sheets(sh2).Range("L" & c).value = Sheets(sht1).Range("L" & i).value
     Sheets(sh2).Range("M" & c).value = Sheets(sht1).Range("M" & i).value
     Sheets(sh2).Range("N" & c).value = Sheets(sht1).Range("N" & i).value
     c = c + 1
End If
i = i + 1
Loop
This code does not do what you wanted. You have 2 sheets. Sheet 1 and a sheet called ON. Both sheets have the same data in them. If the dates in column L are between 2 dates, you want only those rows with those dates to be visible in your ON sheet and hide the other rows. I didn't make code to do that. The code I made inserts the rows that are between the 2 dates into the sheet named ON. The only thing I didn't put in the code that will prevent it to work properly is to clear the ON sheet of data before running the code. I didn't do this because I don't have that code memorized. What you need to do is enter clear code right after the "Sub Macro1()" and before the first line of code I wrote that says "sht1 = "Sheet 1"". Record a macro of you selecting columns A through N and press the delete button. Then end the macro recording. That is the easiest way to add clear code before pasting the code I wrote you.
Click to expand...

WarPiglet said:
Code: 
sht1 = "Sheet1"
sht2 = "ON"
startDate = Format("10/09/2014", "dd/mm/yyyy")
endDate = Format("16/9/2014", "dd/mm/yyyy")
lastRow = Sheets(sht1).Range("L" & Rows.Count).End(xlup).Row
i = 2 'if you don't have headers, change i = 2 to i = 1
c = 2
Do Until i > lastRow
If Sheets(sht1).Range("L" & i).value >= startDate AND _
     Sheets(sht1).Range("L" & i).value <= endDate Then
     Sheets(sh2).Range("A" & c).value = Sheets(sht1).Range("A" & i).value
     Sheets(sh2).Range("B" & c).value = Sheets(sht1).Range("B" & i).value
     Sheets(sh2).Range("C" & c).value = Sheets(sht1).Range("C" & i).value
     Sheets(sh2).Range("D" & c).value = Sheets(sht1).Range("D" & i).value
     Sheets(sh2).Range("E" & c).value = Sheets(sht1).Range("E" & i).value
     Sheets(sh2).Range("F" & c).value = Sheets(sht1).Range("F" & i).value
     Sheets(sh2).Range("G" & c).value = Sheets(sht1).Range("G" & i).value
     Sheets(sh2).Range("H" & c).value = Sheets(sht1).Range("H" & i).value
     Sheets(sh2).Range("I" & c).value = Sheets(sht1).Range("I" & i).value
     Sheets(sh2).Range("J" & c).value = Sheets(sht1).Range("J" & i).value
     Sheets(sh2).Range("K" & c).value = Sheets(sht1).Range("K" & i).value
     Sheets(sh2).Range("L" & c).value = Sheets(sht1).Range("L" & i).value
     Sheets(sh2).Range("M" & c).value = Sheets(sht1).Range("M" & i).value
     Sheets(sh2).Range("N" & c).value = Sheets(sht1).Range("N" & i).value
     c = c + 1
End If
i = i + 1
Loop
This code does not do what you wanted. You have 2 sheets. Sheet 1 and a sheet called ON. Both sheets have the same data in them. If the dates in column L are between 2 dates, you want only those rows with those dates to be visible in your ON sheet and hide the other rows. I didn't make code to do that. The code I made inserts the rows that are between the 2 dates into the sheet named ON. The only thing I didn't put in the code that will prevent it to work properly is to clear the ON sheet of data before running the code. I didn't do this because I don't have that code memorized. What you need to do is enter clear code right after the "Sub Macro1()" and before the first line of code I wrote that says "sht1 = "Sheet 1"". Record a macro of you selecting columns A through N and press the delete button. Then end the macro recording. That is the easiest way to add clear code before pasting the code I wrote you.
Click to expand...

Sub Macro1()
' ActiveCell.Range("A1:N1").Select Selection.ClearContents
'sht1 = "ON 13-14"
sht2 = "Sheet1"
StartDate = Format("10/09/2014", "dd/mm/yyyy")
EndDate = Format("16/9/2014", "dd/mm/yyyy")
lastRow = Sheets(sht1).Range("L" & Rows.Count).End(xlUp).Row
i = 2 'if you don't have headers, change i = 2 to i = 1
c = 2
Do Until i > lastRow
If Sheets(sht1).Range("L" & i).Value >= StartDate And _
Sheets(sht1).Range("L" & i).Value <= EndDate Then
Sheets(sh2).Range("A" & c).Value = Sheets(sht1).Range("A" & i).Value
Sheets(sh2).Range("B" & c).Value = Sheets(sht1).Range("B" & i).Value
Sheets(sh2).Range("C" & c).Value = Sheets(sht1).Range("C" & i).Value
Sheets(sh2).Range("D" & c).Value = Sheets(sht1).Range("D" & i).Value
Sheets(sh2).Range("E" & c).Value = Sheets(sht1).Range("E" & i).Value
Sheets(sh2).Range("F" & c).Value = Sheets(sht1).Range("F" & i).Value
Sheets(sh2).Range("G" & c).Value = Sheets(sht1).Range("G" & i).Value
Sheets(sh2).Range("H" & c).Value = Sheets(sht1).Range("H" & i).Value
Sheets(sh2).Range("I" & c).Value = Sheets(sht1).Range("I" & i).Value
Sheets(sh2).Range("J" & c).Value = Sheets(sht1).Range("J" & i).Value
Sheets(sh2).Range("K" & c).Value = Sheets(sht1).Range("K" & i).Value
Sheets(sh2).Range("L" & c).Value = Sheets(sht1).Range("L" & i).Value
Sheets(sh2).Range("M" & c).Value = Sheets(sht1).Range("M" & i).Value
Sheets(sh2).Range("N" & c).Value = Sheets(sht1).Range("N" & i).Value
c = c + 1
End If
i = i + 1
Loop
' Macro1 Macro
'


'

End Sub
I didnt actually put the correct sheet names on my question, The information is on "ON 13-14" i would like it to be on "Sheet1"

This is what I have at the moment is this correct?
 
Upvote 0
The dataset that the code is searching through needs to be in "sht1". Whatever you named that sheet, enter it into the code...
Code: 
sht1 = "Sheet 1"
sht2 is where the output will go. It should be a blank sheet until you run the code for the first time. Lets say that the output will go in sheet 2.
Code: 
sht2 = "Sheet 2"

Now lets talk about that clear code you wrote. Did you use the record macro button to do that? It looks wrong. When you recorded the clear macro, did you highlight just A1 through N1? Because that's what it looks like. You need to highlight the entire columns of A through N. You see on excel right above cell A1 that there is a letter A there. If you click that letter, it highlights the entire column. Highlight columns A through N and press the delete button. Then click in cell A1 and stop recording. The code will look something like this but I'm not positive because I'm writing this from memory.
Code: 
Sheets(sh2).Range("A:N").clearContents
Try using the clear contents line of code above first before you record a macro. It might just work. I'm 90% sure it will work. You'll want to insert that line of code after "sht2 = Sheet Name"
 
Last edited:
Upvote 0
WarPiglet said:
The dataset that the code is searching through needs to be in "sht1". Whatever you named that sheet, enter it into the code...
Code: 
sht1 = "Sheet 1"
sht2 is where the output will go. It should be a blank sheet until you run the code for the first time. Lets say that the output will go in sheet 2.
Code: 
sht2 = "Sheet 2"

Now lets talk about that clear code you wrote. Did you use the record macro button to do that? It looks wrong. When you recorded the clear macro, did you highlight just A1 through N1? Because that's what it looks like. You need to highlight the entire columns of A through N. You see on excel right above cell A1 that there is a letter A there. If you click that letter, it highlights the entire column. Highlight columns A through N and press the delete button. Then click in cell A1 and stop recording. The code will look something like this but I'm not positive because I'm writing this from memory.
Code: 
Sheets(sh2).Range("A:N").clearContents
Try using the clear contents line of code above first before you record a macro. It might just work. I'm 90% sure it will work. You'll want to insert that line of code after "sht2 = Sheet Name"
Click to expand...


Yes i did just highlight the to row of cells.

I now have:
Sub Macro1()
'ActiveCell.Columns("A:N").EntireColumn.Select
Selection.ClearContents
ActiveCell.Select
'sht1 = "sht1"
sht2 = "sht2"
StartDate = Format("10/09/2014", "dd/mm/yyyy")
EndDate = Format("16/9/2014", "dd/mm/yyyy")
lastRow = Sheets(sht1).Range("L" & Rows.Count).End(xlUp).Row
i = 2 'if you don't have headers, change i = 2 to i = 1
c = 2
Do Until i > lastRow
If Sheets(sht1).Range("L" & i).Value >= StartDate And _
Sheets(sht1).Range("L" & i).Value <= EndDate Then
Sheets(sh2).Range("A" & c).Value = Sheets(sht1).Range("A" & i).Value
Sheets(sh2).Range("B" & c).Value = Sheets(sht1).Range("B" & i).Value
Sheets(sh2).Range("C" & c).Value = Sheets(sht1).Range("C" & i).Value
Sheets(sh2).Range("D" & c).Value = Sheets(sht1).Range("D" & i).Value
Sheets(sh2).Range("E" & c).Value = Sheets(sht1).Range("E" & i).Value
Sheets(sh2).Range("F" & c).Value = Sheets(sht1).Range("F" & i).Value
Sheets(sh2).Range("G" & c).Value = Sheets(sht1).Range("G" & i).Value
Sheets(sh2).Range("H" & c).Value = Sheets(sht1).Range("H" & i).Value
Sheets(sh2).Range("I" & c).Value = Sheets(sht1).Range("I" & i).Value
Sheets(sh2).Range("J" & c).Value = Sheets(sht1).Range("J" & i).Value
Sheets(sh2).Range("K" & c).Value = Sheets(sht1).Range("K" & i).Value
Sheets(sh2).Range("L" & c).Value = Sheets(sht1).Range("L" & i).Value
Sheets(sh2).Range("M" & c).Value = Sheets(sht1).Range("M" & i).Value
Sheets(sh2).Range("N" & c).Value = Sheets(sht1).Range("N" & i).Value
c = c + 1
End If
i = i + 1
Loop
' Macro1 Macro
'


'

End Sub


Hows this looking?

I dont quite understand macros, how would his activate dates to be shown and also after Loop at the bottom of the macro is that information correct?

Also do the (') that are in front of some instructions matter about their position
 
Upvote 0
Aladin Akyurek said:
@Harrywatson

You could consider adapting a formula system like the one given in:

http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

to your workbook.
Click to expand...

WarPiglet said:
The dataset that the code is searching through needs to be in "sht1". Whatever you named that sheet, enter it into the code...
Code: 
sht1 = "Sheet 1"
sht2 is where the output will go. It should be a blank sheet until you run the code for the first time. Lets say that the output will go in sheet 2.
Code: 
sht2 = "Sheet 2"

Now lets talk about that clear code you wrote. Did you use the record macro button to do that? It looks wrong. When you recorded the clear macro, did you highlight just A1 through N1? Because that's what it looks like. You need to highlight the entire columns of A through N. You see on excel right above cell A1 that there is a letter A there. If you click that letter, it highlights the entire column. Highlight columns A through N and press the delete button. Then click in cell A1 and stop recording. The code will look something like this but I'm not positive because I'm writing this from memory.
Code: 
Sheets(sh2).Range("A:N").clearContents
Try using the clear contents line of code above first before you record a macro. It might just work. I'm 90% sure it will work. You'll want to insert that line of code after "sht2 = Sheet Name"
Click to expand...

I have reposted but with a table to help people understand, --------------http://www.mrexcel.com/forum/excel-questions/804746-vlookup-code-help.html-------------
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
234
John_w
J
S
  • Solved
Convert text formatted date to date format
Replies
5
Views
295
Georgiboy
Georgiboy
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
357
kevin9999
K
C
  • Question
Revenue Allocation by Week
Replies
3
Views
226
ChetanPuri
C
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
248
AOB
AOB

Forum statistics

Threads
1,214,522
Messages
6,120,025
Members
448,939
Latest member
Leon Leenders

Share this page

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