numbers change to text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a row of formulas that yield a percentage. These formulas reference
% values on another work sheet

I have the following Macro that copies these values to another row that is
formatted %

Private Sub CommandButton5_Click() 'Copies Calculated MI% to Actual MI%
Range("AB41:AF41").Select
Selection.Copy
Range("AB40").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("AB40").Select
End Sub

The copy results in numbers formatted as text

What am I doing wrong?

oldjay
 
Give the following code a try...

Range("A41:F41").Copy
Range("A40").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("A40").Select

Note we didn't have to physically select the first range to copy it, nor the
second range cell to paste into it (although it looks like we do need to
select the range cell at the end to turn off the large selection).

Rick
 
I tried it got with the same result!!

Rick Rothstein (MVP - VB) said:
Give the following code a try...

Range("A41:F41").Copy
Range("A40").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("A40").Select

Note we didn't have to physically select the first range to copy it, nor the
second range cell to paste into it (although it looks like we do need to
select the range cell at the end to turn off the large selection).

Rick
Click to expand...
 
I tried it with the same result

Rick Rothstein (MVP - VB) said:
Give the following code a try...

Range("A41:F41").Copy
Range("A40").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("A40").Select

Note we didn't have to physically select the first range to copy it, nor the
second range cell to paste into it (although it looks like we do need to
select the range cell at the end to turn off the large selection).

Rick
Click to expand...
 
You are still getting the values pasted as text? Using the exact code I
posted (corrected for the range change I made when testing)? If yes, post
the formula you have in AB41 and AC41 along with the values in whatever
cells they are referencing. Also, which cells are formatted as percentage...
the cells being referenced on the other work sheet and/or those in AB41 to
AF41?

Rick
 
Sorry for the delay in answering - my service provider was down

Cells are still being copied as text and I did correct the cells addresses

AB41 - =MAIN!205
AC41 - =MAIN!206
BOTH CELLS FORMATED AS %
MAIN!O205 & O206 FORMATED AS %
 
an added note - I formatted a cell on the Main sheet as %, then put 54 in the
cell
I then formatted the cell M105 on the Input sheet as % and then inserted
the formula =Main!M105. I then tried to Copy- Paste Special-Values to another
cell with the same result "Number formatted as text"
 
Back
Top