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" wrote in message
...
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" napisa³ w
wiadomo¶ci ...
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.