Why does this function NOT return a value?
 

The msgbox in the "strip" function below has a good value.
But when I check it in the " MsgBox "after strip comp1=" & comp1 & ",
comp2=" & comp2
" box, there is no value. Any help would be greatly appreciated.
Sam


Sub compare_output()
Dim str1 As String
Dim str2 As String
Dim comp1 As String
Dim comp2 As String
r = 1
Dim lastrow As Long, i As Long
ActiveSheet.UsedRange
With Cells.SpecialCells(xlCellTypeLastCell)
lastrow = .Row
End With

For i = 1 To lastrow
str1 = ActiveSheet.Cells(r, 1)
str2 = ActiveSheet.Cells(r, 2)
MsgBox "str1=" & str1 & ", str2=" & str2
comp1 = strip(str1)
comp2 = strip(str2)
MsgBox "after strip comp1=" & comp1 & ", comp2=" & comp2
If (comp1 < comp2) Then
XColor = 5
Else
XColor = 7
End If

If (XColor) Then
Range(ActiveSheet.Cells(r, 1), ActiveSheet.Cells(r, 2)).Select
With Selection.Interior
.ColorIndex = XColor
.Pattern = xlSolid
End With
End If
r = r + 1

Next
End Sub

Function strip(chars)
Dim buff As String
buff = " "
For i = 1 To Len(chars)
If Mid(chars, i, 1) " " Then
buff = buff & Mid(chars, i, 1)
End If
Next i
MsgBox "buff=" & buff
End Function

Why does this function NOT return a value?
 

Strip is never set in the function of the same name.
--
Gary''s Student


"Nooby" wrote:

The msgbox in the "strip" function below has a good value.
But when I check it in the " MsgBox "after strip comp1=" & comp1 & ",
comp2=" & comp2
" box, there is no value. Any help would be greatly appreciated.
Sam


Sub compare_output()
Dim str1 As String
Dim str2 As String
Dim comp1 As String
Dim comp2 As String
r = 1
Dim lastrow As Long, i As Long
ActiveSheet.UsedRange
With Cells.SpecialCells(xlCellTypeLastCell)
lastrow = .Row
End With

For i = 1 To lastrow
str1 = ActiveSheet.Cells(r, 1)
str2 = ActiveSheet.Cells(r, 2)
MsgBox "str1=" & str1 & ", str2=" & str2
comp1 = strip(str1)
comp2 = strip(str2)
MsgBox "after strip comp1=" & comp1 & ", comp2=" & comp2
If (comp1 < comp2) Then
XColor = 5
Else
XColor = 7
End If

If (XColor) Then
Range(ActiveSheet.Cells(r, 1), ActiveSheet.Cells(r, 2)).Select
With Selection.Interior
.ColorIndex = XColor
.Pattern = xlSolid
End With
End If
r = r + 1

Next
End Sub

Function strip(chars)
Dim buff As String
buff = " "
For i = 1 To Len(chars)
If Mid(chars, i, 1) " " Then
buff = buff & Mid(chars, i, 1)
End If
Next i
MsgBox "buff=" & buff
End Function

Why does this function NOT return a value?
 

Sorry, but I am new to this and I'm working as a fill in on a real
project.
I don't understand your answer. How do I set it?

Why does this function NOT return a value?
 

Function strip(chars)
Dim buff As String
buff = " "
For i = 1 To Len(chars)
If Mid(chars, i, 1) " " Then
buff = buff & Mid(chars, i, 1)
End If
Next i
MsgBox "buff=" & buff
strip=buff
End Function


Note the added last statement before the End Function

--
Gary''s Student


"Nooby" wrote:

Sorry, but I am new to this and I'm working as a fill in on a real
project.
I don't understand your answer. How do I set it?