I’m tweaking a simple macro to copy a list of numbers contained in an EDD file that are formatted as General; I need to transpose them and paste just the values. Once they’re pasted, I need to apply custom formatting to specific ones by selecting them and doing a second macro. The problem is the numbers all get pasted with a leading apostrophe and the custom format won’t work on them. (It works on all other numbers on the sheet I’ve entered manually, and works when I manually go in and remove the apostrophe.)
I can’t change the ‘general’ format to ‘number’ for each source selection (I’ll be using filters to get lists like this dozens of times, so I could but the point of VBA is to not be doing that!)
Sub Transpose_Values()
'
' Transpose_Values Macro
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Selection.NumberFormat = 0#
End Sub
I’ve tried a bunch of variations of NumberFormat and nothing works. The apostrophes stay. How could I fix this? I’m flipping the "Results’ into this table and need to apply any ‘Flags’ with custom formatting to the numbers like this:
(Note the highlighted ‘9.5’ has an apostrophe (came from the transpose macro) and won’t accept the key command to add a ‘J flag’ like the cells in the upper right have, which I entered manually as numbers and the key command works for)
UPDATE: I tried changing the format to ‘Number’ in a selection of the source data just to see if that worked, and it still pasted all the cells in with apostrophes. Except for the first value, interestingly. Whether it has an ‘<‘ or not, it gets pasted in normally and the others all have apostrophes, even when the source data is already set to ‘Number’.
>Solution :
Use Replace() on the range where you’re pasting values to find and replace the apostrophe with nothing. You could do the same thing to remove the "<" also.
For Each cell In Range("YourRange")
cell.Value = Replace(cell.Value, "'", "")
Next cell