Another Scientific Notation Quandry

[JAlbers]

Greetings

I have a report that contains a batch number which is formatted YYMXXX where the month is represented by a single letter. These letters are sequential from A=Jan, B=Feb etc. I don't have a problem manipulating my data until that fateful month of May when my 13E123 suddenly becomes 1.30E+124.

Using a macro I copy it from a text formatted field where it is formatted correctly and paste it into another sheet where it comes up as scientific even though I have formatted the column as text. Going back and trying to format the column again after the numbers are in it just results with everything shifting a bit and snickering at me.

While I did my best to get the powers that be to use something, anything other than an E for May, it was not to be. Looking through the forum I see others with problems with long numbers, but I haven't found a solution to a situation where I have data that actually looks like scientific notation before I even get started.

Any help would be greatly appreciated. I am using Excel 2010.

 

[circledchicken]

Hi,

Thats funny . Can you post the macro you are using?

Perhaps try amending your macro so that each entry begins with an apostrophe character ('). This should ensure it is stored as text the way you want rather than converted to a number.

 

[JAlbers]

Sure, here is the code. I am pulling scattered data from another sheet and consolidating it so that I can generate an MRP list for production.

Sub Consolidate()


    Dim Data As Variant
    Dim FirstCell As Range
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim LastColumn As Long
    Dim lastrow As Long
    Dim Lines As Variant
    Dim Rng As Range
    Dim LastCol As Long
    Dim BlockStart As Long
    




    'Start consolidating data and outputting it to table in sheet Output
    ReDim Data(1 To 5)
        'Initialize ranges
        With Worksheets("Schedule")
            Set FirstCell = .Range("A6")
                    lastrow = 61
                    LastColumn = 30
            Set Rng = .Range(FirstCell, .Cells(lastrow, LastColumn))
        End With
            
            k = ((LastColumn - Rng.Column) + 1) \ 5
            ReDim Lines(1 To k, 1 To Rng.Rows.Count)
            c = 2
                'Getting Loopy
                For c = 2 To LastColumn Step 5
                    i = 0       ' Independent row counter.
                    j = j + 1   ' Production Line number.
                    
                    'Creates table in memory to be output later
                    For r = 1 To Rng.Rows.Count
                        Data(1) = Empty
                        Data(2) = Rng.Cells(r, c)
                        Data(3) = Rng.Cells(r, c + 1)
                        Data(4) = Rng.Cells(r, c + 2)
                        Data(5) = Rng.Cells(r, c + 3)
                        
                        If Application.CountA(Rng.Cells(r, c).Resize(1, 5)) > 0 Then
                          ' Get the Kettle number.
                            Data(1) = (Rng.Cells(r, "A").MergeArea.Row - 2) / 4
                            i = i + 1
                            Lines(j, i) = Data
                        End If
                    Next r
                Next c
                
        
          ' Ouput the consolidateed data to "Output".
            For i = 1 To 6
                For j = 1 To UBound(Lines, 2)
                    Data = Lines(i, j)
                    Worksheets("Output").Range("B2:F2").Offset(j - 1, _
                        (i - 1) * 5).Value = Data
                Next j
            Next i
            
                BlockStart = 58
                
            For dblock = 7 To 35 Step 5
                Cells(2, dblock).Resize(56, 5).Select
                    Selection.Copy
                Cells(BlockStart, 2).Select
                    Selection.PasteSpecial Paste:=xlAll
                    BlockStart = BlockStart + 56
            Next dblock
            
            Cells(2, 7).Resize(57, (Columns.Count - 7)).ClearContents
            Cells(1, 1) = "Prod Line"
   end sub

it is Data(5) that would contain the aforementioned batch number.

 

[circledchicken]

I haven't looked at your code in detail, but try this small change in red:

Data(5) = [COLOR=#b22222][B]"'" & [/B][/COLOR]Rng.Cells(r, c + 3)

 

[shg]

You could pre-format the output range as text:

    With Worksheets("Output").Range("B2:F2").Offset(j - 1, (i - 1) * 5)
        .NumberFormat = "@"
        .Value = Data
    End With

 

[JAlbers]

Thanks! CircledChicken that worked perfectly! Shg, it looks like yours will work too, but for simplicity's sake I am going to use the other one. I am struggling through a few lines at a time and still don't have a really good concept of what I am doing so I'm trying to keep things pretty obvious so when I come back to in a month or so, I can remember what I did.

Thanks both of you. I am always amazed with the elegance of the solutions I see on here. You guys are awesome!

 

[shg]

You're welcome, good luck.