Syntax for variable search
 

Are the brackets shown because the number is negative - therefore they are
produced by a number format?

If they are not, then the entries are text and can be found with

"(*)"
and lookat:=xlWhole

although this would hit on "(abc)" as well. You would then need to check
if the interior is numeric.

Regards,
Tom Ogilvy


"brym" wrote in message
...
Hi!
This macro I would like to enhance to be more flexible. But before I start
doing (trying) that, I need some help re. the searching syntax.
There's no problem finding a specified part of a string and change the
properties. However, (as I expected) the SQL syntax doesn't work with the
FindWhat var.
Can anyone help me with the syntax so I may find/isolate any number
surrounded by brackets, e.g (98765) or (1234567).

Sub PartOfString()

Dim Pos, Lngt As Integer
Dim FindWhat As String

On Error Resume Next

FindWhat = "Perfect" ' Should be "(" and any whole number and ")"

Cells.Find(what:=FindWhat, after:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

Lngt = Len(FindWhat)
Pos = InStr(ActiveCell.Value, FindWhat)

With ActiveCell.Characters(Start:=Pos, Length:=Lngt).Font
.Name = "Arial"
.FontStyle = "Normal"
End With

End Sub

OR MAYBE - another way to do the trick !?

Thanks in advance

Regards Birger

Syntax for variable search
 

Addendum

Dim rng as Range
set rng = Cells.Find(what:="(*)", after:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)


Should work for negative numbers.

Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Are the brackets shown because the number is negative - therefore they are
produced by a number format?

If they are not, then the entries are text and can be found with

"(*)"
and lookat:=xlWhole

although this would hit on "(abc)" as well. You would then need to
check
if the interior is numeric.

Regards,
Tom Ogilvy


"brym" wrote in message
...
Hi!
This macro I would like to enhance to be more flexible. But before I
start
doing (trying) that, I need some help re. the searching syntax.
There's no problem finding a specified part of a string and change the
properties. However, (as I expected) the SQL syntax doesn't work with
the
FindWhat var.
Can anyone help me with the syntax so I may find/isolate any number
surrounded by brackets, e.g (98765) or (1234567).

Sub PartOfString()

Dim Pos, Lngt As Integer
Dim FindWhat As String

On Error Resume Next

FindWhat = "Perfect" ' Should be "(" and any whole number and
")"

Cells.Find(what:=FindWhat, after:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

Lngt = Len(FindWhat)
Pos = InStr(ActiveCell.Value, FindWhat)

With ActiveCell.Characters(Start:=Pos, Length:=Lngt).Font
.Name = "Arial"
.FontStyle = "Normal"
End With

End Sub

OR MAYBE - another way to do the trick !?

Thanks in advance

Regards Birger

Syntax for variable search
 

Hi Tom!

In my first post I changed the FindWhat to "(*)" and xlWhole. Now it only
hits cells containing (number) and moreover only the first 3 letters
are changed to bold.

And no, it will always be in connection with a text.

In my sheets i could have this in a cell:

this is a sample (12345) cell value

I want only (12345) to be e.g. bold. As mentioned in my later post, I can do
this, IF I know the number of letters I'm searching for (?????). I would
like only the brackets to be the limits.

(Hitting an (abc) is ok. I'll find a solution for that.)

Regards Birger

Syntax for variable search
 

Sub PartOfString()
Dim rng As Range
Dim firstAddress As String
Set rng = Cells.Find(what:="(*)", after:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
firstAddress = rng.Address
Do
With rng.Characters(Start:=2, _
Length:=Len(rng) - 2).Font
.Name = "Arial"
.FontStyle = "Bold"
End With
Set rng = Cells.FindNext(rng)
If rng Is Nothing Then Exit Do
Loop While rng.Address < firstAddress
End If
End Sub

Assuming you don't want to work on cells like

this cell has (12345) in it

but only cells like

(12345)

--
Regards,
Tom Ogilvy



"brym" wrote in message
...
Hi Tom!

In my first post I changed the FindWhat to "(*)" and xlWhole. Now it only
hits cells containing (number) and moreover only the first 3 letters
are changed to bold.

And no, it will always be in connection with a text.

In my sheets i could have this in a cell:

this is a sample (12345) cell value

I want only (12345) to be e.g. bold. As mentioned in my later post, I can
do
this, IF I know the number of letters I'm searching for (?????). I would
like only the brackets to be the limits.

(Hitting an (abc) is ok. I'll find a solution for that.)

Regards Birger