Replacing column values with formula values
 

Hi,

After a merge operation I have a column, F, which contains
a decimal value. I wish to apply a formula to the value
and then replace the existing cells with the *value* from the
formula.

I want to incorporate this into my VBA and am looking for an
easy way. Help very much appreciated.

--
dman

Replacing column values with formula values
 

I have a solution now. I would like to know if anyone has
suggestions for improvement. Specifically, is it possible
to end up with the following result "in-place" without
first creating a temporary column to do the work, then
deleting it?

Here is my VBA. Note that Col. E contains the date. Col. F
contains either hh:mm:ss or a 5-or-6-digit "fake" replica of
time that my below formula converts to hh:mm:ss. (See a recent
thread in microsoft.public.excel.misc for where that came from.)
My replacement Col. F will combine the two values in one and format
it as hh:mm:ss. (I will later use this column to sort the sheet
chronologically.)

I am making a temporary Col. G to do the calculation and then
paste it back over the current Col. F.

Note, also, that "LastRow" is a function not shown here. I
got it from Ron de Bruin's sheet-merge pages.

'==================
Private Sub TimeFix()

Dim myRgF, myRgG As Range

Const myFormula = "=INDIRECT(""E""&ROW())+IF(INDIRECT(""F""&ROW())1 ,TEXT(INDIRECT(""F""&ROW()),""0\:00\:00"")+0,INDIR ECT(""F""&ROW()))"

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Columns("G:G").Insert Shift:=xlToRight

Set myRgF = Range("F3", Cells(LastRow(ActiveSheet), "F"))
Set myRgG = myRgF.Offset(0, 1)

myRgG.Formula = myFormula
myRgG.Copy
myRgF.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm:ss;@"
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub
'==================

-------------------------
In , Dallman Ross <dman@localhost.
spake thusly:

Hi,

After a merge operation I have a column, F, which contains
a decimal value. I wish to apply a formula to the value
and then replace the existing cells with the *value* from the
formula.

I want to incorporate this into my VBA and am looking for an
easy way. Help very much appreciated.

Replacing column values with formula values
 

Nobody has anything good or bad to say about my VBA? "It's fine"?
"It sucks"? "It could be made better thus and so"? I thought
you guys enjoyed discussing code here? Have I broken some
unspoken rule of the group?

Dallman

------------------------------
In , Dallman Ross <dman@localhost. spake thusly:
I have a solution now. I would like to know if anyone has
suggestions for improvement. Specifically, is it possible
to end up with the following result "in-place" without
first creating a temporary column to do the work, then
deleting it?

Here is my VBA. Note that Col. E contains the date. Col. F
contains either hh:mm:ss or a 5-or-6-digit "fake" replica of
time that my below formula converts to hh:mm:ss. (See a recent
thread in microsoft.public.excel.misc for where that came from.)
My replacement Col. F will combine the two values in one and format
it as hh:mm:ss. (I will later use this column to sort the sheet
chronologically.)

I am making a temporary Col. G to do the calculation and then
paste it back over the current Col. F.

Note, also, that "LastRow" is a function not shown here. I
got it from Ron de Bruin's sheet-merge pages.

'==================
Private Sub TimeFix()

Dim myRgF, myRgG As Range

Const myFormula = "=INDIRECT(""E""&ROW())+IF(INDIRECT(""F""&ROW())1 ,TEXT(INDIRECT(""F""&ROW()),""0\:00\:00"")+0,INDIR ECT(""F""&ROW()))"

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Columns("G:G").Insert Shift:=xlToRight

Set myRgF = Range("F3", Cells(LastRow(ActiveSheet), "F"))
Set myRgG = myRgF.Offset(0, 1)

myRgG.Formula = myFormula
myRgG.Copy
myRgF.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm:ss;@"
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub
'==================

-------------------------
In , Dallman Ross <dman@localhost.
spake thusly:

Hi,

After a merge operation I have a column, F, which contains
a decimal value. I wish to apply a formula to the value
and then replace the existing cells with the *value* from the
formula.

I want to incorporate this into my VBA and am looking for an
easy way. Help very much appreciated.