Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to match number in a column
Hi everyone,
In my table below, I'm trying to match a designated number, find it (i.e.goto it!) and delete the number beside it. I will then manipulate the data range after this action but don't know how to write the macro to do this part. Could anyone help with the code, please. Thankyou. ColZ ColAA Row 73 2 Enter a number in Z73 Say number 2 Row 74 6 3 Match and find number 2 anywhere 75 10 8 in the range Z74 to Z97. 76 9 8 77 8 9 78 1 11 79 2 13 Delete the number beside it (13) 80 11 15 81 5 19 82 12 20 83 7 20 84 15 38 85 13 41 86 14 59 87 4 63 88 16 70 89 17 no data 90 18 '' 91 19 '' 92 20 '' 93 21 '' 94 22 '' 95 23 '' 96 24 '' 97 3 '' |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to match number in a column
Hi Rick,
Try this for a starter: Dim r, c As Integer r = 74 c = 26 Do While Cells(r, c) "" If Cells(r, c) = Cells(73, 26) Then Cells(r, c + 1).ClearContents End If r = r + 1 Loop ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to match number in a column
Range("Z74:z97").Find(what:=Range("z73").Value, _
lookat:=xlWhole).Offset(0, 1).ClearContents Alan Beban Rick wrote: Hi everyone, In my table below, I'm trying to match a designated number, find it (i.e.goto it!) and delete the number beside it. I will then manipulate the data range after this action but don't know how to write the macro to do this part. Could anyone help with the code, please. Thankyou. ColZ ColAA Row 73 2 Enter a number in Z73 Say number 2 Row 74 6 3 Match and find number 2 anywhere 75 10 8 in the range Z74 to Z97. 76 9 8 77 8 9 78 1 11 79 2 13 Delete the number beside it (13) 80 11 15 81 5 19 82 12 20 83 7 20 84 15 38 85 13 41 86 14 59 87 4 63 88 16 70 89 17 no data 90 18 '' 91 19 '' 92 20 '' 93 21 '' 94 22 '' 95 23 '' 96 24 '' 97 3 '' |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to match number in a column
Thanks Alan for your assistance,
Regards, Rick -----Original Message----- Range("Z74:z97").Find(what:=Range("z73").Value, _ lookat:=xlWhole).Offset(0, 1).ClearContents Alan Beban Rick wrote: Hi everyone, In my table below, I'm trying to match a designated number, find it (i.e.goto it!) and delete the number beside it. I will then manipulate the data range after this action but don't know how to write the macro to do this part. Could anyone help with the code, please. Thankyou. ColZ ColAA Row 73 2 Enter a number in Z73 Say number 2 Row 74 6 3 Match and find number 2 anywhere 75 10 8 in the range Z74 to Z97. 76 9 8 77 8 9 78 1 11 79 2 13 Delete the number beside it (13) 80 11 15 81 5 19 82 12 20 83 7 20 84 15 38 85 13 41 86 14 59 87 4 63 88 16 70 89 17 no data 90 18 '' 91 19 '' 92 20 '' 93 21 '' 94 22 '' 95 23 '' 96 24 '' 97 3 '' . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to match number in a column
Hi Zantor,
Incorporated your code. Works fine thank you very much. Cheers! Rick -----Original Message----- Hi Rick, Try this for a starter: Dim r, c As Integer r = 74 c = 26 Do While Cells(r, c) "" If Cells(r, c) = Cells(73, 26) Then Cells(r, c + 1).ClearContents End If r = r + 1 Loop ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to match number in a column
It works for me--*with* the relevant sheet active at the time the Sub is
run. Otherwise, assuming your table is on Sheets("whatever"), change it to With Sheets("whatever") .Range("Z74:z97").Find(what:=.Range("z73").Value, _ lookat:=xlWhole).Offset(0, 1).ClearContents End With Let us know what happens. Alan Beban Rick wrote: Hello again Alan, Tried the code but it crashes with message "Unable to Find Property or Range Class". Could there be a typo or do I have to add something else to the code. All looks logical to me. Thanks and regards, Rick -----Original Message----- Range("Z74:z97").Find(what:=Range("z73").Value , _ lookat:=xlWhole).Offset(0, 1).ClearContents Alan Beban Rick wrote: Hi everyone, In my table below, I'm trying to match a designated number, find it (i.e.goto it!) and delete the number beside it. I will then manipulate the data range after this action but don't know how to write the macro to do this part. Could anyone help with the code, please. Thankyou. ColZ ColAA Row 73 2 Enter a number in Z73 Say number 2 Row 74 6 3 Match and find number 2 anywhere 75 10 8 in the range Z74 to Z97. 76 9 8 77 8 9 78 1 11 79 2 13 Delete the number beside it (13) 80 11 15 81 5 19 82 12 20 83 7 20 84 15 38 85 13 41 86 14 59 87 4 63 88 16 70 89 17 no data 90 18 '' 91 19 '' 92 20 '' 93 21 '' 94 22 '' 95 23 '' 96 24 '' 97 3 '' . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to match number in a column
Hello Alan,
Thanks for that. If I include the With statements it works perfectly. Appreciate your response. Much obliged. Cheers!, Rick -----Original Message----- It works for me--*with* the relevant sheet active at the time the Sub is run. Otherwise, assuming your table is on Sheets ("whatever"), change it to With Sheets("whatever") .Range("Z74:z97").Find(what:=.Range("z73").Value, _ lookat:=xlWhole).Offset(0, 1).ClearContents End With Let us know what happens. Alan Beban Rick wrote: Hello again Alan, Tried the code but it crashes with message "Unable to Find Property or Range Class". Could there be a typo or do I have to add something else to the code. All looks logical to me. Thanks and regards, Rick -----Original Message----- Range("Z74:z97").Find(what:=Range("z73").Valu e, _ lookat:=xlWhole).Offset(0, 1).ClearContents Alan Beban Rick wrote: Hi everyone, In my table below, I'm trying to match a designated number, find it (i.e.goto it!) and delete the number beside it. I will then manipulate the data range after this action but don't know how to write the macro to do this part. Could anyone help with the code, please. Thankyou. ColZ ColAA Row 73 2 Enter a number in Z73 Say number 2 Row 74 6 3 Match and find number 2 anywhere 75 10 8 in the range Z74 to Z97. 76 9 8 77 8 9 78 1 11 79 2 13 Delete the number beside it (13) 80 11 15 81 5 19 82 12 20 83 7 20 84 15 38 85 13 41 86 14 59 87 4 63 88 16 70 89 17 no data 90 18 '' 91 19 '' 92 20 '' 93 21 '' 94 22 '' 95 23 '' 96 24 '' 97 3 '' . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH A NUMBER IN A COLUMN WITH A COLUMN IN A DIFFERENT SHEET | Excel Discussion (Misc queries) | |||
Need to match 2 columns, if a match found add info from 2nd column | Excel Worksheet Functions | |||
MATCH A NUMBER AS THE SUM OF VALUES IN A COLUMN | Excel Worksheet Functions | |||
Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
Any way for 2 column vlookups. i.e match last name then match firs | Excel Worksheet Functions |