Regular Next witout For Errors; am I missing something?

[steveh8204]

A few times I've had this error when it always seems the case that I have a For statement. To make sure I'm not missing something I've stripped the code down to the bare bones but still getting it with the following code:

Sub mysearch()
Dim v As String, i As Integer


For i = 1 To 10
    v = Cells(i, 1)
If v = "Steve" Then
Cells(i, 1).Font = Bold

Next i
    
End Sub

How is there a no For statement error?

I'm relatively new to VBA and while I've had plenty of joy learning, struggling with the basics like this can be disheartening.

Is there something obvious I'm regularly likely to be missing that is causing this error.

If I put an Endif in before the Next it gets worse, I get a Runtime 438 error "Object doesnt support this property or method".

 

[Fluff]

You're missing the End If, but also you are setting the font to bold incorrectly, it should be

Sub mysearch()
Dim v As String, i As Integer


For i = 1 To 10
   v = Cells(i, 1)
   If v = "Steve" Then
      Cells(i, 1).Font.Bold = True
   End If
Next i
    
End Sub

 

[steveh8204]

Thanks, seems to have sorted it. Thought I'd set the Bold correctly earlier but must have got mixed up cutting and changing the code.

Root cause seems to be the EndIf statement. Do you always have to use this as I'm surely I regularly don't and get away with it.

Thanks again.

 

[Fluff]

You need the End If whenever the If statement is on more than one line. So you could use this instead

For i = 1 To 10
   v = Cells(i, 1)
   If v = "Steve" Then Cells(i, 1).Font.Bold = True
Next i

 

[Joe4]

Root cause seems to be the EndIf statement. Do you always have to use this as I'm surely I regularly don't and get away with it.

Rule is this:
- If you put the ENTIRE If ... Then on a single line of code, you do not need an End If, i.e.

[B]If [/B][I]some condition[/I] [B]Then[/B] [I]do something[/I]

- If you split up the If into multiple lines (many times you may want to do more than one thing), then you need an End If, i.e.

[B]If [/B][I]some condition[/I] [B]Then[/B] 
[I]    do something
[/I]    [I]do something[/I]
[B]End If[/B]

 

[Scott Huish]

You don't really need the IF here at all really, your comparison returns a TRUE/FALSE result which can be used in your .Font.Bold line:

Sub mysearch()
Dim i As Integer
For i = 1 To 10
    Cells(i, 1).Font.Bold = (Cells(i, 1) = "Steve")
Next i
End Sub

 

[steveh8204]

Ah, that makes sense (about the one line thing) thanks for the replies.

Thanks too Scott for that little bit of code, handy to know.

 

[Rick Rothstein]

You don't really need the IF here at all really, ...

Actually, the OP doesn't even need a loop...

[table="width: 500"]
[tr]
	[td]Sub MakeSteveBold()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Font.Bold = True
  Range("A1:A10").Replace "Steve", "", SearchFormat:=False, ReplaceFormat:=True
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[Scott Huish]

Good point.

Very cool!

 

[steveh8204]

Nice bit of code, just need a way to make "Steve" into a variable and the range Dynamic though as that's where I was heading.

Thanks though, much appreciated.