Copying number using VBA

[nebffa]

Hi all, I have cells containing 'strings that look like numbers' - e.g. 04012

I have this line in my code:

Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).Value = .Cells(J, 3 * (I + 1) - 2).Value

The problem is, when assigning the value if the old cell is 04012 the new cell will be 4012 as Excel seems to treat it as a number rather than a string. There are many simple workarounds I could craft for this, for example just chucking a "%" before each value before I copy it, then removing the "%" afterwards, but I want to learn a lot about Excel so is there some other way I can do this directly?

 

[nebffa]

Nope it's not downloaded data from another source. What I've done is followed Rick's suggestion and uploaded my file to box.net

Here is the link to the file: https://www.box.com/s/5a4b31ae6973ef87db4e

What you can note is that on worksheets "PCheck" and "SCheck" there are 5 digit numbers that are stored as text, and when they show up in the sheet "MicrosoftWord" after the macro has run some have become 4 digit numbers stored as numbers (I want all of them to stay as 5 digit numbers stored as text)


PS. I use Excel 2010, so you know

 

[Michael M]

I can't access box.net from work....but, I note that you say the sheet is called "MicrosoftWord"
Have you changed the code Rick or I posted, to reflect that sheet name ??

 

[nebffa]

Ahhhhhhhhhhh!! I am so sorry! Well what happens actually before it goes to sheet "MicrosoftWord" is data is worked through the sheet "Working". I thought I was looking at that sheet to see if your method worked, but I must've been looking at "MicrosoftWord". Michael M, your solution works perfectly, however Rick's code still does not fix the problem.

Regardless, all I need to do now is do exactly what you wrote Michael and then go from there. Thanks so much for your help and sorry about leading you guys astray

 

[Rick Rothstein]

Ahhhhhhhhhhh!! I am so sorry! Well what happens actually before it goes to sheet "MicrosoftWord" is data is worked through the sheet "Working". I thought I was looking at that sheet to see if your method worked, but I must've been looking at "MicrosoftWord". Michael M, your solution works perfectly, however Rick's code still does not fix the problem.

Regardless, all I need to do now is do exactly what you wrote Michael and then go from there. Thanks so much for your help and sorry about leading you guys astray

Actually, my code will not work because however you have loaded up the PCheck and SCheck worksheets, all the cells are formatted as General even though the contents of the cells are numbers displaying a leading zero. The reason Michael's suggestion is not working is because you are looking at the wrong place for the problem (which is a good reason to give us all the code instead of the fragment where you think the problem is). Michael's suggestion works fine, but you undo the formatting later on in your CreateTable function (which, like several other "functions" in your project, are not really functions... they are not returning a value... so, in essence, they are simple Subs and nothing more). Anyway, here is your CreateTable function with the code line shown in red where I applied Michael's suggestion to fix your problem...

Function CreateTable(ByRef SchoolCodes() As String)
Dim SchoolCode As Integer
Dim vlLastRow As Integer
vlLastRow = LastRowInOneColumn(ColumnNumber:=2, UseWorksheet:="MicrosoftWord")
With Worksheets("MicrosoftWord")
    For I = 0 To UBound(SchoolCodes) - 1
        [SIZE=3][COLOR=#B22222][B].Cells(I + vlLastRow + 2, 2).Value = "'" & SchoolCodes(I + 1)[/B][/COLOR]
[/SIZE]    Next I
    For I = 0 To UBound(SchoolCodes) - 1
        For J = 0 To 4
            If PCheck.Cells(3 + I, 3 + 3 * J).Value <> "FALSE" Then
                SchoolCode = PCheck.Cells(3 + I, 3 + 3 * J - 2).Value
                For K = 0 To UBound(SchoolCodes) - 1
                    If SchoolCode = SchoolCodes(K + 1) Then
                        .Cells(4 + I, 3 + J).Value = Chr(149)
                    End If
                Next K
            End If
        Next J
    Next I
End With
End Function

 

[Rick Rothstein]

Oh, I forgot to mention in my last message... you still need to apply Michael's suggestion to the SchoolLevel "function" (which as I mentioned earlier, is not really a function, rather, you should make it a Sub)...

Function SchoolLevel(ByVal PriSch As String)
Dim SchoolCodes() As String
Dim blDimensioned As Boolean
blDimensioned = False
Dim blSchoolCodeCheck As Boolean
Dim SchoolCodeStackHeight As Integer
Dim ColumnNumber As Integer
Dim vlLastRow As Integer
Sheets.Add.Name = "Working"
With Worksheets(PriSch)
SchoolCodeStackHeight = 0
    For I = 0 To 4
        vlLastRow = LastRowInOneColumn(ColumnNumber:=I + 1, UseWorksheet:=PriSch)
        For J = 3 To vlLastRow
            [SIZE=2][B][COLOR=#b22222]Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).Value = "'" & .Cells(J, 3 * (I + 1) - 2).Value[/COLOR][/B]
[/SIZE]            SchoolCodeStackHeight = SchoolCodeStackHeight + 1
        Next J
    Next I
End With
SortSchoolCodes
ArraySchoolCodes SchoolCodes:=SchoolCodes
CreateTable SchoolCodes:=SchoolCodes
End Function

 

[Michael M]

Glad you got it sorted, sorry that I couldn't look at your workbook, due to work station restrictions.
Thanks Rick for posting the rest of the code....at least it's easier to understand once it's all there.

 

[Rick Rothstein]

Glad you got it sorted, sorry that I couldn't look at your workbook, due to work station restrictions.
Thanks Rick for posting the rest of the code....at least it's easier to understand once it's all there.

Just to give you a complete picture, there was an intermediate "function" which transferred the originally formatted numbers into the SchoolCodes array... those values transferred in correctly (that is, with leading zeroes) because the SchoolColds arrays was declared as a String array, it was just the assignment from the SchoolCodes array into the cells on the MicrosoftWord sheet to the cell's Value property in the CreateTable "function" that undid everything. Again, for a complete picture, here is that intermediate procedure...

Public Function ArraySchoolCodes(ByRef SchoolCodes() As String)
Dim vlLastRow As IntegervlLastRow = LastRowInOneColumn(ColumnNumber:=1, UseWorksheet:="Working")
    With Sheets("Working")
    ReDim SchoolCodes(1 To vlLastRow) As String
    [B][COLOR=#B22222]For I = 1 To vlLastRow
        SchoolCodes(I) = .Cells(I, 1).Value
    Next I
[/COLOR][/B]    .Range("A1").EntireColumn.Clear
    Application.DisplayAlerts = False
    Sheets("Working").Delete
    Application.DisplayAlerts = True
End With
End Function

 

[Michael M]

Thanks Rick....simply reinforces what you said in an earlier post...."show us ALL the code" in case we're looking in the wrong place !!

 

[nebffa]

Yes Rick I found that was the issue before you mentioned it, but yes.... show all the code lol. Thankyou both!