How to Conserve Decimals When Using Transpose Method?

[_Echo_2]

Hi all,

Been working on this problem most of the afternoon:

I am transposing data from a data sheet to a temporary sheet, sorting it, and then transposing it back to the original formatting on the data sheet. I am avoiding using "Copy" and "Paste," so therefore I have not been using PasteSpecial, etc.
Here is what I have right now:

'Sets rng as the range to copy
rng = Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank + 1)

'Transposes the chem data range to the temp sheet
Sheets("Temp").Range("A1:B" & lastCol - 7).Value = Application.Transpose(rng)

'Sorts data on temp via max values
Worksheets("Temp").Range("A1:B" & lastCol - 7).Sort _
Key1:=Worksheets("Temp").Range("B:B"), Order1:=xlDescending

'Copy data back to spec sheet
Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank).Value = Application.Transpose(Sheets("Temp").Range("A1:A" & lastCol - 7))

The problem comes at the first 'transpose' line where any numbers with 0's after a decimal are lost. For example if I have 0.10, during the transpose and then after it is added back to the data sheet it appears as 0.1.

I tried adding the xlRangeValueDataType Enumeration(11) after the .Value part, but that gives me an error since I can't do the same to the right side of the equation (I think).

How can I do this while conserving decimal places?

Thanks!

 

[Joe4]

Are your entries numbers or text?

If they are numbers, then if a cell is showing 0.10, it is due to the formatting on the cell that shows two decimal places.
So it would just be a matter of applying the same formatting as your originally had before the transposing.
Hopefully, each cell is not formatted differently, so you can just re-apply a single format to your range after the final transpose.

 

[_Echo_2]

Are your entries numbers or text?

If they are numbers, then if a cell is showing 0.10, it is due to the formatting on the cell that shows two decimal places.
So it would just be a matter of applying the same formatting as your originally had before the transposing.
Hopefully, each cell is not formatted differently, so you can just re-apply a single format to your range after the final transpose.

Well the numbers are placed on the data sheet via user inputs on a userform. When the decimals are transferred from the form to the cells, the format is as "Text". After organizing the data, they are placed back as text as well, but lose the 0's at the end (if any). The only problem with formatting the entire range is that there are not necessarily always going to be the same number of decimal places, so doing something like

Selection.NumberFormat = "0.00"

would not be effective. Ex. if I have 0.005 for one number, setting the format with the code above would round to 0.01 in this case.

Let me throw something else out here:
I have a userform as mentioned before that has a series of boxes. One box is for an element, the next is for the minimum value of that element, and then the last is for the max. The reason I'm sorting is because users may not enter data into the form from greatest to least (and I don't expect them to), but when they 'look up' the data later and it repopulates the form, I would like it displayed greatest to least, relative to the maximum values. Is there a way to sort greatest to least when transferring the data directly from the UF to the data sheet and not have to use the 'regular' sorting methods?

Here is an example the entered data in the userform:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Element[/TD]
[TD]Fe[/TD]
[TD]B[/TD]
[TD]Co[/TD]
[/TR]
[TR]
[TD]Min[/TD]
[TD]Balance[/TD]
[TD]0.005[/TD]
[TD]8.50[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD]Balance[/TD]
[TD]0.010[/TD]
[TD]9.50[/TD]
[/TR]
</tbody>[/TABLE]

Here is an image of the 'looked-up' data from the data sheet populated back into the userform:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Element[/TD]
[TD]Fe[/TD]
[TD]Co[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Min[/TD]
[TD]Balance[/TD]
[TD]8.50[/TD]
[TD]0.005[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD]Balance[/TD]
[TD]9.50[/TD]
[TD]0.010[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Try formatting the range you are copying to as Text before pasting (it seemed to work for me), i.e.

'Sets rng as the range to copy
rng = Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank + 1)

'Transposes the chem data range to the temp sheet
Sheets("Temp").Range("A1:B" & lastCol - 7).NumberFormat = "@"
Sheets("Temp").Range("A1:B" & lastCol - 7).Value = Application.Transpose(rng)

'Sorts data on temp via max values
Worksheets("Temp").Range("A1:B" & lastCol - 7).Sort _
Key1:=Worksheets("Temp").Range("B:B"), Order1:=xlDescending

'Copy data back to spec sheet
Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank).NumberFormat = "@"
Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank).Value = Application.Transpose(Sheets("Temp").Range("A1:A" & lastCol - 7))

 

[_Echo_2]

Try formatting the range you are copying to as Text before pasting (it seemed to work for me), i.e.

'Sets rng as the range to copy
rng = Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank + 1)

'Transposes the chem data range to the temp sheet
Sheets("Temp").Range("A1:B" & lastCol - 7).NumberFormat = "@"
Sheets("Temp").Range("A1:B" & lastCol - 7).Value = Application.Transpose(rng)

'Sorts data on temp via max values
Worksheets("Temp").Range("A1:B" & lastCol - 7).Sort _
Key1:=Worksheets("Temp").Range("B:B"), Order1:=xlDescending

'Copy data back to spec sheet
Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank).NumberFormat = "@"
Sheets(MWS).Range("H" & NextBlank, ConvertToLetter & NextBlank).Value = Application.Transpose(Sheets("Temp").Range("A1:A" & lastCol - 7))

Ok this worked! What exactly does the @ do/represent? Does that just preserve however it appears originally?

Thanks Joe!

 

[Joe4]

What exactly does the @ do/represent? Does that just preserve however it appears originally?

That is just the VBA setting for Text format.
If you turn on the Macro Recorder, and format any cell to be Text, this is what it looks like in VBA.