Macro won't recognize value in cell that has If statement

[Liberteric]

I've been running the macro below manually. I would like a macro to test column A which contains an If statement IF(B1=E1,1,0) on each row until row A is empty.
I tried a suggestion from here before and if the value was 0, it called up this macro but the macro saw the value of 1 as a 0. If you can help, that would be great.


Sub AddLine()
' Addline Macro
'
'
Application.ScreenUpdating = False
Application.CutCopyMode = False
Range(Selection, Selection.End(xlToRight).Offset(, 13)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, -4).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 1).Select
' Start New GL Code list
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("NEWGL").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Sheets("TB").Select
Application.CutCopyMode = False
'Fill New Code data
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(, 2).Select
ActiveCell.FormulaR1C1 = "0"
Application.CutCopyMode = False
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(, -1).Select
Selection.ClearContents
Application.CutCopyMode = False

'Get Record location
ActiveCell.Offset(, 1).Select
ActiveCell.Formula = "=CELL(""address"")"
Application.CutCopyMode = False
ActiveCell.Offset(, -3).Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(, 3).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Sheets("NEWGL").Select
Range("D1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Sheets("TB").Select

'Return to column E
Application.CutCopyMode = False
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(, 4).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Joe4]

One thing that stands out. Look at this line:

[COLOR=#333333]ActiveCell.FormulaR1C1 = "0"[/COLOR]

This returns a text value of 0 (anything enclosed in double-quotes is treated as literal text).

Now look at your formula:

[COLOR=#333333]=IF(B1=E1,1,0)[/COLOR]

This returns a numeric value of 0.

Note that the two are NOT equal (a text value of "0" is not the same as the numeric value of zero).
You will want to make them both numeric or both text if you want to compare them.

 

[Liberteric]

One thing that stands out. Look at this line:

[COLOR=#333333]ActiveCell.FormulaR1C1 = "0"[/COLOR]

This returns a text value of 0 (anything enclosed in double-quotes is treated as literal text).

Now look at your formula:

[COLOR=#333333]=IF(B1=E1,1,0)[/COLOR]

This returns a numeric value of 0.

Note that the two are NOT equal (a text value of "0" is not the same as the numeric value of zero).
You will want to make them both numeric or both text if you want to compare them.

That macro is supposed to put text in it, and either way, that isn't the cell that's being tested. The If statement returns 1, but the macro I had written before recognized it as a 0 so I went back to running this macro manually. I want to write a macro that compares the result in A, and if it equals 0, then run the macro I have listed, if not, keep checking each row until it hits the end.

 

[Joe4]

Sorry, I misunderstood. Try something like this:

Sub MyMacro()


    Dim cell As Range
    
    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        If (Len(cell) = 1) And (cell.Value = 0) Then Call AddLine
    Next cell
        
End Sub

Also, you can clean-up your original code a bit. Using many of the Select and ActiveCell statements are usually not necessary, especially when you have one line that ends with ".Select" and the next line begins with "Selection.". Those lines can usually be combined into one line. This will help your code run a little faster (and be less lines of code).

Here is an example. This three lines:

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select

can be condensed down to this:

Range("A1").End(xlDown).Offset(1).Select

 

[Liberteric]

Sorry, I misunderstood. Try something like this:

Sub MyMacro()


    Dim cell As Range
    
    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        If (Len(cell) = 1) And (cell.Value = 0) Then Call AddLine
    Next cell
        
End Sub

Also, you can clean-up your original code a bit. Using many of the Select and ActiveCell statements are usually not necessary, especially when you have one line that ends with ".Select" and the next line begins with "Selection.". Those lines can usually be combined into one line. This will help your code run a little faster (and be less lines of code).

Here is an example. This three lines:

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select

can be condensed down to this:

Range("A1").End(xlDown).Offset(1).Select

Thanks for the coding tip. I'm new to this. Your macro, and everyone's macro for that matter doesn't recognize the value of column A as 1. It runs the addline macro. I've tried just typing in the
"1" as a number and as text but no macro that refers to it sees it as a 1. I just thought I'd give it a shot again. I'm beginning to think it's a data problem, but I have no idea in what way. Thank you for your effort. I really appreciate it.

 

[Joe4]

Are you dealing with multiple sheets here? Are you sure it is on the correct sheet when it is running?
Where have you placed the VBA code? Is it in a Standard Module, or a Worksheet module?

Try this, this will tell you the value of the cell in a Message Box, so you can see exactly what it thinks the value is while it is running:

Sub MyMacro()


    Dim cell As Range
    
    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        MsgBox "The value in cell " & cell.Address & " is " & cell.Text
        If (Len(cell) = 1) And (cell.Value = 0) Then Call AddLine
    Next cell
        
End Sub

 

[Liberteric]

This actually worked. It recognized when I got to the first line that=0 or false in column a and ran the macro. I can't do this with a message box though. Is there a way to run this in column E until I run out of data? Thank you so much. This is the first suggestion that actually worked. I look forward to your response.

 

[Liberteric]

I apologize. I neglected to tell you that my AddLine macro must be run in column E. I don't know if that matters or not. Again, thank you for your time and tips. combining movement commands in one select is going to save me lots of time.

 

[Joe4]

This actually worked. It recognized when I got to the first line that=0 or false in column a and ran the macro. I can't do this with a message box though. Is there a way to run this in column E until I run out of data? Thank you so much. This is the first suggestion that actually worked. I look forward to your response.

The message box was just a way for us to see what the macro thought the value of the cell was (this is just a method of debugging our code, looking for errors).

If you wanted it to run on column E, you would just alter the code I provided previously, changing columns A to E, and you probably need to select the cell in column E, since your AddLine macro runs on whatever cell is selected when it is called, i.e.

Sub MyMacro()


    Dim cell As Range
    
    For Each cell In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        If (Len(cell) = 1) And (cell.Value = 0) Then
            cell.Select
            Call AddLine
        End If
    Next cell
        
End Sub

 

[Liberteric]

The message box was just a way for us to see what the macro thought the value of the cell was (this is just a method of debugging our code, looking for errors).

If you wanted it to run on column E, you would just alter the code I provided previously, changing columns A to E, and you probably need to select the cell in column E, since your AddLine macro runs on whatever cell is selected when it is called, i.e.

Sub MyMacro()


    Dim cell As Range
    
    For Each cell In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        If (Len(cell) = 1) And (cell.Value = 0) Then
            cell.Select
            Call AddLine
        End If
    Next cell
        
End Sub

We're getting somewhere. Running it as above did nothing at all. I didn't run. I replaced the "E"'s with "A"'s and it moved down to the first row where column A=0, but it ran AddLine from column A rather than from E. It also stopped running.