Hello - I've got a huge calendar of bookings and the hours are all
messed up from our database! I've got to manually fix several hundred
cells, basically changing most of them from "40" to "55", or "32" to
"44", "24" to "33", and a few others. I have Excel 2000.

I'd like to write a macro and I have a very small start. Any advice or
code would be unbelievably appreciated. Ideally I'd like to select
specific columns of cells and then run this macro pseudocode on them:

------------------------------
For each *selected* cell:
Does the cell directly to my left contain(":")?
If Yes --
Does my cell contain("40")?
If Yes --
Change my cell to ("55")
Does my cell contain("32")?
If Yes --
Change my cell to ("44")
Does my cell contain("24")?
If Yes --
Change my cell to "(33")
Does my cell contain("16")?
If Yes --
Change my cell to ("22")
Does my cell contain("8")?
If Yes --
Change my cell to ("11")
Otherwise... do nothing
If No --
do nothing

---------------------------------------------

thank you, thank you, thank you.
Jennifer.

Jennifer,

Select the cells that need fixing, and run this macro:

Sub MacroForJennifer()
Selection.Replace What:="40", Replacement:="55", LookAt:=xlWhole
Selection.Replace What:="32", Replacement:="44", LookAt:=xlWhole
Selection.Replace What:="24", Replacement:="33", LookAt:=xlWhole
Selection.Replace What:="16", Replacement:="22", LookAt:=xlWhole
Selection.Replace What:="8", Replacement:="11", LookAt:=xlWhole
End Sub

HTH,
Bernie
MS Excel MVP

"Jennifer" wrote in message
om...
Hello - I've got a huge calendar of bookings and the hours are all
messed up from our database! I've got to manually fix several hundred
cells, basically changing most of them from "40" to "55", or "32" to
"44", "24" to "33", and a few others. I have Excel 2000.

I'd like to write a macro and I have a very small start. Any advice or
code would be unbelievably appreciated. Ideally I'd like to select
specific columns of cells and then run this macro pseudocode on them:

------------------------------
For each *selected* cell:
Does the cell directly to my left contain(":")?
If Yes --
Does my cell contain("40")?
If Yes --
Change my cell to ("55")
Does my cell contain("32")?
If Yes --
Change my cell to ("44")
Does my cell contain("24")?
If Yes --
Change my cell to "(33")
Does my cell contain("16")?
If Yes --
Change my cell to ("22")
Does my cell contain("8")?
If Yes --
Change my cell to ("11")
Otherwise... do nothing
If No --
do nothing

---------------------------------------------

thank you, thank you, thank you.
Jennifer.

Bernie,

Thank you so much. That was the most helpful so far. I wasn't very
clear, but I am looking for something more specific, I don't know how
hard or easy it is!

For each selected cell,
I only want to change "40" to "55" (for example), if the *cell
directly to its left* contains a colon (":").

For example, the following 12 cells (4x3) would go from this:
-----------------------------------------------------
total:hours 40 40 min:hour 32
total:min 24 min:hour 8 24
32 min:hour 55 normal 40
-----------------------------------------------------
TO THIS:
-----------------------------------------------------
total:hours 55 40 min:hour 44
total:min 33 min:hour 11 24
32 min:hour 55 normal 40
-----------------------------------------------------
The first cell "total:hours" would cause "40" to turn into "55",
because "total:hours" contains a colon. Similarly, the very last cell
"40" would not change to "55" because "normal" does not contain a
colon.

I know I am asking a lot!! This would save me hours upon hours of data
entry if I can get it working!

Thank you so much,
Jennifer.

Jennifer,

You can use the same macro, but you simply need to filter your data first
and select only visible cells.

Apply the filter using Data | Filter | Autofilter, then for the column to
the left of your numbers, click the dropdown at the top of the column and
choose (Custom...). Select 'contains' from the drop down, and enter a colon
into the box to the right. Then select your cells with the numbers, and
choose Edit | Go To... SpecialCells | Visible Cells only, and then run the
macro.

The above steps can be included in the macro if you are going to do this a
lot: the sample code below is based on filtering column A, and selecting
visible cells in column B. I simply added those steps to the top of the
macro from before.

HTH,
Bernie
MS Excel MVP

Sub MacroForJennifer()
Columns("A:B").AutoFilter Field:=1, Criteria1:="=*:*"
With Columns("B:B").SpecialCells(xlCellTypeVisible)
.Replace What:="40", Replacement:="55", LookAt:=xlWhole
.Replace What:="32", Replacement:="44", LookAt:=xlWhole
.Replace What:="24", Replacement:="33", LookAt:=xlWhole
.Replace What:="16", Replacement:="22", LookAt:=xlWhole
.Replace What:="8", Replacement:="11", LookAt:=xlWhole
End With
Columns("A:B").AutoFilter
End Sub




"Jennifer" wrote in message
om...
Bernie,

Thank you so much. That was the most helpful so far. I wasn't very
clear, but I am looking for something more specific, I don't know how
hard or easy it is!

For each selected cell,
I only want to change "40" to "55" (for example), if the *cell
directly to its left* contains a colon (":").

For example, the following 12 cells (4x3) would go from this:
-----------------------------------------------------
total:hours 40 40 min:hour 32
total:min 24 min:hour 8 24
32 min:hour 55 normal 40
-----------------------------------------------------
TO THIS:
-----------------------------------------------------
total:hours 55 40 min:hour 44
total:min 33 min:hour 11 24
32 min:hour 55 normal 40
-----------------------------------------------------
The first cell "total:hours" would cause "40" to turn into "55",
because "total:hours" contains a colon. Similarly, the very last cell
"40" would not change to "55" because "normal" does not contain a
colon.

I know I am asking a lot!! This would save me hours upon hours of data
entry if I can get it working!

Thank you so much,
Jennifer.

Jenifer

Try This

Sub Test()
Dim c As Variant
For Each c In Selection
If c.Offset(0, -1) = ":" Or IsDate(c.Offset(0, -1))
Then
If c = 40 Then
c.Value = 55
ElseIf c = 32 Then
c.Value = 44
ElseIf c = 24 Then
c.Value = 33
ElseIf c = 16 Then
c.Value = 22
ElseIf c = 8 Then
c.Value = 11
End If
End If
Next c

End Sub


Regards
Peter

-----Original Message-----
Hello - I've got a huge calendar of bookings and the
hours are all
messed up from our database! I've got to manually fix
several hundred
cells, basically changing most of them from "40" to "55",
or "32" to
"44", "24" to "33", and a few others. I have Excel 2000.

I'd like to write a macro and I have a very small start.
Any advice or
code would be unbelievably appreciated. Ideally I'd like
to select
specific columns of cells and then run this macro
pseudocode on them:

------------------------------
For each *selected* cell:
Does the cell directly to my left contain(":")?
If Yes --
Does my cell contain("40")?
If Yes --
Change my cell to ("55")
Does my cell contain("32")?
If Yes --
Change my cell to ("44")
Does my cell contain("24")?
If Yes --
Change my cell to "(33")
Does my cell contain("16")?
If Yes --
Change my cell to ("22")
Does my cell contain("8")?
If Yes --
Change my cell to ("11")
Otherwise... do nothing
If No --
do nothing

---------------------------------------------

thank you, thank you, thank you.
Jennifer.
.

Hi Peter and Bernie,
thank you! I posted a reply to Bernie 5 or 6 hours ago, so hopefully
it will post soon. It has my dream code a little better articulated.

As soon as I get to work tomorrow, I will try out Peter's code. I feel
much closer to getting it working, though.

Peter, in this line of code:

If c.Offset(0, -1) = ":"

How would I check to see if any part of the cell contained a colon?
That is, not equal to ":" but contained ":"? For example, "nice:guys"
and "thank:you" would also pass the test and continue on to the "then"
statement?

I am so excited! Thanks again,
Jennifer.

Jennifer,

Change

If c.Offset(0, -1) = ":"

to

If InStr(c.Offset(0, -1).Value, ":" ) 0

HTH,
Bernie
MS Excel MVP

"Jennifer" wrote in message
om...
Hi Peter and Bernie,
thank you! I posted a reply to Bernie 5 or 6 hours ago, so hopefully
it will post soon. It has my dream code a little better articulated.

As soon as I get to work tomorrow, I will try out Peter's code. I feel
much closer to getting it working, though.

Peter, in this line of code:

If c.Offset(0, -1) = ":"

How would I check to see if any part of the cell contained a colon?
That is, not equal to ":" but contained ":"? For example, "nice:guys"
and "thank:you" would also pass the test and continue on to the "then"
statement?

I am so excited! Thanks again,
Jennifer.

Bernie and Peter,
Thank you so much!! I have never been so excited about a macro in my
entire life. You two literally saved me hours and hours of manual data
entry.

And now I am "THE WOMAN" at work!!

Thanks again,
Jennifer.