# LOOKUP help possibly

#### Harrywatson

##### Board Regular
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)

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.

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.

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.

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?

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:
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"

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

@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

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"

I have reposted but with a table to help people understand, --------------http://www.mrexcel.com/forum/excel-questions/804746-vlookup-code-help.html-------------

Replies
1
Views
302
Replies
3
Views
165
Replies
5
Views
347
Replies
3
Views
416
Replies
3
Views
343

1,216,409
Messages
6,130,407
Members
449,581
Latest member
econtent2

### 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.

### Which adblocker are you using?

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

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