Use Formula to Replace A Cell

[opieandy]

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

 

[opieandy]

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!

 

[Jacob Skaria]

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

 

[opieandy]

Thanks, Jacob, that worked!!!

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