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".

 

[steveh8204]

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

I'm just looking at this code now and was wondering what is the best way to incorporate additional format changes (colour etc) with the With/End WIth Syntax into it?

I've only recently discovered the With command and still trying to get my head around it. I tried With.Selection.Font but it didn't work.

 

[Scott Huish]

I'm going to use a Range variable instead, but how about this?

Sub mysearch()
Dim i As Range, chk As Boolean
For Each i In Range("A1:A10")
    chk = (i = "Steve")
    If chk Then
        With i.Font
            .Bold = chk
            .Color = vbRed
        End With
    End If
Next
End Sub

 

[Rick Rothstein]

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.

Not sure exactly what you are thinking about when you say you want to make the range "Dynamic". Assuming you might mean you want the code to start at cell A1 and find the last cell in Column A with data, give this a try (where I have also made "Steve" a variable)...

[table="width: 500"]
[tr]
	[td]Sub MakeSteveBold()
  Dim sName As String
  sName = "Steve"
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Font.Bold = True
  Range("A1", Cells(Rows.Count, "A")).Replace sName, "", SearchFormat:=False, ReplaceFormat:=True
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

I'm just looking at this code now and was wondering what is the best way to incorporate additional format changes (colour etc) with the With/End WIth Syntax into it?

Just to give you an idea, here is the above code modified to make the text containing the searched for word (Steve in this case) Bold, Red and Italicized while demonstrating the use of the With and End With keywords...

[table="width: 500"]
[tr]
	[td]Sub MakeSteveBold()
  Dim sName As String
  sName = "Steve"
  Application.ReplaceFormat.Clear
  With Application.ReplaceFormat.Font
    .Bold = True
    .Italic = True
    .Color = vbRed
  End With
  Range("A1", Cells(Rows.Count, "A")).Replace sName, "", SearchFormat:=False, ReplaceFormat:=True
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[steveh8204]

Thanks both. These are both very useful and good to learn.