Replacing text

[liampog]

Hi

I have a range which has empty cells and either numbers or a piece of text.

Currently, I use a For/Next loop to go through each cell one-by-one but it takes a while to do this as the "Rota" range it's looking at is 7 columns by 165 rows.

                            For Each ShiftCell In Range("Rota")
                                
                                If ShiftCell <> "" Then
    
                                    If IsNumeric(ShiftCell) Then
                                        
                                        ShiftCell.Value = "HOL-" & ShiftCell
                                    
                                    Else
                                        
                                        If ShiftCell = "O" Then ShiftCell.Value = "OFF"
                                    
                                    End If
                                
                                End If

                            Next ShiftCell

Is there a faster and more efficient way of doing this?

Thanks
Liam

 

[Fluff]

How about

Sub liampog()
   With Range("Rota")
      .Value = Evaluate(Replace("If(isnumber(@),""HOL-""&@,if(@=""O"",""Off"",If(@="""","""",@)))", "@", .Address))
   End With
End Sub

 

[liampog]

Hi Fluff

That resulted in erroneous data.

I am running this code from a separate workbook and I believe I've referenced it correctly but it's replacing data from the original workbook (where the code is run from).

With Workbooks(Rota_Filename).Sheets("Rota")
   .Range("Rota").Value = Evaluate(Replace("If(isnumber(@),""HOL-""&@,if(@=""O"",""OFF"",If(@="""","""",@)))", "@", .Range("Rota").Address))
End With

Any ideas?

 

[Fluff]

In that case try

With Workbooks(Rota_Filename).Sheets("Rota")
  .Value = .Evaluate(Replace("If(isnumber(@),""HOL-""&@,if(@=""O"",""OFF"",If(@="""","""",@)))", "@", .Address))
End With

 

[liampog]

That works perfectly!

Thanks so much and a MUCH quicker way of doing it!

 

[Fluff]

You're welcome & thanks for the feedback.