Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Values and Copy Rows
Hi all, I have two Sheets in a Workbook. One Sheet name is "Data" and
the other Sheet name is "Search". In Sheet "Search" I have three TextBoxes and in top cell of each TextBoxe I have put headings like, TextBox1 heading is "Serial" , TextBox2 heading is "Record" and TextBox3 heading is "Quantity". In Sheet "Data" i have data from Column A to Column F. In Column A i put "Serials" , in Column B i put "Record no." and in column C i put "Quantity". In other Columns (D to F) i have text and other sort of data. I want macro on a button which should check values in each TextBox of Sheet "Search" and match those values in Columns A to C of Sheet "Data" and if that value string match then macro should copy that row from Column A to Column F of Sheet "Data" into Sheet "Search" For example if i put value "334" in TextBox1 and "SC0001" in TextBox2 and "45" in TextBox3 then macro should match TextBox1 value in Column A of Sheet "Data" and TextBox2 value in Column B and then TextBox3 value in Column C and if all three values get matched in Sheet "Data" columns on same row then macro should copy that row into Sheet "Search". I hope i was able to explain my question. Is there any friend can help me on this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Values and Copy Rows
try this
Sub Macro1() ' ' Macro1 Macro ' With Sheets("Sheet2") Set Serial = .TextBoxes("Textbox 1").Value Set Record = .TextBoxes("Textbox 2").Value Set Quantity = .TextBoxes("Textbox 3").Value With Sheets("Sheet1") 'turn off autofilter If Worksheets("Sheet1").AutoFilterMode Then Selection.AutoFilter End If .Columns("A:C").AutoFilter Field:=1, Criteria1:=Serial .Columns("A:C").AutoFilter Field:=2, Criteria1:=Record .Columns("A:C").AutoFilter Field:=3, Criteria1:=Quantity .Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _ Destination:=Sheets("Sheet2").Cells 'turn off autofilter If Worksheets("Sheet1").AutoFilterMode Then Selection.AutoFilter End If End With End With End Sub Sub Macro2() ' ' Macro2 Macro ' ' Selection.AutoFilter Columns("A:B").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$B$2").AutoFilter Field:=2 End Sub Sub Macro3() ' ' Macro3 Macro ' ' Selection.AutoFilter Range("F13").Select ActiveSheet.Range("$A$1:$C$14").AutoFilter Field:=2, Criteria1:="14" End Sub "K" wrote: Hi all, I have two Sheets in a Workbook. One Sheet name is "Data" and the other Sheet name is "Search". In Sheet "Search" I have three TextBoxes and in top cell of each TextBoxe I have put headings like, TextBox1 heading is "Serial" , TextBox2 heading is "Record" and TextBox3 heading is "Quantity". In Sheet "Data" i have data from Column A to Column F. In Column A i put "Serials" , in Column B i put "Record no." and in column C i put "Quantity". In other Columns (D to F) i have text and other sort of data. I want macro on a button which should check values in each TextBox of Sheet "Search" and match those values in Columns A to C of Sheet "Data" and if that value string match then macro should copy that row from Column A to Column F of Sheet "Data" into Sheet "Search" For example if i put value "334" in TextBox1 and "SC0001" in TextBox2 and "45" in TextBox3 then macro should match TextBox1 value in Column A of Sheet "Data" and TextBox2 value in Column B and then TextBox3 value in Column C and if all three values get matched in Sheet "Data" columns on same row then macro should copy that row into Sheet "Search". I hope i was able to explain my question. Is there any friend can help me on this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Values and Copy Rows
On Jul 20, 2:22*pm, Joel wrote:
try this Sub Macro1() ' ' Macro1 Macro ' With Sheets("Sheet2") * *Set Serial = .TextBoxes("Textbox 1").Value * *Set Record = .TextBoxes("Textbox 2").Value * *Set Quantity = .TextBoxes("Textbox 3").Value * *With Sheets("Sheet1") * * * 'turn off autofilter * * * If Worksheets("Sheet1").AutoFilterMode Then * * * * *Selection.AutoFilter * * * End If * * * .Columns("A:C").AutoFilter Field:=1, Criteria1:=Serial * * * .Columns("A:C").AutoFilter Field:=2, Criteria1:=Record * * * .Columns("A:C").AutoFilter Field:=3, Criteria1:=Quantity * * * .Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _ * * * * *Destination:=Sheets("Sheet2").Cells * * * 'turn off autofilter * * * If Worksheets("Sheet1").AutoFilterMode Then * * * * *Selection.AutoFilter * * * End If * *End With End With End Sub Sub Macro2() ' ' Macro2 Macro ' ' * * Selection.AutoFilter * * Columns("A:B").Select * * Selection.AutoFilter * * ActiveSheet.Range("$A$1:$B$2").AutoFilter Field:=2 End Sub Sub Macro3() ' ' Macro3 Macro ' ' * * Selection.AutoFilter * * Range("F13").Select * * ActiveSheet.Range("$A$1:$C$14").AutoFilter Field:=2, Criteria1:="14" End Sub "K" wrote: Hi all, I have two Sheets in a Workbook. *One Sheet name is "Data" and the other Sheet name is "Search". *In Sheet "Search" I have three TextBoxes and in top cell of each TextBoxe I have put headings like, TextBox1 heading is "Serial" , TextBox2 heading is "Record" and TextBox3 heading is "Quantity". *In Sheet "Data" i have data from Column A to Column F. *In Column A i put "Serials" , in Column B i put "Record no." and in column C i put "Quantity". *In other Columns (D to F) i have text and other sort of data. *I want macro on a button which should check values in each TextBox of Sheet "Search" and match those values in Columns A to C of Sheet "Data" and if that value string match then macro should copy that row from Column A to Column F of Sheet "Data" into Sheet "Search" For example if i put value "334" in TextBox1 and "SC0001" in TextBox2 and "45" in TextBox3 then macro should match TextBox1 value in Column A of Sheet "Data" and TextBox2 value in Column B and then TextBox3 value in Column C and if all three values get matched in Sheet "Data" columns on same row then macro should copy that row into Sheet "Search". *I hope i was able to explain my question. *Is there any friend can help me on this.- Hide quoted text - - Show quoted text - thanks joel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Values and Copy Rows
Joel
Permit me to ask you for some help: CHecking your code and comparing wuith my demand, I would like to have your support on the folowing purpose: I have a huge spreadsheet with a lot of information. I need to compare two diffents columns and paste the result in another column.: i.e.: If(cell A1 = B1, Paste in C1 = "OK", If not Paste "NOK" I understand that I can have this using Excel formula, but I need to have a macro in order to I need to include this solution inside another Macro Code. I really appreciate if you could help me. Thanks and regards Mota "Joel" wrote: try this Sub Macro1() ' ' Macro1 Macro ' With Sheets("Sheet2") Set Serial = .TextBoxes("Textbox 1").Value Set Record = .TextBoxes("Textbox 2").Value Set Quantity = .TextBoxes("Textbox 3").Value With Sheets("Sheet1") 'turn off autofilter If Worksheets("Sheet1").AutoFilterMode Then Selection.AutoFilter End If .Columns("A:C").AutoFilter Field:=1, Criteria1:=Serial .Columns("A:C").AutoFilter Field:=2, Criteria1:=Record .Columns("A:C").AutoFilter Field:=3, Criteria1:=Quantity .Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _ Destination:=Sheets("Sheet2").Cells 'turn off autofilter If Worksheets("Sheet1").AutoFilterMode Then Selection.AutoFilter End If End With End With End Sub Sub Macro2() ' ' Macro2 Macro ' ' Selection.AutoFilter Columns("A:B").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$B$2").AutoFilter Field:=2 End Sub Sub Macro3() ' ' Macro3 Macro ' ' Selection.AutoFilter Range("F13").Select ActiveSheet.Range("$A$1:$C$14").AutoFilter Field:=2, Criteria1:="14" End Sub "K" wrote: Hi all, I have two Sheets in a Workbook. One Sheet name is "Data" and the other Sheet name is "Search". In Sheet "Search" I have three TextBoxes and in top cell of each TextBoxe I have put headings like, TextBox1 heading is "Serial" , TextBox2 heading is "Record" and TextBox3 heading is "Quantity". In Sheet "Data" i have data from Column A to Column F. In Column A i put "Serials" , in Column B i put "Record no." and in column C i put "Quantity". In other Columns (D to F) i have text and other sort of data. I want macro on a button which should check values in each TextBox of Sheet "Search" and match those values in Columns A to C of Sheet "Data" and if that value string match then macro should copy that row from Column A to Column F of Sheet "Data" into Sheet "Search" For example if i put value "334" in TextBox1 and "SC0001" in TextBox2 and "45" in TextBox3 then macro should match TextBox1 value in Column A of Sheet "Data" and TextBox2 value in Column B and then TextBox3 value in Column C and if all three values get matched in Sheet "Data" columns on same row then macro should copy that row into Sheet "Search". I hope i was able to explain my question. Is there any friend can help me on this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Values based on a Match Q | Excel Programming | |||
Match and copy rows from 2 sheets | Excel Programming | |||
Match Values in Rows with Partial Values in Columns | Excel Worksheet Functions | |||
copy rows that do not match values in dynamic range. | Excel Programming | |||
How do i compare values from two sheet and copy & paste if values match? | Excel Programming |