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

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/

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

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

.

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/

.

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

.

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

.



.