Current Cell

[daniels012]

I have code for erasing data after I enter certain data in a cell then hit my button to execute the code. It works great.
Right now, whatever I enter in Cell A1. I want to change it to THE CURRENT CELL I'M IN. How do I write in code... the current cell

ie: Set Criteria1=Range("A1") I need to change A1 to current cell?? How?

Thank You,
Michael

[Rutgers_Excels]

Try this....

i = ActiveCell.Address
Range(i).Select

sets the active cell/cells as range

[daniels012]

I have other "Dim" in the code should I use:
Dim i As Range
then do as you suggested???

Thank You,
Michael

[Rutgers_Excels]

Do not Dim as Range.

Just make sure you set i at the top of your code so it sets i to the current active cell. Once you start running your code, the active cell may be changed (depending on what your code is for)

[daniels012]

One more thing, I hope you can help (Thank You by the way)
I have a list of numbers, I hit a button and it erases the number I enter in the current cell!! Great! However if I enter a number that was already deleted nothing happens. I would like a message to appear that the number is already used. Is this possible and where would I put it?
I will include all the code so you can see it. It works great!

Sub RemovePlayer()
Dim PlayerNumbers As Range
Dim c As Range
Dim Criteria1 As Range

i = ActiveCell.Address
Range(i).Select
Set Criteria1 = Range(i)
Set PlayerNumbers = Range("b2:s11")
For Each c In PlayerNumbers
If c.Value = Criteria1.Value Then
c.ClearContents
Else
End If
Next c

Thank You,
Michael

[Rutgers_Excels]

Do you want a message box to come up if the number you enter in the "current cell" is not found in the range? I don't know what you mean when you said "number is already used".

[daniels012]

Sorry, I am very new to code. Yes, a message box to come up if the number you enter in the "current cell" is not found in the range?

Thank You,
Michael

[Rutgers_Excels]

I modified your code a little bit. There's probably an easier way. But try this out....


Sub RemovePlayer()
Dim PlayerNumbers As Range
Dim c As Range
Dim Criteria1 As Range

i = ActiveCell.Address
Range(i).Select
Set Criteria1 = Range(i)
Set PlayerNumbers = Range("b2:s11")

For Each b In PlayerNumbers
If b.Value = Criteria1.Value Then
GoTo DeleteNumber
End If
Next b

MsgBox ("Number is already used!")
End

DeleteNumber:
For Each c In PlayerNumbers
If c.Value = Criteria1.Value Then
c.ClearContents
Else
End If
Next c
End

End Sub