Find cells without multiple spacebars and format...
 

(Re-posting. Sorry - I posted it in the Excel General section accidentally)

Hi all (warning novice approaching)....

I'm writing VBE code and I want to find cells in column A where the text
entered does not start with 5 spacebars eg:

Alligators aren't always aggressive...
Blue bulls blow big...

Great grapes grow ....
Pink pigs put purple....

In the above example I need the code to pick up the "The quick brown
Fox...." row and the "Great grapes grow..." row and make them bold. The
"Blue Bulls" row and the "Pink pigs" row stay unbold.

I've tried the following code but it makes all active cells bold..
What am I doing wrong? Any help would be greatly appreciated.

.......

Dim i
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next



Thank for your help
BeSmart

Find cells without multiple spacebars and format...
 

were there actually five blanks spaces in those rows?


BeSmart wrote in message
...
(Re-posting. Sorry - I posted it in the Excel General section
accidentally)

Hi all (warning novice approaching)....

I'm writing VBE code and I want to find cells in column A where the text
entered does not start with 5 spacebars eg:

Alligators aren't always aggressive...
Blue bulls blow big...

Great grapes grow ....
Pink pigs put purple....

In the above example I need the code to pick up the "The quick brown
Fox...." row and the "Great grapes grow..." row and make them bold. The
"Blue Bulls" row and the "Pink pigs" row stay unbold.

I've tried the following code but it makes all active cells bold..
What am I doing wrong? Any help would be greatly appreciated.

......

Dim i
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next



Thank for your help
BeSmart

Find cells without multiple spacebars and format...
 

Yes....
As per the example I gave, all heading rows have no spaces before them, and
the products rows that follow them have 5 spaces before the product title.

I need to select the heading rows that DON'T have the 5 spaces and bold them.

Thanks
BeSmart


"R.VENKATARAMAN" wrote:

were there actually five blanks spaces in those rows?


BeSmart wrote in message
...
(Re-posting. Sorry - I posted it in the Excel General section
accidentally)

Hi all (warning novice approaching)....

I'm writing VBE code and I want to find cells in column A where the text
entered does not start with 5 spacebars eg:

Alligators aren't always aggressive...
Blue bulls blow big...

Great grapes grow ....
Pink pigs put purple....

In the above example I need the code to pick up the "The quick brown
Fox...." row and the "Great grapes grow..." row and make them bold. The
"Blue Bulls" row and the "Pink pigs" row stay unbold.

I've tried the following code but it makes all active cells bold..
What am I doing wrong? Any help would be greatly appreciated.

......

Dim i
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next



Thank for your help
BeSmart

Find cells without multiple spacebars and format...
 

try adding a "Step -1" to work from the bottom up

Dim i
For i = Cells(Rows.Count, "a").End(xlUp).Row to 1 Step-1
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next


Also - important- when I deleted the spaces in front o your asterisk I only
found 4 spaces in your code. I think the above has 5.


"BeSmart" wrote:

(Re-posting. Sorry - I posted it in the Excel General section accidentally)

Hi all (warning novice approaching)....

I'm writing VBE code and I want to find cells in column A where the text
entered does not start with 5 spacebars eg:

Alligators aren't always aggressive...
Blue bulls blow big...

Great grapes grow ....
Pink pigs put purple....

In the above example I need the code to pick up the "The quick brown
Fox...." row and the "Great grapes grow..." row and make them bold. The
"Blue Bulls" row and the "Pink pigs" row stay unbold.

I've tried the following code but it makes all active cells bold..
What am I doing wrong? Any help would be greatly appreciated.

......

Dim i
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next



Thank for your help
BeSmart

Find cells without multiple spacebars and format...
 

Thanks for that....

I tried the revised code as suggested, and I made sure there were definitely
5 spaces before the asterik in the code.

But I got the same result, ie all rows became bold....

Any other ideas????
Regards
BeSmart



"gocush" wrote:

try adding a "Step -1" to work from the bottom up

Dim i
For i = Cells(Rows.Count, "a").End(xlUp).Row to 1 Step-1
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next


Also - important- when I deleted the spaces in front o your asterisk I only
found 4 spaces in your code. I think the above has 5.


"BeSmart" wrote:

(Re-posting. Sorry - I posted it in the Excel General section accidentally)

Hi all (warning novice approaching)....

I'm writing VBE code and I want to find cells in column A where the text
entered does not start with 5 spacebars eg:

Alligators aren't always aggressive...
Blue bulls blow big...

Great grapes grow ....
Pink pigs put purple....

In the above example I need the code to pick up the "The quick brown
Fox...." row and the "Great grapes grow..." row and make them bold. The
"Blue Bulls" row and the "Pink pigs" row stay unbold.

I've tried the following code but it makes all active cells bold..
What am I doing wrong? Any help would be greatly appreciated.

......

Dim i
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next



Thank for your help
BeSmart

Find cells without multiple spacebars and format...
 

It's this statement that's the problem. Evidently you are thinking of COUNTIF
and SUMIF, which accept wild cards. A direct comparison does not.

If Cells(i, "a").Value < " *" Then

You could write this as

If Cells(i, "A").Value LIKE " *" Then

or

If Left$(Cells(i, "A").Value, 5) = " " Then


On Wed, 26 Jan 2005 21:27:01 -0800, "BeSmart"
wrote:

Thanks for that....

I tried the revised code as suggested, and I made sure there were definitely
5 spaces before the asterik in the code.

But I got the same result, ie all rows became bold....

Any other ideas????
Regards
BeSmart



"gocush" wrote:

try adding a "Step -1" to work from the bottom up

Dim i
For i = Cells(Rows.Count, "a").End(xlUp).Row to 1 Step-1
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next


Also - important- when I deleted the spaces in front o your asterisk I only
found 4 spaces in your code. I think the above has 5.


"BeSmart" wrote:

(Re-posting. Sorry - I posted it in the Excel General section
accidentally)

Hi all (warning novice approaching)....

I'm writing VBE code and I want to find cells in column A where the text
entered does not start with 5 spacebars eg:

Alligators aren't always aggressive...
Blue bulls blow big...

Great grapes grow ....
Pink pigs put purple....

In the above example I need the code to pick up the "The quick brown
Fox...." row and the "Great grapes grow..." row and make them bold. The
"Blue Bulls" row and the "Pink pigs" row stay unbold.

I've tried the following code but it makes all active cells bold..
What am I doing wrong? Any help would be greatly appreciated.

......

Dim i
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next



Thank for your help
BeSmart

Find cells without multiple spacebars and format...
 

Thanks Myrna
I understand your response.
However, I want to select those cells that are NOT "Like" " *"

How do you suggest I right that?


While we're here, I'm also trying to do a simple copy and paste special for
a range but it's got a problem with the paste function. I know I've done
something wrong but I can't work out how to fix it. Could you take a look
for me.

Current Code to select range, copy cells, paste values only:
.....
Range("B7:B65536").Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
Range("B8").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A6").Select
.......

I think I have a problem is with how I'm selecting the range (which will be
different each time we run the macro) and as a result the
"ActiveSheet.PasteSpecial..." line is not working correctly.

Any advice would be greatly appreciated.

Thanks very much for your help
BeSmart

"Myrna Larson" wrote:

It's this statement that's the problem. Evidently you are thinking of COUNTIF
and SUMIF, which accept wild cards. A direct comparison does not.

If Cells(i, "a").Value < " *" Then

You could write this as

If Cells(i, "A").Value LIKE " *" Then

or

If Left$(Cells(i, "A").Value, 5) = " " Then


On Wed, 26 Jan 2005 21:27:01 -0800, "BeSmart"
wrote:

Thanks for that....

I tried the revised code as suggested, and I made sure there were definitely
5 spaces before the asterik in the code.

But I got the same result, ie all rows became bold....

Any other ideas????
Regards
BeSmart



"gocush" wrote:

try adding a "Step -1" to work from the bottom up

Dim i
For i = Cells(Rows.Count, "a").End(xlUp).Row to 1 Step-1
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next


Also - important- when I deleted the spaces in front o your asterisk I only
found 4 spaces in your code. I think the above has 5.


"BeSmart" wrote:

(Re-posting. Sorry - I posted it in the Excel General section
accidentally)

Hi all (warning novice approaching)....

I'm writing VBE code and I want to find cells in column A where the text
entered does not start with 5 spacebars eg:

Alligators aren't always aggressive...
Blue bulls blow big...

Great grapes grow ....
Pink pigs put purple....

In the above example I need the code to pick up the "The quick brown
Fox...." row and the "Great grapes grow..." row and make them bold. The
"Blue Bulls" row and the "Pink pigs" row stay unbold.

I've tried the following code but it makes all active cells bold..
What am I doing wrong? Any help would be greatly appreciated.

......

Dim i
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
If Cells(i, "a").Value < " *" Then
Cells(i, "a").Font.FontStyle = "Bold"
End If
Next



Thank for your help
BeSmart