I got this wonderfully concise bit of code, and for a majority of what I need
it is flawless. Then I was handed another task and thought about reusing this
code. Problem is, I am not that bright, and therefore I am unable to get it
to function as I want it too.

Dim s As String
Dim r As Range
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
For Each r In ActiveSheet.UsedRange
s = r.Value
If InStr(s, ":") > 0 Then
r.Value = "0:" & s
r.NumberFormat = "[h]:mm:ss"
End If
Next
Next

In the end I need it to go through each sheet in the workbook and convert
anything with a : in the cell. That part works well enough, what I can not
seem to get to work is the switching between the different sheets.

Additionally, some of the cells are formulas, and I want to know if there is
away that if the code sees a formula it will not try and change the cell at
all, except for the .NumberFormat.

So, long and short of it.

1) Scan each sheet in a workbook. If the cell contains a ‘:’ and does not
already have a formula, add ‘0:’ to the beginning of the string.

So, is this something that can be done?

Thank you in advance for all your help, you have been invaluable.

There is an wonderfully written addin called FlexFind that can do this. I
would recommend that over using the code in your macro.
http://office.microsoft.com/en-gb/ma...544451033.aspx



"Mahnian" <(E-Mail Removed)> wrote in message
news:37DEB96D-2E69-4C7A-A7F7-(E-Mail Removed)...
>I got this wonderfully concise bit of code, and for a majority of what I
>need
> it is flawless. Then I was handed another task and thought about reusing
> this
> code. Problem is, I am not that bright, and therefore I am unable to get
> it
> to function as I want it too.
>
> Dim s As String
> Dim r As Range
> Dim sht As Worksheet
> For Each sht In ActiveWorkbook.Sheets
> For Each r In ActiveSheet.UsedRange
> s = r.Value
> If InStr(s, ":") > 0 Then
> r.Value = "0:" & s
> r.NumberFormat = "[h]:mm:ss"
> End If
> Next
> Next
>
> In the end I need it to go through each sheet in the workbook and convert
> anything with a : in the cell. That part works well enough, what I can not
> seem to get to work is the switching between the different sheets.
>
> Additionally, some of the cells are formulas, and I want to know if there
> is
> away that if the code sees a formula it will not try and change the cell
> at
> all, except for the .NumberFormat.
>
> So, long and short of it.
>
> 1) Scan each sheet in a workbook. If the cell contains a ':' and does not
> already have a formula, add '0:' to the beginning of the string.
>
> So, is this something that can be done?
>
> Thank you in advance for all your help, you have been invaluable.

I think, it can be useful range's property "HasFormula", e.g.

Dim s As String
Dim r As Range
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
For Each r In ActiveSheet.UsedRange
s = r.Value
If InStr(s, ":") > 0 and r.HasFormula=false Then
r.Value = "0:" & s
r.NumberFormat = "[h]:mm:ss"
End If
Next
Next

Regards
Mariusz

U¿ytkownik "Mahnian" <(E-Mail Removed)> napisa³ w
wiadomo¶ci news:37DEB96D-2E69-4C7A-A7F7-(E-Mail Removed)...
>I got this wonderfully concise bit of code, and for a majority of what I
>need
> it is flawless. Then I was handed another task and thought about reusing
> this
> code. Problem is, I am not that bright, and therefore I am unable to get
> it
> to function as I want it too.
>
> Dim s As String
> Dim r As Range
> Dim sht As Worksheet
> For Each sht In ActiveWorkbook.Sheets
> For Each r In ActiveSheet.UsedRange
> s = r.Value
> If InStr(s, ":") > 0 Then
> r.Value = "0:" & s
> r.NumberFormat = "[h]:mm:ss"
> End If
> Next
> Next
>
> In the end I need it to go through each sheet in the workbook and convert
> anything with a : in the cell. That part works well enough, what I can not
> seem to get to work is the switching between the different sheets.
>
> Additionally, some of the cells are formulas, and I want to know if there
> is
> away that if the code sees a formula it will not try and change the cell
> at
> all, except for the .NumberFormat.
>
> So, long and short of it.
>
> 1) Scan each sheet in a workbook. If the cell contains a ':' and does not
> already have a formula, add '0:' to the beginning of the string.
>
> So, is this something that can be done?
>
> Thank you in advance for all your help, you have been invaluable.

You can give this a try. I didn't test it but it
should run OK. If not, just repost.
Sub FmtTime()
Dim s As String
Dim r As Range
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
For Each r In sht.UsedRange
If r.HasFormula = False Then
s = r.Value
If InStr(s, ":") > 0 Then
r.Value = "0:" & s
r.NumberFormat = "[h]:mm:ss"
End If
End If
Next
Next
End Sub

"Mahnian" wrote:

> I got this wonderfully concise bit of code, and for a majority of what I need
> it is flawless. Then I was handed another task and thought about reusing this
> code. Problem is, I am not that bright, and therefore I am unable to get it
> to function as I want it too.
>
> Dim s As String
> Dim r As Range
> Dim sht As Worksheet
> For Each sht In ActiveWorkbook.Sheets
> For Each r In ActiveSheet.UsedRange
> s = r.Value
> If InStr(s, ":") > 0 Then
> r.Value = "0:" & s
> r.NumberFormat = "[h]:mm:ss"
> End If
> Next
> Next
>
> In the end I need it to go through each sheet in the workbook and convert
> anything with a : in the cell. That part works well enough, what I can not
> seem to get to work is the switching between the different sheets.
>
> Additionally, some of the cells are formulas, and I want to know if there is
> away that if the code sees a formula it will not try and change the cell at
> all, except for the .NumberFormat.
>
> So, long and short of it.
>
> 1) Scan each sheet in a workbook. If the cell contains a ‘:’ and does not
> already have a formula, add ‘0:’ to the beginning of the string.
>
> So, is this something that can be done?
>
> Thank you in advance for all your help, you have been invaluable.