Search by date and paste to a second sheet.

[Trueblue862]

Hi, I'm trying to figure out how to do this. I need a macro to search column A of sheet1 for a date which the user will enter into textboxA. When it finds the date in column A I then need it to transfer the values across that row to Sheet2, after row 2 (two row header). The what I'm really struggling with is on sheet2 the data needs to be in a different order and not all of it is used. The columns are as follows. Sheet 1 column B needs to go to sheet 2 column A, Sheet 1 column c needs to go to sheet 2 column b, Sheet 1 column c needs to go to sheet 2 column g. If anyone can help with this it would be fantastic.

 

[WarPigl3t]

Super easy. Gimme a few minutes.

 

[Trueblue862]

If it helps I have my dates formatted DD/MM/YYYY

 

[WarPigl3t]

'Sheet1 Constant Variables Public Const sht1 = "Sheet1" Public Const firstRow_Sht1 = 2 Public Const findCol = 1 Public Const inputCol1 = 2 Public Const inputCol2 = 3 Public Const inputCol3 = 3 'Sheet2 Constant Variables Public Const sht2 = "Sheet2" Public Const firstRow_sht2 = 2 Public Const outputCol1 = 1 Public Const outputCol2 = 2 Public Const outputCol3 = 7 Sub findSub() textboxA = 'Enter your textbox value here. r = firstRow_Sht1 lastRow_Sht1 = Sheets(sht1).Cells(Rows.Count, findCol).End(xlUp).Row Do Until r > lastRow_Sht1 Do Events findValue = Sheets(Sht1).Cells(r, findCol).Value If findValue = textboxA Then output1 = Sheets(Sht1).Cells(r, inputCol1).Value output2 = Sheets(Sht1).Cells(r, inputCol2).Value output3 = Sheets(Sht1).Cells(r, inputCol3).Value Call outputFunction(output1, output2, output3) End If r = r + 1 Loop End Sub Function outputFunction(output1, output2, output3) outputRow = Sheets(Sht2).Cells(Rows.Count, outputCol1).End(xlUp).Row + 1 Sheets(Sht2).Cells(outputRow, outputCol1).Value = output1 Sheets(Sht2).Cells(outputRow, outputCol2).Value = output2 Sheets(Sht2).Cells(outputRow, outputCol3).Value = output3 End Function

 

[WarPigl3t]

textboxA = 'Enter your textbox value here.

Just guessing that you are using a userform and that you didnt change the name of the userform. It would be...

textboxA = Userform1.TextboxA.Value

 

[Trueblue862]

Thank you for your help, I'm getting several errors, I'll have a play with them and see if I can sort them out.

 

[Trueblue862]

I'm getting a lot of "Object variable or With block variable not set" errors with this code. I've tried getting it sorted but I'm not having any luck with it.

 

[WarPigl3t]

Sounds like your code might have Option Explicit on. And are you making sure that the constant vairables are at the very top of any of your already existing code? And are you putting my code in a Module?

 

[WarPigl3t]

Make it easy. Just create a new module and paste just my code in it. Then from your submit button on your userform, make is call the findSub macro.

 

[Trueblue862]

That sorted it. Thank you.