Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting specific values and replacing in set column | Excel Discussion (Misc queries) | |||
Replacing Linked Cell Values w/ Current Values | Excel Worksheet Functions | |||
AFTER REPLACING FORMULA WITH VALUES, HOW DO I ADD THE COLUMN? | Excel Discussion (Misc queries) | |||
Replacing Values in Cells Via Formula | Excel Worksheet Functions | |||
Calculating values to column D with formula based on values column A | Excel Programming |