PLEASE HELP!!! Loop through worksheets 2 - 26 and find blanks in columns 1, 2, 3, 4, 5, 6 with varying rows

brandonslee

New Member
Joined
Oct 15, 2015
Messages
1
Hi.
I'm quite a newbie w/ Excel VBA and need your help. I've struggled with this for quite a while and can't seem to figure it out.
I need a VBA code that runs through each row in columns 1 thru 6 and finds blank cells in columns 2 to 6 when there's a value for column 1 in the same row. I need to have this loop from worksheet 2 thru 26 with each having varying row counts.


Sub Check_Null()
'
' Check_Null Macro
'


'
Application.Goto Reference:="Check_Null"


Dim i As Long
Dim emptyA As Integer
Dim emptyB As Integer
Dim emptyC As Integer
Dim emptyD As Integer
Dim emptyE As Integer
Dim emptyF As Integer
Dim w As Integer
emptyB = 0
emptyC = 0
emptyD = 0
emptyE = 0
emptyF = 0


'2 to 26
For w = 2 To 3


For i = 1 To Rows.Count

If IsEmpty(Worksheets(w).Cells(i, 1).Value) Then
Worksheets(w).Cells(i, 1).Interior.ColorIndex = 6 And emptyA = emptyA + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 2).Value) Then
Worksheets(w).Cells(i, 2).Interior.ColorIndex = 6 And emptyB = emptyB + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 3).Value) Then
Worksheets(w).Cells(i, 3).Interior.ColorIndex = 6 And emptyC = emptyC + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 4).Value) Then
Worksheets(w).Cells(i, 4).Interior.ColorIndex = 6 And emptyD = emptyD + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 5).Value) Then
Worksheets(w).Cells(i, 5).Interior.ColorIndex = 6 And emptyE = emptyE + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 6).Value) Then
Worksheets(w).Cells(i, 6).Interior.ColorIndex = 6 And emptyF = emptyF + 1

End If

Next i

Next w


If emptyA > 0 Then
'MsgBox "You have a blank value in Column_1. Please check the highlighted rows in the Column_1"


ElseIf emptyB > 0 Then
'MsgBox "You have a blank value in Column_2."


ElseIf emptyC > 0 Then
'MsgBox "You have a blank value in Column_3."


ElseIf emptyD > 0 Then
'MsgBox "You have a blank value in Column_4."


ElseIf emptyE > 0 Then
'MsgBox "You have a blank value in Column_5."


ElseIf emptyF > 0 Then
'MsgBox "You have a blank value in Column_6."


End If


End Sub
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,254
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I need a VBA code that runs through each row in columns 1 thru 6 and finds blank cells in columns 2 to 6 when there's a value for column 1 in the same row.
Keep telling us in words what you want to do once you find these cells rather than have us trying to guess from your code that apparently doesn't work how you want anyway.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,127
Messages
5,526,991
Members
409,733
Latest member
revender17

This Week's Hot Topics

Top