Select active cell and return value

Hi

I would like the user to be able to select any cell they wish and then click on a button to active my macro.

The problem I am having is getting the value of the selected cell e.g. D4

All I can get is the value 4.

I would then like to add this to a range so that I can use a loop to interigate the data.

For example:

If the user selected the cell D4 and the data went all the way down to D100 my range would be D4:D100

Here is my code (I had help from a previous post to get this far!)

Sub SearchString()

'Sets the cells in range from top to bottom
MyRange = "D4:D100"

'Declares Count as an Integer
Count = 0

'Declares CountNull as an Integer
CountNull = 0

For Each myCell In Range(MyRange)

'If two or more cells have a Null value in sequence then the loop ends
'Amend the value to 'x' for greater Null cells
If CountNull = 2 Then
Exit For
End If

'Checks if cell has a Null value
If myCell < 1 Then
'Allows one cell to be Null
CountNull = CountNull + 1
'Skips a line
GoTo NextLoop
Else
'Resets value so that the count is correct
CountNull = 0
End If

'searches for the occurance of tlp or TLP in the string
If InStr(myCell, "tlp") > 0 Or InStr(myCell, "TLP") > 0 Then
myCell.Offset(0, -2) = "Yes"
'Adds one to the count
Count = Count + 1

Else
'if neither is found then No is returned
myCell.Offset(0, -2) = "No"

End If

'searches for the occurance of tnt or TNT in the string
If InStr(myCell, "tnt") > 0 Or InStr(myCell, "TNT") > 0 Then
myCell.Offset(0, -3) = "Yes"
'Adds one to the count
Count = Count + 1
Else
'if neither is found then No is returned
myCell.Offset(0, -3) = "No"
End If

'Checks the value of Count, IF 2 then both strings have been found

If Count = 2 Then
'Count is two so BOTH strings have been found
myCell.Offset(0, -1) = "Yes"
Else
'Value of Count is either zero or one
myCell.Offset(0, -1) = "No"
End If

'Re-sets the value of count ready for Next Loop
Count = 0

NextLoop:
Next myCell

End Sub

I hope I have explained this clearly enough!

Just being able to work out the actual cell value would be great e.g. D4

hi,

is this what you want?

rng = ActiveCell.Address

dave

That's it !!!!

I was using a variable as an integer and not a string.

.Address works a treat.

Thank you sweep !!

you're welcome - glad it works