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

 

[BarryL]

so the , is always in the activecell? try:

If Instr(Cells(1,7).Value,",")>0 Then Set inp = Cells(1,7)

 

[romanemul]

thanks . instr method worked . however i still want to know where is the problem with the find method.
something tells me it had something to do with the excel autoformat feature

So bump.

 

[BarryL]

what was an example of a piece of text where it would not pick up the ","?

 

[GTO]

What makes you think it is not being found? It seems to work for me.

Sub h()
Dim inp As Range
Dim outp As Range

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

MsgBox "Found it = " & Not inp Is Nothing

End Sub


Mark

 

[romanemul]

what was an example of a piece of text where it would not pick up the ","?

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

 

[romanemul]

Mark your code return same results. only returns true when 2+ commas appear.

What makes you think it is not being found? It seems to work for me.

Sub h()
Dim inp As Range
Dim outp As Range

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

MsgBox "Found it = " & Not inp Is Nothing

End Sub


Mark

 

[BarryL]

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

like 100,000? but is that formatted in accounting style or it it an actual ","?

 

[GTO]

Mark your code return same results. only returns true when 2+ commas appear.

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.

<font face=Courier New>**<SPAN style="color:#00007F">Set</SPAN> inp = ActiveCell.Find(What:=",", LookIn:=xlValues)</FONT>

 

[romanemul]

i have tried many ways and many formats. Tried changing variants, objects , ranges. checked different find method parameters and even recorded formula returns wrong results.

Few hours ago this function looked totaly trivial to me until i found the comma problem.

I think i return to the instr function until then