Excel is telling me black is white and up is down

[dan7055]

I have a Macro that will not work because even though my If statement is true, VBA says it's false. I cannot figure out why.

Here is part of my code

Start_Odo_Comment = Mid(Cells(i - 2, "K"), 12, 6)
Start_Odo_Comment = Replace(Start_Odo_Comment, ",", "")

For b = 6 To Quantity_Line_Items
If Start_Odo_Comment = Cells(b, "K") Then
Driver = Cells(b, "H")
End_Odo_Comment = Cells(b, "N")
Exit For
End If
If b = Quantity_Line_Items Then
MsgBox "I can't find the Driver Name for one of the comments based on the start odometer of " & Start_Odo_Comment & ", I will enter it as Unknown."
Driver = "Unknown"
End If
Next b



The bolded part is where it says "False" even though it's true. I hover over the variables and they both say "8195" exactly. It doesn't matter if I store the variable as long, integer, string etc. Stills says that it's false when it's true. Here is the whole code

Sub Transfer_Var_to_Log()

'Define Variables
Dim Start_Row As Integer 'This variable determines what row to begin at for pasting information into the Log
Dim Var_Path As Variant 'This variable is the user selected path to the Var Log(s)
Dim Log_Path As Variant 'This variable is the user selected path to the Leaf Log
Dim Comments_Path As Variant 'This variable is the user selected path to the Comments Log
Dim Quantity_Line_Items As Integer 'This variable is the number of line items for each VAR that are going to be copied and pasted into the LOG
Dim Start_AER_1 As Integer 'This variable is the first row that the AER-1 readings begin on in the VAR file
Dim Start_AER_2 As Integer 'This variable is the first row that the AER-2 readings begin on in the VAR file
Dim Start_ECO_1 As Integer 'This variable is the first row that the ECO-1 readings begin on in the VAR file
Dim Start_ECO_2 As Integer 'This variable is the first row that the ECO-2 readings begin on in the VAR file
Dim Start_SOC_1 As Integer 'This variable is the first row that the SOC-1 readings begin on in the VAR file
Dim Start_SOC_2 As Integer 'this variable is the first row that the SOC-2 readings begin on in the VAR file
Dim Start_Fuel As Integer 'this variable is the first row that the Fuel Consumption Activity begings on in the VAR file
Dim Lap As Integer 'this variable will determine if we are on a Lap 1 or Lap 2
Dim End_Odometer, Start_Odometer As Long 'These variables are the end odometer reading and start odometer reading of the current line item (lap) that the macro is on
Dim Previous_Log_Date As Date 'This variable is the date of the previously entered Log entry
Dim First_Var_Date As Date 'This variable is the data of the first entry in the VAR
Dim Insertion_Row As Integer 'This variable is the row in which lines must be inserted if necessary
Dim PL_Number, OF_Number As String 'These variables are the PL# and OF# of the comment taken from the VAR to be transfered to the Comment Log
Dim Vehicle_Number As String
Dim Driver_Comment As String 'This variable is the coment from the driver copied over from the VAR to the Comment Log
Dim First_Comment_Row, First_Blank_Comment_Row As Integer 'This variable is the first blank row in the Comment Log that the comment information will be copied over to
Dim Spec As String 'This variable is either Positive or Negative based on the PL number, is used when transfering comments to the Comments Log
Dim Driver_Name As String 'This variable is the name of the driver writing the comment
Dim Shift As String 'This variable is the shift that the comment was made on
Dim Date_of_Comment As String
Dim Start_Odo_Comment, End_Odo_Comment, Odo_Comment As Double
Dim Detection_Time As String 'This variable is the detection time of the comment
Dim Component As String 'This variable is the component for the comments

'Have the user select the VAR(s)

Var_Path = Application.GetOpenFilename(Title:="Select the VAR(s) you wish to transfer data from", MultiSelect:=True)
    
'    If Var_Path = CStr(False) Then
'        MsgBox ("You have not selected a VAR file")
'        Exit Sub
'    End If
    
'Have the user select the Log path

Log_Path = Cells(21, "C")

    If Log_Path = CStr(False) Then
        MsgBox ("You have not selected a Log file")
        Exit Sub
    End If


    Set Log_Path = Workbooks.Open(Log_Path)



'Open the VAR file(s) one by one

For fnum = LBound(Var_Path) To UBound(Var_Path)


    'First, identify the first empty line in the leaf_log so we know where to begin pasting information
    
    Log_Path.Activate
    For Start_Row = 5 To 4000
    
        If Cells(Start_Row, "A") = "" And Cells(Start_Row, "B") = "" Then
            If Cells(Start_Row + 1, "A") = "" And Cells(Start_Row + 2, "A") = "" And Cells(Start_Row + 3, "A") = "" And Cells(Start_Row + 4, "A") = "" And Cells(Start_Row + 5, "A") = "" Then
                Exit For
            End If
        End If
    
    Next Start_Row
    
    'Identify the previous log date
    
    Previous_Log_Date = Cells(Start_Row - 1, "A")
    
    

                
    
    

    Set Var_Path = Workbooks.Open(Var_Path(fnum))
    
    'Find out how many line items we have for this particular VAR
    
    For Quantity_Line_Items = 6 To 100
        If Cells(Quantity_Line_Items, "A") = "" Then
            Quantity_Line_Items = Quantity_Line_Items - 6
            Exit For
        End If
    Next Quantity_Line_Items
    
    
    
    'Identify the first Var date
    
    First_Var_Date = Cells(6, "A")
    
    
    
    
    
    If ThisWorkbook.Sheets("Sheet1").OLEObjects("Meter_Readings_CheckBox").Object.Value = True Then
    
    'Find out the row where the AER - 1 starts
        
        For Start_AER_1 = 1 To 1000
            If Cells(Start_AER_1, "E") = "AER - 1" Then
                Exit For
            End If
            If Start_AER_1 = 1000 Then
                AERmsg = MsgBox("I cannot find the row where AER - 1 readings begin in the VAR. Do you want me to skip transfering the AER readings?", vbYesNo)
                If AERmsg = vbYes Then GoTo ECO
                If AERmsg = vbNo Then Exit Sub
            End If
        Next Start_AER_1
        
        
        'Find out the row where the AER - 2 starts
        
        For Start_AER_2 = Start_AER_1 To 1000
            If Cells(Start_AER_2, "E") = "AER - 2" Then
                Exit For
            End If
            If Start_AER_2 = 1000 Then
                AERmsg = MsgBox("I cannot find the row where AER - 2 readings begin in the VAR. Do you want me to skip transfering the AER readings?", vbYesNo)
                If AERmsg = vbYes Then GoTo ECO
                If AERmsg = vbNo Then Exit Sub
            End If
        Next Start_AER_2
        
        
        'Find out the row where ECO - 1 starts
ECO:
        For Start_ECO_1 = 1 To 1000
            If Cells(Start_ECO_1, "E") = "ECO - 1" Then
                Exit For
            End If
            If Start_ECO_1 = 1000 Then
                ECOmsg = MsgBox("I cannot find the row where ECO - 1 readings begin in the VAR. Do you want me to skip transfering the ECO readings?", vbYesNo)
                If ECOmsg = vbYes Then GoTo SOC
                If ECOmsg = vbNo Then Exit Sub
            End If
        Next Start_ECO_1
        
        
        'Find out the row where ECO - 2 starts
        
        For Start_ECO_2 = Start_ECO_1 To 1000
            If Cells(Start_ECO_2, "E") = "ECO - 2" Then
                Exit For
            End If
            If Start_ECO_2 = 1000 Then
                ECOmsg = MsgBox("I cannot find the row where ECO - 2 readings begin in the VAR. Do you want me to skip transfering the ECO readings?", vbYesNo)
                If ECOmsg = vbYes Then GoTo SOC
                If ECOmsg = vbNo Then Exit Sub
            End If
        Next Start_ECO_2
        
        
        
        'Find out the row where the SOC - 1 starts
SOC:
        For Start_SOC_1 = 1 To 1000
            If Cells(Start_SOC_1, "E") = "SOC - 1" Then
                Exit For
            End If
            If Start_SOC_1 = 1000 Then
                SOCmsg = MsgBox("I cannot find the row where SOC - 1 readings begin in the VAR. Do you want me to skip transfering the SOC readings?", vbYesNo)
                If SOCmsg = vbYes Then GoTo Start_Fuel
                If SOCmsg = vbNo Then Exit Sub
            End If
        Next Start_SOC_1
        
        
        'Find out the row where SOC - 2 starts
        
        For Start_SOC_2 = Start_SOC_1 To 1000
            If Cells(Start_SOC_2, "E") = "SOC - 2" Then
                Exit For
            End If
            If Start_SOC_2 = 1000 Then
                SOCmsg = MsgBox("I cannot find the row where SOC - 2 readings begin in the VAR. Do you want me to skip transfering the SOC readings?", vbYesNo)
                If SOCmsg = vbYes Then GoTo Start_Fuel
                If SOCmsg = vbNo Then Exit Sub
            End If
        Next Start_SOC_2
        
   End If
        
        'Find out the row where the Fuel Consumption Activity Starts
Start_Fuel:
        For Start_Fuel = 5 To 1000
            If Cells(Start_Fuel, "F") = "EVSE Level III" Or Cells(Start_Fuel, "F") = "EVSE Level II" Or Cells(Start_Fuel, "F") Like "*FUEL*" Then
                Exit For
            End If
            If Start_Fuel = 1000 Then
                MsgBox "I cannot find the row where the Fuel Consumption Activity begins. I will exit sub."
                Exit Sub
            End If
        Next Start_Fuel
    
    
    
    'Figure out if lines need to be inserted in the Log and if so, insert the lines
    Log_Path.Activate
    If Previous_Log_Date > First_Var_Date Then
        MsgBox "The dates for this VAR do not sequentially follow the latest Log entries, I will have to insert new lines in the Log."
        For Insertion_Row = 5 To 30000
            If Cells(Insertion_Row, "A") > First_Var_Date Then
                For i = 1 To Quantity_Line_Items
                    Rows(Insertion_Row).Insert Shift:=xlDown
                    Range(Cells(Insertion_Row - 1, "AL"), Cells(Insertion_Row - 1, "AX")).AutoFill Destination:=Range(Cells(Insertion_Row - 1, "AL"), Cells(Insertion_Row, "AX")), Type:=xlFillDefault
                Next i
                Start_Row = Insertion_Row
                Exit For
            End If
        Next Insertion_Row
    End If
    
    
    
    
    
    'Start transfering information one line at a time
    
    For i = 0 To Quantity_Line_Items - 1
    
    
    
    
    
        'Copy in the Mileage Activity
        Var_Path.Activate
        Range(Cells(i + 6, "A"), Cells(i + 6, "B")).Copy
        Log_Path.Activate
        Cells(Start_Row + i, "A").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
    
        Var_Path.Activate
        Range(Cells(i + 6, "E"), Cells(i + 6, "H")).Copy
        Log_Path.Activate
        Cells(Start_Row + i, "F").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
        
        Var_Path.Activate
        Range(Cells(i + 6, "J"), Cells(i + 6, "P")).Copy
        Log_Path.Activate
        Cells(Start_Row + i, "J").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
    
    
    
    
        'Copy in the Meter Readings
        If ThisWorkbook.Sheets("Sheet1").OLEObjects("Meter_Readings_CheckBox").Object.Value = True Then
            x = Int(i / 2)
                  
            If i Mod 2 = 0 Then
            Lap = 1
            Else
            Lap = 2
            End If
    
            Select Case Lap
            
                Case 1
                    
                    If AERmsg <> vbYes Then
                        Var_Path.Activate
                        Range(Cells(x + Start_AER_1, "L"), Cells(x + Start_AER_1, "M")).Copy
                        Log_Path.Activate
                        Cells(Start_Row + i, "V").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
                        :=False, Transpose:=False
                    End If
                    If SOCmsg <> vbYes Then
                        Var_Path.Activate
                        Range(Cells(x + Start_SOC_1, "L"), Cells(x + Start_SOC_1, "M")).Copy
                        Log_Path.Activate
                        Cells(Start_Row + i, "X").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
                        :=False, Transpose:=False
                    End If
                    If ECOmsg <> vbYes Then
                        Var_Path.Activate
                        Range(Cells(x + Start_ECO_1, "L"), Cells(x + Start_ECO_1, "M")).Copy
                        Log_Path.Activate
                        Cells(Start_Row + i, "AJ").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
                        :=False, Transpose:=False
                    End If
                    
                Case 2
                    
                    If AERmsg <> vbYes Then
                        Var_Path.Activate
                        Range(Cells(x + Start_AER_2, "L"), Cells(x + Start_AER_2, "M")).Copy
                        Log_Path.Activate
                        Cells(Start_Row + i, "V").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
                        :=False, Transpose:=False
                    End If
                    If SOCmsg <> vbYes Then
                        Var_Path.Activate
                        Range(Cells(x + Start_SOC_2, "L"), Cells(x + Start_SOC_2, "M")).Copy
                        Log_Path.Activate
                        Cells(Start_Row + i, "X").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
                        :=False, Transpose:=False
                    End If
                    If ECOmsg <> vbYes Then
                        Var_Path.Activate
                        Range(Cells(x + Start_ECO_2, "L"), Cells(x + Start_ECO_2, "M")).Copy
                        Log_Path.Activate
                        Cells(Start_Row + i, "AJ").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
                        :=False, Transpose:=False
                    End If
                    
            End Select
        End If
        
        
        
       'Copy in the Fuel Consumption Activity
       Var_Path.Activate
        End_Odometer = Cells(i + 6, "N")
        Start_Odometer = Cells(i + 6, "K")
        
        For y = 0 To Quantity_Line_Items
            If Cells(Start_Fuel + y, "I") <= End_Odometer And Cells(Start_Fuel + y, "I") > Start_Odometer Then
                Range(Cells(Start_Fuel + y, "F"), Cells(Start_Fuel + y, "O")).Copy
                Log_Path.Activate
                Cells(Start_Row + i, "Z").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
                :=False, Transpose:=False
                GoTo Next_i
            End If
        Next y
         
        
    
Next_i:
    Next i
       
Next fnum



'Copy over the comments

For i = 1 To 2000
    Var_Path.Activate
    If Cells(i, "A") Like "*PL0*" Or Cells(i, "A") Like "*PL1*" Or Cells(i, "A") Like "*PL2*" Or Cells(i, "A") Like "*PL3*" Or Cells(i, "A") Like "*PL4*" Then
        PL_Start = InStr(Cells(i, "A"), "PL")
        PL_Number = Mid(Cells(i, "A"), PL_Start + 2, 1)
        
        OF_Start = InStr(Cells(i, "A"), "OF")
        OF_Number = Mid(Cells(i, "A"), OF_Start + 2, 1)
        
        Driver_Comment = Trim(Mid(Cells(i, "A"), OF_Start + 4))
        Vehicle_Number = Cells(2, "I")
        Date_of_Comment = Mid(Cells(i - 2, "G"), 7, 10)
        If PL_Number = "0" Then
            Spec = "Positive"
        Else
            Spec = "Negative"
        End If
        Shift = Cells(i - 2, i)
        Odo_Comment = Mid(Cells(i - 1, "H"), 11, 6)
        Start_Odo_Comment = Mid(Cells(i - 2, "K"), 12, 6)
        Start_Odo_Comment = Replace(Start_Odo_Comment, ",", "")
        
        For b = 6 To Quantity_Line_Items
            If Start_Odo_Comment = Cells(b, "K") Then
                Driver = Cells(b, "H")
                End_Odo_Comment = Cells(b, "N")
                Exit For
            End If
            If b = Quantity_Line_Items Then
                MsgBox "I can't find the Driver Name for one of the comments based on the start odometer of " & Start_Odo_Comment & ", I will enter it as Unknown."
                Driver = "Unknown"
            End If
        Next b
        Detection_Time = Mid(Cells(i + 3, "H"), 16, 5)
        Component = Cells(i - 1, "A")
        
            
        
        
        a = a + 1

        If a = 1 Then
            Comments_Path = Application.GetOpenFilename(Title:="Select the Comments Log", MultiSelect:=False)
            Set Comments_Path = Workbooks.Open(Comments_Path)
            Sheets(Vehicle_Number).Activate
          
            
        End If
        
        Comments_Path.Activate
        First_Comment_Row = Application.match("VEC. NO", ActiveWorkbook.Sheets(1).Columns("A"), 0) + 1
        For First_Blank_Comment_Row = First_Comment_Row To 4000
            If Cells(First_Blank_Comment_Row, "A") = "" And Cells(First_Blank_Comment_Row, "B") = "" And Cells(First_Blank_Comment_Row + 1, "A") = "" And Cells(First_Blank_Comment_Row + 2, "A") = "" Then
                Exit For
            End If
        Next First_Blank_Comment_Row
        
        Cells(First_Blank_Comment_Row, "H") = Date_of_Comment
        Cells(First_Blank_Comment_Row, "N") = PL_Number
        Cells(First_Blank_Comment_Row, "O") = OF_Number
        Cells(First_Blank_Comment_Row, "B") = Driver_Comment
        Cells(First_Blank_Comment_Row, "C") = Spec
        Cells(First_Blank_Comment_Row, "E") = Driver
        Cells(First_Blank_Comment_Row, "J") = Detection_Time
        Cells(First_Blank_Comment_Row, "L") = Odo_Comment
        Cells(First_Blank_Comment_Row, "K") = Start_Odo_Comment
        Cells(First_Blank_Comment_Row, "M") = End_Odo_Comment
        Cells(First_Blank_Comment_Row, "D") = Component
        Cells(First_Blank_Comment_Row, "A") = Vehicle_Number
        
        With Range(Cells(First_Blank_Comment_Row, "A"), Cells(First_Blank_Comment_Row, "R")).Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Range(Cells(First_Blank_Comment_Row, "A"), Cells(First_Blank_Comment_Row, "R")).Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Range(Cells(First_Blank_Comment_Row, "A"), Cells(First_Blank_Comment_Row, "R")).Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Range(Cells(First_Blank_Comment_Row, "A"), Cells(First_Blank_Comment_Row, "R")).Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Range(Cells(First_Blank_Comment_Row, "A"), Cells(First_Blank_Comment_Row, "R")).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        
        
         
    With Range(Cells(First_Blank_Comment_Row, "A"), Cells(i, "R"))
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'    With Range(Cells(First_Blank_Comment_Row, "A"), Cells(i, "R")).Font
 '       .Name = "Meiryo UI"
  '      .Size = 10
   '     .Strikethrough = False
    '    .Superscript = False
     '   .Subscript = False
      '  .OutlineFont = False
'        .Shadow = False
 '       .Underline = xlUnderlineStyleNone
  '      .ThemeColor = xlThemeColorLight1
   '     .TintAndShade = 0
    '    .ThemeFont = xlThemeFontNone
    'End With

    With Cells(First_Blank_Comment_Row, "B")
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   ' With Cells(First_Blank_Comment_Row, "B").Font
    '    .Name = "Meiryo UI"
     '   .Size = 11
      '  .Strikethrough = False
       ' .Superscript = False
   '     .Subscript = False
    '    .OutlineFont = False
     '   .Shadow = False
      '  .Underline = xlUnderlineStyleNone
       ' .ThemeColor = xlThemeColorLight1
   '     .TintAndShade = 0
    '    .ThemeFont = xlThemeFontNone
   ' End With
        
        


    End If
Next i

Log_Path.Activate
If a >= 1 Then Comments_Path.Activate

End Sub

 

[marcelocbd]

Have you debbuged your code? Like leftclicking a line for it to stop in that line and so you can check the variables?

 

[dan7055]

Have you debbuged your code? Like leftclicking a line for it to stop in that line and so you can check the variables?

Yes, many times, that is why I mentioned that when I hover over the variables when debugging, they both say the exact same thing, yet VBA is still saying the if statement is false

 

[marcelocbd]

Hummm.

When you define Start_Odo_Comment you take 6 charecters and then remove any commas. But you said the variables would be "8195" (4 characters).

Any chance one of them being " 8195" or "8195 " with a blank character before or after the real number?

 

[Joe4]

Also, be aware of the Floating Arithmetic Error that sometimes happens in Excel (see: https://support.microsoft.com/en-us/kb/78113)

You may want to try rounding your results, if they are both numeric, i.e.

[B]If Round(Start_Odo_Comment,0) = Round(Cells(b, "K"),0) Then[/B]

 

[marcelocbd]

Although you said you already tryed changing how the values are stored, I can't think of anything else to be causing the problem.
Try to force define it as a String by doing:

Start_Odo_Comment = [COLOR="#FF0000"]CStr[/COLOR](Mid(Cells(i - 2, "K"), 12, 6))
 Start_Odo_Comment = Replace(Start_Odo_Comment, ",", "")

For b = 6 To Quantity_Line_Items
 If Start_Odo_Comment = [COLOR="#FF0000"]CStr[/COLOR](Cells(b, "K") Then)
 Driver = Cells(b, "H")
 End_Odo_Comment = Cells(b, "N")
 Exit For
 End If
 If b = Quantity_Line_Items Then
 MsgBox "I can't find the Driver Name for one of the comments based on the start odometer of " & Start_Odo_Comment & ", I will enter it as Unknown."
 Driver = "Unknown"
 End If
 Next b

 

[Joe4]

If spaces are indeed the issue (as marcel pointed out), if they are normal spaces, they can be handled by the TRIM function, i.e.

If TRIM(Start_Odo_Comment) = TRIM([COLOR=#FF0000]CStr[/COLOR](Cells(b, "K"))) Then

Note: In Marcel's previous response, the last parenthesis should be before the word "THEN", not after it.

 

[marcelocbd]

If spaces are indeed the issue (as marcel pointed out), if they are normal spaces, they can be handled by the TRIM function, i.e.

If TRIM(Start_Odo_Comment) = TRIM([COLOR=#FF0000]CStr[/COLOR](Cells(b, "K"))) Then

Note: In Marcel's previous response, the last parenthesis should be before the word "THEN", not after it.

Oops, my bad. That's right.

 

[dan7055]

Okay thanks, I will try some of these ideas when I have time. I also thought I would try taking the contents of the cell and defining it as a string variable before comparing it in the If statement. I don't know why but that might work.

 

[xenou]

Okay thanks, I will try some of these ideas when I have time. I also thought I would try taking the contents of the cell and defining it as a string variable before comparing it in the If statement. I don't know why but that might work.

Not such a great idea but if you do so use the .Text property of the cell, not the value property (which means, what you SEE in the cell, not the actual contents of the cell).