What the Heck VBA...

[magelan]

So, I have a loop that is going through allll the cells in a column -

 For Each cellVar In checkRange
            Call lengthCheck(cellVar)
            Debug.Print "Final Cell is..." & cellVar.Value
        Next

Then i have the sub it is calling...

Sub lengthCheck(cellVar As Range)
    Dim splitter() As String
    Dim workingVar As String
    Dim j As Integer
    
    splitter = Split(cellVar.Value, "-") 'get rid of -
    workingVar = splitter(LBound(splitter))
    workingVar = Application.WorksheetFunction.Clean(workingVar) 'remove special chars
    
    If Len(workingVar) < 5 Then
        For j = 1 To (5 - Len(workingVar)) 'for however many 0's its missing, add one
            workingVar = 0 & workingVar
            Debug.Print "Adding..." & workingVar
        Next
    End If
    If Len(workingVar) > 5 Then
        workingVar = Left(workingVar, 5) 'only take the left 5 digits of a long zip
        Debug.Print "Trimming..." & workingVar
    End If
    
    cellVar.Value = workingVar
    Debug.Print "Final cell SHOULD " & cellVar.Value & " Working " & workingVar
End Sub

Now here is the thing. The Debug.print "Adding.." shows me that it is adding a 0 to the beginning of this value to make it 5 characters. However, the next debug.prints contradict that this operation happened.

Here is the output of my debug.prints.

Adding...07885
Final cell SHOULD 7885 Working 07885
Final Cell is...7885

Am I doing something wrong? How can "cellVar.value = workingVar" followed by "Debug.print CellVar.value & workingVar" show the two values as completely different?

[Kenneth Hobs]

& is a concatenation operator. If you need leading 0's, use a number format and then use Text to rather than Value for a cell. Of course you can always use Format() to get a number into a string format as well.

[magelan]

& is a concatenation operator. If you need leading 0's, use a number format and then use Text to rather than Value for a cell. Of course you can always use Format() to get a number into a string format as well.

But would that still influence workingvar and cellvar.value showing different values?

I can always make it `"0" & workingvar` instead of "0 & workingvar" [without the ` and " surrounding] which should hopefully concatenate correctly.

[Kenneth Hobs]

I am not sure what you are after.

It is working as designed. I makes perfect sense to me.

See if this makes sense:
[VBA]Sub t()
Dim workingVar As String
workingVar = 4
If Len(workingVar) < 5 Then
workingVar = Format(workingVar, "00000")
End If
Debug.Print workingVar
End Sub[/VBA]

[magelan]

I am not sure what you are after.

It is working as designed. I makes perfect sense to me.

See if this makes sense:
[vba]Sub t()
Dim workingVar As String
workingVar = 4
If Len(workingVar) < 5 Then
workingVar = Format(workingVar, "00000")
End If
Debug.Print workingVar
End Sub[/vba]

I think my problem here is that my cell's value is not changing..

Cell Value SHOULD BE = 07885
Cell Value IS = 7885

is a result of

Debug.Print "Cell Value SHOULD BE = " & workingVar
Debug.Print "Cell Value IS = " & theCell.Value

i've also included the code

workingVar = Format(workingVar, "00000")
theCell.Value = workingVar
theCell.Value = Format(workingVar, "00000")

I just dont see how it makes sense to say "x = y" and then printing x and y shows 2 different values.

EDIT-NOTE even without the Format... function, debug.print workingvar had the leading 0, so essentially the format didnt do anything.

[Kenneth Hobs]

Range("A1").Value may not be equal to Range("A1").Text.

At the top of a Module try some, Option Compare, options when using the = comparison operator. This goes at the top of a Module. e.g.
[VBA] 'Set the string comparison method to Binary.
Option compare Binary ' That is, "AAA" is less than "aaa".

' Set the string comparison method to Text.
Option compare Text ' That is, "AAA" is equal to "aaa". [/VBA]

There is also a Database compare option.

Attach a workbook if you like and try to simplify your issue if I have not explained why it is working properly, properly.

[Zack Barresse]

If you're entering 07885 into a cell, and it's formatted as General or any numeric, it will lop off leading zero's. So you'd either need to use the Format() function as Kenneth suggested, set the cell format to text and then add your leading zero's, or set the value to a string variable at run-time and add the leading zero's as desired.

Your code uses two different variable types, where the workingVar is a string, so it's value will be literal, while you're also passing the Value of a cell, which means whatever you see in the formula bar, and looks like you're passing it as a Range variable.

[magelan]

If you're entering 07885 into a cell, and it's formatted as General or any numeric, it will lop off leading zero's. So you'd either need to use the Format() function as Kenneth suggested, set the cell format to text and then add your leading zero's, or set the value to a string variable at run-time and add the leading zero's as desired.

Your code uses two different variable types, where the workingVar is a string, so it's value will be literal, while you're also passing the Value of a cell, which means whatever you see in the formula bar, and looks like you're passing it as a Range variable.

Hmm...I understand what youre saying, but I feel like i'm literally assigning a variable a value and having it come up with a different variable.

What can I change out of this block of code to make thecell.value actually equal the workingvar? [in this case, my output in the immediate window is..

Adding...07885
Cell Value SHOULD BE = 07885
Cell Value IS = 7885
Final Cell is...7885

and my code is...

...
Call lengthCheck(cellVar)
Debug.Print "Final Cell is..." & cellVar.Value
...

Sub lengthCheck(theCell As Range)
    Dim splitter() As String
    Dim workingVar As String
    Dim j As Integer
    
    splitter = Split(theCell, "-") 'get rid of -
    workingVar = splitter(LBound(splitter))
    workingVar = Application.WorksheetFunction.Clean(workingVar) 'remove special chars
    
    If Len(workingVar) < 5 Then
        For j = 1 To (5 - Len(workingVar)) 'for however many 0's its missing, add one
            workingVar = 0 & workingVar
            Debug.Print "Adding..." & workingVar
        Next
    End If
    If Len(workingVar) > 5 Then
        workingVar = Left(workingVar, 5) 'only take the left 5 digits of a long zip
        Debug.Print "Trimming..." & workingVar
    End If
    
    theCell.Value = workingVar
    Debug.Print "Cell Value SHOULD BE = " & workingVar
    Debug.Print "Cell Value IS = " & theCell.Value
    
End Sub

[Aflatoon]

Try
[vba]thecell.numberformat = "@"
thecell.value = workingvar[/vba]

[magelan]

Try
[vba]thecell.numberformat = "@"
thecell.value = workingvar[/vba]

Well, that fixed it.

Cell Value SHOULD BE = 07885
Cell Value IS = 07885
Final Cell is...07885

And i definitely would never have guessed that fix myself.

Thanks!