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:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top