Values are not copying over as entered on one sheet to another.

[pawcoyote]

Howdy,

I am not sure what is wrong with this code. I have two spreadsheets on one I enter costs in the accounting format with four (4) decimal places. There is a copy cells option that we use but when it copies the contents of the cell from one worksheet to the other it does not bring over the formatting and it changes the number. i.e. 0.0123 I enter and when it gets copied it goes to 0.0100 or I enter 0.0095 when it is copied it goes to 0.0100 as well.

I have made bold the two lines that are the ones I am having issues with.

The Green box has the cells we enter the data in and the Gray box has what it looks like when it copies over..

copyfromcells.jpgcopytocells.jpg

Option Explicit

Sub BuildMOST()
Dim i As Long
Dim iCol As Long

Set wsMDS = Worksheets("MDS Equipment Detail")
Set wsMOST = Worksheets("Most Equipment ADD")

Set headerMDS = wsMDS.Rows(rowHeaderMDS)
Set headerMOST = wsMOST.Rows(rowHeaderMOST)

Application.ScreenUpdating = False

'get last Client Name row
Set wsMDS = Worksheets("MDS Equipment Detail")
Set headerMDS = wsMDS.Rows(rowHeaderMDS)

'get last Client Name row
iCol = GetColumnNumber("Client Name", headerMDS)

With wsMDS
rowDataEndMDS = .Cells(.Rows.Count, iCol).End(xlUp).Row
End With

'added 3/21/2017
If rowDataEndMDS < 7 Then
MsgBox "No Data on MDS to copy to MOST"
Exit Sub
End If

Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)


Range(wsMOST.Rows(rowDataStartMOST), wsMOST.Rows(rowDataEndMDS)).EntireRow.Delete

For i = rowDataStartMDS To rowDataEndMDS
With wsMOST.Rows(i - 2)
' LUV(headerMOST, "Config Serial Number" & vbLf & "Manufacturer serial if no Configured serial", i - 2).Value = LUV(headerMDS, "Oracle Configuration SN", i).Value

.Cells(1).Value = LUV(headerMDS, "Oracle Configuration SN", i).Value
.Cells(2).Value = LUV(headerMDS, "Service Resource", i).Value
.Cells(3).Value = LUV(headerMDS, "Oracle Project Code", i).Value
.Cells(10).Value = LUV(headerMDS, "Location Street Address", i).Value & " " & LUV(headerMDS, "Location City", i).Value & " " & LUV(headerMDS, "Location State", i).Value & " " & LUV(headerMDS, "Location Zip", i).Text
.Cells(12).Value = LUV(headerMDS, "Cost Center (if required)", i).Value
.Cells(13).Value = LUV(headerMDS, "Department Name (if required)", i).Value
.Cells(14).Value = LUV(headerMDS, "Contract Effective Date", i).Value
.Cells(15).Value = LUV(headerMDS, "Account Entitlements (Service or Supplies or Both or Monitor Only)", i).Value
.Cells(18).Value = LUV(headerMDS, "Contract Effective Date", i).Value
.Cells(19).Value = LUV(headerMDS, "B&W Total Meter", i).Value
.Cells(25).Value = LUV(headerMDS, "BW Supplies Overage Rate", i).Value
.Cells(26).Value = LUV(headerMDS, "Color Total Meter", i).Value
.Cells(29).Value = LUV(headerMDS, "Color Supplies Overage Rate", i).Value
.Cells(30).Value = LUV(headerMDS, "Contract Effective Date", i).Value
.Cells(39).Value = LUV(headerMDS, "First Name", i).Value & " " & LUV(headerMDS, "Last Name", i).Value
.Cells(40).Value = LUV(headerMDS, "Phone", i).Value
.Cells(41).Value = LUV(headerMDS, "E-mail", i).Value
.Cells(43).Value = LUV(headerMDS, "Location Street Address", i).Value & " " & LUV(headerMDS, "Location City", i).Value & " " & LUV(headerMDS, "Location State", i).Value & " " & LUV(headerMDS, "Location Zip", i).Text
.Cells(44).Value = LUV(headerMDS, "Mfg", i).Value
.Cells(45).Value = LUV(headerMDS, "Oracle VPN (Item Number)", i).Value
.Cells(46).Value = LUV(headerMDS, "Ricoh Equipment ID", i).Value
.Cells(47).Value = LUV(headerMDS, "Serial Number", i).Value
End With
Next i


Application.ScreenUpdating = True


MsgBox "The data has been copied to the MOST Equipment Add worksheet. Please verify that the data has copied over properly!" ' 5/9/17 Fixed spelling



End Sub

[Paul_Hossler]

Formatting is 'sort of' part of the cell, and you're just putting a new value into whatever formatting is there

You can either

1. apply the desired formatting manually,
2. do a copy/paste
3. or try something like this untested bit of code

.Cells(25).Value = LUV(headerMDS, "BW Supplies Overage Rate", i).Value
.Cells(25).NumberFormat = LUV(headerMDS, "BW Supplies Overage Rate", i).NumberFormat

If these don't work, post a small WB

[pawcoyote]

Hi,

I tried that code it didn't fix it. I can copy and paste special and it puts the correct number in the cell.

I have attached a sample for you to look at... I am pretty sure you helped me with the a while ago...

Formatting is 'sort of' part of the cell, and you're just putting a new value into whatever formatting is there

You can either

1. apply the desired formatting manually,
2. do a copy/paste
3. or try something like this untested bit of code

.Cells(25).Value = LUV(headerMDS, "BW Supplies Overage Rate", i).Value
.Cells(25).NumberFormat = LUV(headerMDS, "BW Supplies Overage Rate", i).NumberFormat

If these don't work, post a small WB

[Paul_Hossler]

Since AS7 is styled as 'Currency' the .Value of the LUV() function is a vbCurrency variant

Currency variables are a little funny and I don't use them since there is some decimal scaling built in

https://msdn.microsoft.com/VBA/Langu...ency-data-type

Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Thetype-declaration character for Currency is the at sign ( @ ).

Easiest way is to just convert them to a Double with CDbl before putting on the worksheet

            .Cells(25).Value = CDbl(LUV(headerMDS, "BW Supplies Overage Rate", i).Value)

BTW -- you've made some good progress with your work book. Looks nice. Hope your users appreciate your hard work

[pawcoyote]

Hi and Thank you, you have shown me a lot. Still learning as I go... Lot's to learn.

I made the following changes, Format of the cells on the sheet I enter the info to General. Added in the code below and it worked like a champ. I need to read up more on currency to see how I can get that to work properly.

Since AS7 is styled as 'Currency' the .Value of the LUV() function is a vbCurrency variant

Currency variables are a little funny and I don't use them since there is some decimal scaling built in

https://msdn.microsoft.com/VBA/Langu...ency-data-type





Easiest way is to just convert them to a Double with CDbl before putting on the worksheet

            .Cells(25).Value = CDbl(LUV(headerMDS, "BW Supplies Overage Rate", i).Value)

BTW -- you've made some good progress with your work book. Looks nice. Hope your users appreciate your hard work