VBA check 2 columns for text

Question

I have a sheet, that has 2 columns and 36 rows. Macro should do these things: 1) if cell in row 1, column 1 are not empty and cell in row 1, column 2 is empty macro should stop, else it should continue and after checking all 36 rows it should stop and do SaveAs, if no such values are found. I found this code and modified a little bit, but it doesn't work as I described:

Sub CheckRows()
    Dim i As Long
    For i = 12 To 47
        'Criteria search
        If Sheets("Claims").Cells(i, 2).Value <> "" Then
            If Sheets("Claims").Cells(i, 3).Value = "" Then
                        Exit Sub
                    Else
            End If
        End If
    Next i
  ActiveWorkbook.SaveAs Filename:="myFile.xlsx", FileFormat:=56
End Sub

Could anyone help me out and tell what's wrong with the code? Thanks

I do not remember exactly how cells are indexed in XL VBA - you wrote about columns 1 and 2, but the macro deals with columns 2 and 3? Also check if the rows 12-47 are correct ones. You could debug the macro to see what's going wrong — Mixaz, Nov 16 '14 at 20:12
Also you could consider using IsEmpty(), it is better than comparing with empty string (and more correct by the way) http://stackoverflow.com/questions/13360651/excel-how-to-check-if-a-cell-is-empty-with-vba — Mixaz, Nov 16 '14 at 20:17

score 0 · Answer 1 · answered Nov 16 '14 at 20:18

your looking in column 2 and 3 ,not column 1 and 2 as you wrote... also your starting at row 12, is this correct ?

Sub CheckRows()
    Dim i As Long
    For i = 12 To 47
        'Criteria search
        If Sheets("Claims").Cells(i, 1).Value <> "" Then
            If Sheets("Claims").Cells(i, 2).Value = "" Then
                        Exit Sub

            End If
        End If
    Next i
  ActiveWorkbook.SaveAs Filename:="myFile.xlsx", FileFormat:=56
End Sub

VBA check 2 columns for text

1 Answers1