range.find wont find single "," comma occurance

[romanemul]

Hi community

my sub wont find single occurence of "," only when i type additional comma it started to work.
So only cells with 2 and more occurances of the comma character are processed correctly.

Sub proc()


Dim inp As Range
Dim outp As Range


Cells(1, 7).Activate
Set inp = ActiveCell.Find(What:=",")


End Sub

inp always returns Nothing

any help would be appreciated

 

[romanemul]

The lookin parameter worked

Thank you


Correct code

[COLOR=#00007F][FONT=Courier][I]Sub[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] h()[/I][/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier][I]Dim[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] inp [/I][/FONT][/COLOR][COLOR=#00007F][FONT=Courier][I]As[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] Range[/I][/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier][I]Dim[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] outp [/I][/FONT][/COLOR][COLOR=#00007F][FONT=Courier][I]As[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] Range[/I][/FONT][/COLOR]

[COLOR=#333333][FONT=Courier][I]Cells(1, 7).Activate[/I][/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier][I]Set[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] inp = ActiveCell.Find(What:=",", [/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier]LookIn:=xlValues[/FONT][/COLOR][COLOR=#333333][FONT=Courier][I])[/I][/FONT][/COLOR]

[COLOR=#333333][FONT=Courier][I]MsgBox "Found it = " & [/I][/FONT][/COLOR][COLOR=#00007F][FONT=Courier][I]Not[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] inp [/I][/FONT][/COLOR][COLOR=#00007F][FONT=Courier][I]Is[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] [/I][/FONT][/COLOR][COLOR=#00007F][FONT=Courier][I]Nothing[/I][/FONT][/COLOR]

[COLOR=#00007F][FONT=Courier][I]End[/I][/FONT][/COLOR][COLOR=#333333][FONT=Courier][I] [/I][/FONT][/COLOR][COLOR=#00007F][FONT=Courier][I]Sub[/I][/FONT][/COLOR]

I was assuming a comma insterted as an actual string value. If the comma is there because of cell formatting, for instance, the cell displays 1,234 but the formula bar shows 1234, then you'll need to specify the LookIn parameter.

**Set inp = ActiveCell.Find(What:=",", LookIn:=xlValues)

 

[mikerickson]

its a cell whose purpose is to detect if the number has comma in it. so i throw in there random numbers

Just to be clear the VBA .Find method in the OP will find a cell that has a comma it will not FIND (like the worksheet function) the location of a comma within a cell.

Is the comma a thousand's delimiter imposed by number formatting?
If so, .Find will not find it.

.Find can look in the .Value, the .Formula or the .Comment properties of cells. But the comma is in none of those (it is in the .Text of the cell)

 

[GTO]

I am glad we were able to help. Just to explain, .Find is easier to think of as looking for a string value, regardless of whether it is searching for a string (text), date or number; and it is going to be looking in either the formula bar or in the cell - in either case, as displayed...

 

[romanemul]

i want just to ask a quick question.

When using method find to find for example letter of alphabet "A" The system doesnt need to know the LookIn parameter to determine whenever its a formula or value.
So why its required when entering a comma ? Does need to be that value evaluated somehow ?

 

[romanemul]

Thank you

Just to be clear the VBA .Find method in the OP will find a cell that has a comma it will not FIND (like the worksheet function) the location of a comma within a cell.

Is the comma a thousand's delimiter imposed by number formatting?
If so, .Find will not find it.

.Find can look in the .Value, the .Formula or the .Comment properties of cells. But the comma is in none of those (it is in the .Text of the cell)

 

[GTO]

...
Is the comma a thousand's delimiter imposed by number formatting?
If so, .Find will not find it.

.Find can look in the .Value, the .Formula or the .Comment properties of cells. But the comma is in none of those (it is in the .Text of the cell)

Hi Mike,

I agree that in the case that the cell is formatted for Number and Thousands Separator is ticked, then the comma really "exists" in .Text. I would hold that xlValues actually tests against .Text though. At least for me, the code at #11 works (for a partial match of course). Do your results differ?

Mark