PDA

View Full Version : Macro to Convert text to Number to last cell in the range



Foncesa
01-17-2016, 02:07 PM
Hi,

How to convert text stored as numbers in Column I and then move to Column B. Column starts from cell I2 to the last cell,
Macro recording by .FormulaR1C1 = "1" and then Selection.PasteSpecial paste:=xlPasteAll, Operation:= xlMultiply, skipblanks:=False, Transpose:= False.

This is done for total range whereas i want only to the last cell where numbers are there.

Thanks.

SamT
01-17-2016, 05:12 PM
Column starts from cell I2 to the last cell... i want only to the last cell where numbers are.

I am confused

Foncesa
01-17-2016, 09:02 PM
Hi,
SamT, this will clear your confusion.


Sub Macro1()
Range("H1").Select
ActiveCell.FormulaR1C1 = "1"
Range("H1").Select
Selection.Copy
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10.5
Columns("J:J").Select
Selection.Cut
Range("B1").Select
Selection.Insert Shift:=xlToRight
End Sub

SamT
01-18-2016, 01:53 AM
This is not a recorded Macro any longer. Now it is a Sub procedure

Sub Sub1()
With Range(Range("I1), Range("I1").End(xlDown))
.NumberFormat = "General"
.Copy
.PasteSpecial Paste:=xlPasteValues
.Cut
End With
Range("B1").Insert Shift:=xlToRight
Columns(2).AutoFit
End Sub
Does this solve the problem? Test by entering a word two rows below the bottom number in column "I". You must leave a blank cell above that cell

Foncesa
01-18-2016, 03:07 AM
Hi,

Thanks SamT half of the problem solved, but still it does not convert the text stored as number to numeric value, i have tried all the options only this multiplyfactor = 1 and the Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False only work to convert it.
Thanks for assistance.

SamT
01-18-2016, 03:22 AM
Select a cell in H, look in the Formula Bar and see if it proceeded by an apostrophe (')

Foncesa
01-18-2016, 03:34 AM
Hi,

SamT, No i don't see any apostrophe (')

SamT
01-18-2016, 03:39 AM
Copy a few cells from Column H and paste them here.

Foncesa
01-18-2016, 10:42 PM
Hi,


Copy a few cells from Column H and paste them here.

Test.xlsm attached.

snb
01-19-2016, 12:59 AM
Sub M_snb()
Columns(2).Resize(, 7).NumberFormat = "General"
Columns(9).SpecialCells(2) = Columns(9).SpecialCells(2).Value
Cells(2, 2).Resize(Columns(9).SpecialCells(2).Count) = Columns(9).SpecialCells(2).Value
End Sub

SamT
01-19-2016, 01:00 AM
WOW, that was hard to figure out. Actually I still don't know why that code doesn't work, but I figured out one that does.
BTW, you had Range(Range("Eye1"), Range("I2") as Range(Range("EyeEye"), Range("I2")

Sub Sub1()
For Each cel In Range(Range("I1"), Range("I2").End(xlDown))
If IsNumeric(cel) Then
cel.Value = cel.Value * 1
cel.NumberFormat = "general"
End If
Next
End Sub

Foncesa
01-19-2016, 01:20 AM
Hi,

Applause, snb & SamT.

Thank you very much.
Thanks.