# 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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### WarPigl3t

##### Well-known Member
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.

#### Harrywatson

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

#### WarPigl3t

##### Well-known Member
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:

#### Harrywatson

##### Board Regular

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

##### Board Regular
@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------------- Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,199
Messages
5,835,936
Members
430,396
Latest member
dzifna ### 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