macro copy and paste

  • Thread starter Thread starter Jan S
  • Start date Start date
J

Jan S

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?
 
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
 
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
 
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
 
Back
Top