Can anyone help me with this problem? I have a whole load of cells for which I need to change a days to weeks conversion from "=days/7" to "=CEILING(days/7,1)".
I found
this tutorial, with this code:
VBA code:
Sub SearchNReplace2()
Dim sFindInitial As String
Dim sReplaceInitial As String
Dim iLenInitial As Integer
Dim sFindFinal As String
Dim sReplaceFinal As String
Dim iLenFinal As Integer
Dim sTemp As String
Dim rCell As Range
sFindInitial = "="
sReplaceInitial = "=CEILING("
sFindFinal = "7"
sReplaceFinal = "7,1)"
For Each rCell In Selection
sTemp = rCell.Value
iLenInitial = Len(sFindInitial)
iLenFinal = Len(sFindFinal)
If Left(sTemp, iLenInitial) = sFindInitial And Right(sTemp, iLenFinal) = sFindFinal Then
sTemp = Mid(sTemp, iLenInitial + 1)
sTemp = Left(sTemp, Len(sTemp) - iLenFinal)
sTemp = sReplaceInitial & sTemp & sReplaceFinal
rCell.Value = sTemp
End If
Next
Set rCell = Nothing
End Sub
But, when I select a cell and run the macro, nothing happens (yes, this is a .xlsm file). When I select the cell and step into the macro, it highlights the subroutine alright, but nothing occurs after continuing. (I didn't originally have the backslash in the final find/replace, but its absence makes no difference, and I need it there really).
<penelope pitstop> HAYLP! HAYLP! </pp>