Excel copy/paste value issues

General Computing Coding and Design
1

Hi Guys.

I’m trying to pull the last 6 numbers out a chassis number by using =RIGHT(D2,6) and then a macro copies the resulting cell value and "Paste Special"s the value into another cell.

The macro is:

Range("H2:H58").Select ' COPIES LAST 6 TO CELL A2 DOWN
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-6
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

The only problem is, that it does not paste it as a number, but as text. This stops a VLOOKUP from identifying the data as a number when comparing it to a list of car VINs I need to find. Even selecting the cells manually and formatting them does not convert the pasted data to numbers. Any ideas how to overcome this please?

2

prob not the proper way… and this is off the top of me head so may not work
try a double action in setting a cell with a numeric value e.g. 1 then doing the copy to it.

3

Had a long think on what you suggested Paul then checked out various things and finally found a solution in the help section. Created a cell with a number 1 in it, copied it then pasted it special into the target cells using the multiply option. Never used that before :slight_smile: Just key-logged it into the macro and set up a button, all works fine. :thumbsup:

4

I have this in an .xlam (.xla) and a button on the office bar for just such occasions:


Sub numberfy()
' Convert the selected range of cells to numbers
  On Error Resume Next
  For Each rCell In Selection
    rCell.Value = rCell.Value * 1
  Next
End Sub
5

Nice one! Cheers Kev. That will come in handy :thumbsup: