I'm trying to use a formula to replace or delete text in another cell.

I have a list from 1-10. I want to generate a result from 1-10 and delete
that number from the list.

Example: Cell A1 will determine which number is deleted, and cells B1:B10
contain the numbers 1 through 10, respectively. Let's say cell A1 has the
number 2. The formula should find the number 2 in the range (b1:b10) and
delete it.

This doesn't seem too hard but I can't figure it out.

Thanks!

Chris

I actually want to do this in a Visual Basic button command, so if there's a
way to get the cursor to go to a specific cell based on the result of a
formula, that's what I need. Thanks!

"opieandy" wrote:

I'm trying to use a formula to replace or delete text in another cell.

I have a list from 1-10. I want to generate a result from 1-10 and delete
that number from the list.

Example: Cell A1 will determine which number is deleted, and cells B1:B10
contain the numbers 1 through 10, respectively. Let's say cell A1 has the
number 2. The formula should find the number 2 in the range (b1:b10) and
delete it.

This doesn't seem too hard but I can't figure it out.

Thanks!

Chris

Try the below macro which works on the activesheet. Try and feedback

Sub Macro()
Set varfound = Range("B1:B10").Find(Range("A1"))
If Not varfound Is Nothing Then
Range("B" & varfound.Row) = ""
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"opieandy" wrote:

I actually want to do this in a Visual Basic button command, so if there's a
way to get the cursor to go to a specific cell based on the result of a
formula, that's what I need. Thanks!

"opieandy" wrote:

I'm trying to use a formula to replace or delete text in another cell.

I have a list from 1-10. I want to generate a result from 1-10 and delete
that number from the list.

Example: Cell A1 will determine which number is deleted, and cells B1:B10
contain the numbers 1 through 10, respectively. Let's say cell A1 has the
number 2. The formula should find the number 2 in the range (b1:b10) and
delete it.

This doesn't seem too hard but I can't figure it out.

Thanks!

Chris

Thanks, Jacob, that worked!!!

"Jacob Skaria" wrote:

Try the below macro which works on the activesheet. Try and feedback

Sub Macro()
Set varfound = Range("B1:B10").Find(Range("A1"))
If Not varfound Is Nothing Then
Range("B" & varfound.Row) = ""
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"opieandy" wrote:

I actually want to do this in a Visual Basic button command, so if there's a
way to get the cursor to go to a specific cell based on the result of a
formula, that's what I need. Thanks!

"opieandy" wrote:

I'm trying to use a formula to replace or delete text in another cell.

I have a list from 1-10. I want to generate a result from 1-10 and delete
that number from the list.

Example: Cell A1 will determine which number is deleted, and cells B1:B10
contain the numbers 1 through 10, respectively. Let's say cell A1 has the
number 2. The formula should find the number 2 in the range (b1:b10) and
delete it.

This doesn't seem too hard but I can't figure it out.

Thanks!

Chris