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:

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>

It's probably failing because of the stupid hungarian notation. :C

I don't see anything obvious, but what happens if you simplify it - remove everything except For Each and Next parts, then put Cell.Value = "XXX" in the loop

I'm not sure what you mean by "stupid hungarian notation", it just looks like crappy VBA to me. But then I haven't programmed VBA for over 10 years. So, I did this, and still no joy:

Sub SearchNReplace1()
    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

    For Each rCell In Selection
        sTemp = rCell.Value
        iLenInitial = Len("=")
        iLenFinal = Len("/7")
        If Left(sTemp, iLenInitial) = "=" And Right(sTemp, iLenFinal) = "/7" Then
            sTemp = Mid(sTemp, iLenInitial + 1)
            sTemp = Left(sTemp, Len(sTemp) - iLenFinal)
            sTemp = "=CEILING(" & sTemp & "/7,1)"
            rCell.Value = sTemp
        End If
    Next
    Set rCell = Nothing
End Sub

The bit where there's a letter at the start of every variable. It's not necessary and is a fucking stupid way of writing code (in any language).

Anyway, what I was saying was try this:

Sub SearchNReplace1()
    Dim CurrentCell As Range

    For Each CurrentCell In Selection
        CurrentCell.Value = "XXX"
    Next
End Sub

(making sure you have a backup first, just in case)

If that doesn't work then you've got something small to work with, if it does work then add back lines to reproduce original behaviour until it breaks again.

Can you post a snippet of your spreadsheet with fake data?

Thanks Dan, and Peter, but I decided that since this is just a means to an end I'd just work around it. I replaced the "=" and "/7" with blanks (just leaving the days) and then carried out the calculations in new columns referencing the cells. I can hide the old columns to make t look less crap.