CODEWITHSUNDEEP
vbaexcel
SIM
SIM

Reputation: 22440

Unable to make conditional statement accurately

I'm trying to create a macro which will take values (conditionally) from a worksheet and print it in the immediate window. I wish my macro will pick only those values which are just 0 in Range("A1") and then it will look for two columns offset (in the same row) to see if the field is blank. If it is then it will print the values of Range("A1") and Range("B1"). If the condition is not met it will simply ignore the row and go for the next row to get the right match until it reaches Range"A4"). Where I'm going wrong? Thanks in advance.

This is what I'm trying with:

Sub testing()
   Dim cNo As String, cName As String

   For Each cel In Range("A1:A4")
           If cel.Value = 0 And cel.Offset(, 2) = "" Then
                cNo = cel.Value
                cName = Replace(cel.Offset(0, 1).Value, " ", "+")
            End If

            Debug.Print cNo, cName
    Next cel
End Sub

And I'm trying on this values:

ColumnA  ColumnB        ColumnC
0        HERSHE ST  
2816     SHERWICK ST    FRANKLIN JANICE
8034     ANTOINE DR     MALDONADO JESUS
0        ELLINGTON ST

This macro is printing the following (repeating the first one until another match):

0             HERSHE+ST
0             HERSHE+ST
0             HERSHE+ST
0             ELLINGTON+ST

My expected output:

0             HERSHE+ST


0             ELLINGTON+ST

Upvotes: 0

Views: 20

Answers (1)

SJR
SJR

Reputation: 23081

As I say, not sure why you would use the Immediate Window, but to get the results as you want you need to include the results within your If statement and add an Else clause which inserts a blank row if the condition is not met.

Option Explicit

Sub testing()

Dim cNo As String, cName As String, cel As Range

For Each cel In Range("A1:A4")
    If cel.Value = 0 And cel.Offset(, 2) = "" Then
        cNo = cel.Value
        cName = Replace(cel.Offset(0, 1).Value, " ", "+")
        Debug.Print cNo, cName
    Else
        Debug.Print ""
    End If
Next cel

End Sub

Upvotes: 1

Related Questions