VBA Code Run-time error '1004'

[OilEconomist]

Thanks in advance for your assistance. Why do I get the error "Run-time error '1004': Application-defined or object-defined error" for the following line of code

.Range("B" & i) = "=PROPER(" & CompanyName & ")"

in the following code.

Sub PrprCompNm()

Dim i As Long
Dim CompanyName As String
Dim LastRowIndex As Long

With Sheets("Index")
 LastRowIndex = .Cells.Find(What:="*", After:=.Cells(1), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With

  'Loop - remove "<empty>" and correct names to make sure not in all caps
    For i = 2 To LastRowIndex
     With Sheets("Index")
        CompanyName = .Range("B" & i)
            
            If CompanyName = "<empty>" Then
                .Range("B" & i) = ""
                CompanyName = .Range("B" & i)
            End If
            
            .Range("B" & i) = "=PROPER(" & CompanyName & ")"
            
     End With
    Next i
    
End Sub

 

[DanteAmor]

If CompanyName = "<empty>" Then

What is your intention with that line?
If it is to know if the cell is empty, then it could be like this:

Sub PrprCompNm()
  Dim i As Long
  Dim CompanyName As String
  Dim LastRowIndex As Long
  
  With Sheets("Index")
    LastRowIndex = .Cells.Find(What:="*", After:=.Cells(1), _
             SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  End With

  'Loop - remove "<empty>" and correct names to make sure not in all caps
  For i = 2 To LastRowIndex
    With Sheets("Index")
      CompanyName = .Range("B" & i).Value
      If CompanyName <> "" Then
        .Range("B" & i) = "=PROPER(""" & CompanyName & """)"
      End If
    End With
  Next i
End Sub

 

[6StringJazzer]

If you are populating a formula into the cell, the string companyname has to be in quotes. The error is because your formula is invalid.

.Range("B" & i) = "=PROPER(""" & CompanyName & """)"

 

[OilEconomist]

What is your intention with that line?
If it is to know if the cell is empty, then it could be like this:

Sub PrprCompNm()
  Dim i As Long
  Dim CompanyName As String
  Dim LastRowIndex As Long
 
  With Sheets("Index")
    LastRowIndex = .Cells.Find(What:="*", After:=.Cells(1), _
             SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  End With

  'Loop - remove "<empty>" and correct names to make sure not in all caps
  For i = 2 To LastRowIndex
    With Sheets("Index")
      CompanyName = .Range("B" & i).Value
      If CompanyName <> "" Then
        .Range("B" & i) = "=PROPER(""" & CompanyName & """)"
      End If
    End With
  Next i
End Sub

As always Thank you @DanteAmor, but I am not having issues with that line. I am having issues with the following line:

.Range("B" & i) = "=PROPER(" & CompanyName & ")"

where @6StringJazzer recommended the following which worked:

.Range("B" & i) = "=PROPER("[B]""[/B] & CompanyName & "[B]""[/B])"

 

[OilEconomist]

If you are populating a formula into the cell, the string companyname has to be in quotes. The error is because your formula is invalid.

.Range("B" & i) = "=PROPER(""" & CompanyName & """)"

That's it @6StringJazzer. That fixed it! Thank you for your assistance!

 

[DanteAmor]

but I am not having issues with that line

I understand that you have no problems with that line, only that I like to review all the code and make improvements in all the code. Also in my code I corrected the reported problem, but as an extra plus, I corrected all your code.

 

[OilEconomist]

I understand that you have no problems with that line, only that I like to review all the code and make improvements in all the code. Also in my code I corrected the reported problem, but as an extra plus, I corrected all your code.

Oh I apologize @DanteAmor. I see that. My bad.

 

[6StringJazzer]

I understand that you have no problems with that line, only that I like to review all the code and make improvements in all the code. Also in my code I corrected the reported problem, but as an extra plus, I corrected all your code.

That's nice although it would be helpful to point that out when posting the code.