Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
I have a spreadheet that has a column, titled "Running Totals". This
column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
Select all columns to be updated
EditCopy EditPaste special check Values HTH -- AP "GregR" a écrit dans le message de news: ... I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
this may do it all for you since it copies the last column to the next
column and then converts the formulas in the previous last column to values Sub valuelastcoltonest() lc = Cells(1, Columns.Count).End(xlToLeft).Column Columns(lc).Copy Columns(lc + 1) Columns(lc).Value = Columns(lc).Value End Sub -- Don Guillett SalesAid Software "GregR" wrote in message ups.com... I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
Use "Paste Special" and select "Values". To convert this to VBA, record it in the Macro Recorder and copy and paste the code produced. -- MartinShort Software Tester ------------------------------------------------------------------------ MartinShort's Profile: http://www.excelforum.com/member.php...o&userid=22034 View this thread: http://www.excelforum.com/showthread...hreadid=548633 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
try something like this
Sub example() Dim first As Integer Worksheets("Sheet3").Activate Range("A1").Select first = 1 Do While ActiveCell.Offset(0, first).Value < "Running Totals" first = first + 1 Loop Columns(first).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select End Sub you would have to run this every month. this assumes that the sheet is called "sheet3" so you would have to change that appropriately. It also assumes the heading is in row 1 so if it is not, you have to change the 0 in Do While ActiveCell.Offset(0, first).Value < "Running Totals" to 1 or 2 or whatever depending on which row in is in. hope that helps "GregR" wrote: I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
Don, the running total column is not the last column. I need something
to find the running total column, count the columns to the left of it, however omit column "A", and convert any formlas into values. TIA Greg Don Guillett wrote: this may do it all for you since it copies the last column to the next column and then converts the formulas in the previous last column to values Sub valuelastcoltonest() lc = Cells(1, Columns.Count).End(xlToLeft).Column Columns(lc).Copy Columns(lc + 1) Columns(lc).Value = Columns(lc).Value End Sub -- Don Guillett SalesAid Software "GregR" wrote in message ups.com... I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
Sean, if I wanted to skip Column "A", would I select B1. Your other
assumptions are correct, except it is always the first sheet in the workbook. Greg Sean wrote: try something like this Sub example() Dim first As Integer Worksheets("Sheet3").Activate Range("A1").Select first = 1 Do While ActiveCell.Offset(0, first).Value < "Running Totals" first = first + 1 Loop Columns(first).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select End Sub you would have to run this every month. this assumes that the sheet is called "sheet3" so you would have to change that appropriately. It also assumes the heading is in row 1 so if it is not, you have to change the 0 in Do While ActiveCell.Offset(0, first).Value < "Running Totals" to 1 or 2 or whatever depending on which row in is in. hope that helps "GregR" wrote: I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
Yes and also change Columns(first).Select to Columns(first + 1).Select.
note also that is only changes the immediate left column. I read in one of your replies that you wanted to count columns to the left. Does that mean you want to change all columns to the left except column A? "GregR" wrote: Sean, if I wanted to skip Column "A", would I select B1. Your other assumptions are correct, except it is always the first sheet in the workbook. Greg Sean wrote: try something like this Sub example() Dim first As Integer Worksheets("Sheet3").Activate Range("A1").Select first = 1 Do While ActiveCell.Offset(0, first).Value < "Running Totals" first = first + 1 Loop Columns(first).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select End Sub you would have to run this every month. this assumes that the sheet is called "sheet3" so you would have to change that appropriately. It also assumes the heading is in row 1 so if it is not, you have to change the 0 in Do While ActiveCell.Offset(0, first).Value < "Running Totals" to 1 or 2 or whatever depending on which row in is in. hope that helps "GregR" wrote: I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
Sean, yes that's right
Greg Sean wrote: Yes and also change Columns(first).Select to Columns(first + 1).Select. note also that is only changes the immediate left column. I read in one of your replies that you wanted to count columns to the left. Does that mean you want to change all columns to the left except column A? "GregR" wrote: Sean, if I wanted to skip Column "A", would I select B1. Your other assumptions are correct, except it is always the first sheet in the workbook. Greg Sean wrote: try something like this Sub example() Dim first As Integer Worksheets("Sheet3").Activate Range("A1").Select first = 1 Do While ActiveCell.Offset(0, first).Value < "Running Totals" first = first + 1 Loop Columns(first).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select End Sub you would have to run this every month. this assumes that the sheet is called "sheet3" so you would have to change that appropriately. It also assumes the heading is in row 1 so if it is not, you have to change the 0 in Do While ActiveCell.Offset(0, first).Value < "Running Totals" to 1 or 2 or whatever depending on which row in is in. hope that helps "GregR" wrote: I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
Don, thank you very much
Greg Don Guillett wrote: try Sub findrunningtotalcol() mc = Rows(1).Find("Running Total").Column - 1 MsgBox mc Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value = _ Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value End Sub -- Don Guillett SalesAid Software "GregR" wrote in message oups.com... Don, the running total column is not the last column. I need something to find the running total column, count the columns to the left of it, however omit column "A", and convert any formlas into values. TIA Greg Don Guillett wrote: this may do it all for you since it copies the last column to the next column and then converts the formulas in the previous last column to values Sub valuelastcoltonest() lc = Cells(1, Columns.Count).End(xlToLeft).Column Columns(lc).Copy Columns(lc + 1) Columns(lc).Value = Columns(lc).Value End Sub -- Don Guillett SalesAid Software "GregR" wrote in message ups.com... I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Values from formulas
glad it helped
-- Don Guillett SalesAid Software "GregR" wrote in message ups.com... Don, thank you very much Greg Don Guillett wrote: try Sub findrunningtotalcol() mc = Rows(1).Find("Running Total").Column - 1 MsgBox mc Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value = _ Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value End Sub -- Don Guillett SalesAid Software "GregR" wrote in message oups.com... Don, the running total column is not the last column. I need something to find the running total column, count the columns to the left of it, however omit column "A", and convert any formlas into values. TIA Greg Don Guillett wrote: this may do it all for you since it copies the last column to the next column and then converts the formulas in the previous last column to values Sub valuelastcoltonest() lc = Cells(1, Columns.Count).End(xlToLeft).Column Columns(lc).Copy Columns(lc + 1) Columns(lc).Value = Columns(lc).Value End Sub -- Don Guillett SalesAid Software "GregR" wrote in message ups.com... I have a spreadheet that has a column, titled "Running Totals". This column moves each month after the first of the month. When I move that column, I want to automatically convert any formulas to the left of the column into their numeric value. How? TIA Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Formulas and Being Able to Make Changes | Excel Discussion (Misc queries) | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
How can i make formulas automatic (F9) | Setting up and Configuration of Excel | |||
Can I make formulas more flexible? | Excel Discussion (Misc queries) | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming |