LOOKUP help possibly

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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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
Joined
Jan 20, 2014
Messages
92
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
Joined
May 25, 2014
Messages
1,609
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
Joined
Jan 20, 2014
Messages
92

ADVERTISEMENT

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
Joined
Jan 20, 2014
Messages
92
@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.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

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
Top