Change contents of a cell based on cell contents.
 

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.

Change contents of a cell based on cell contents.
 

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.

Change contents of a cell based on cell contents.
 

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.

Change contents of a cell based on cell contents.
 

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.