Excel copy/paste value issues

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
    ActiveWindow.SmallScroll Down:=-6
    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?

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.

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:

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
End Sub

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