PDA

View Full Version : What the Heck VBA...



magelan
12-03-2012, 02:04 PM
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
12-03-2012, 02:33 PM
& 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
12-03-2012, 02:41 PM
& 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
12-03-2012, 02:49 PM
I am not sure what you are after.

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

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

magelan
12-03-2012, 03:15 PM
I am not sure what you are after.

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

See if this makes sense:
Sub t()
Dim workingVar As String
workingVar = 4
If Len(workingVar) < 5 Then
workingVar = Format(workingVar, "00000")
End If
Debug.Print workingVar
End Sub
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
12-03-2012, 04:54 PM
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.
'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".

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
12-03-2012, 07:06 PM
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
12-10-2012, 08:21 AM
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
12-10-2012, 08:32 AM
Try
thecell.numberformat = "@"
thecell.value = workingvar

magelan
12-10-2012, 08:47 AM
Try
thecell.numberformat = "@"
thecell.value = workingvar

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!