Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro copy and paste
I am just learning how to use Macros and I have a process I do quite a bit
that I would like to have a macro created for. I use copy - paste special/multiply often when needing to change number formats. What I would like to do is to create a macro that will multiply a cell or reange of cells by 1 (formateed as a number with no decimals or even just as general, how can I accomplish this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro copy and paste
I'm just trying to guess what exactly you want to achieve and if i got
that right - i think you want to apply format 'General' to a specific range of cells and want the values in those cells to show up according to the 'General' format. So, in that case you could have something like this: Sub FormatMyRange() Dim oneCell As Range Dim myRange As Range Set myRange = Range("A1", "A8")'This is your range to be formated - amend as necessary For Each oneCell In myRange With oneCell .NumberFormat = "General"'Sets the cell's format to General .Value = .Value'Reenters the cell's value into itself so that it would asume the 'new' format End With Next oneCell End Sub On 4 Dec, 12:37, Jan S wrote: I am just learning how to use Macros and I have a process I do quite a bit that I would like to have a macro created for. *I use copy - paste special/multiply often when needing to change number formats. *What I would like to do is to create a macro that will multiply a cell or reange of cells by 1 (formateed as a number with no decimals or even just as general, how can I accomplish this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro copy and paste
Because of the way what you're doing works, it's difficult to learn by
recording a macro. But the code below gives the same result. You can experiment with recording macros to change the .NumberFormat part to get your specific numeric format if 'General' doesn't hack the mission: Simply select the range of cells to be converted and then run the macro. Sub NumericTextToNumber() Dim anyCell As Range Application.ScreenUpdating = False On Error Resume Next For Each anyCell In Selection anyCell.NumberFormat = "General" 'for numbers without decimal place 'anyCell.NumberFormat = "0" anyCell = anyCell * 1 Next If Err < 0 Then Err.Clear End If On Error GoTo 0 End Sub "Jan S" wrote: I am just learning how to use Macros and I have a process I do quite a bit that I would like to have a macro created for. I use copy - paste special/multiply often when needing to change number formats. What I would like to do is to create a macro that will multiply a cell or reange of cells by 1 (formateed as a number with no decimals or even just as general, how can I accomplish this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro copy and paste
I use this often
Sub fixmynums() Application.ScreenUpdating = False 'lr = Cells.SpecialCells(xlCellTypeLastCell).Row On Error Resume Next For Each c In Selection 'Range("a1:q" & lr) If Trim(Len(c)) 0 And c.HasFormula = False Then c.NumberFormat = "General" c.Value = CDbl(c) End If Next Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Jan S" wrote in message ... I am just learning how to use Macros and I have a process I do quite a bit that I would like to have a macro created for. I use copy - paste special/multiply often when needing to change number formats. What I would like to do is to create a macro that will multiply a cell or reange of cells by 1 (formateed as a number with no decimals or even just as general, how can I accomplish this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming |