Skipping Cells on a Loop

**sammclean23** · 09-22-2011, 12:22 AM

Hi All,

Just looking into finishing a script. Iv searched around on google and read some online books (which has got me to where I am) but just need to add one more bit to my script.

Basically, the script takes data from a bunch of cells and creates a string of text which represents the data from the cells. I think It may be called parsing cells for some reason.

Anyway, here is my code:

[vba]
Private Sub CommandButton1_Click()
Dim x As Integer
Dim x2 As Integer

'x is the Views row
'x2 is the Output roew

Dim ViewsSheet As Object
Dim OutputSheet As Object
Dim InsertStart As String
Dim InsertCmd As String
Dim DataSource As String

'declares the above as Objects or Strings

Set ViewsSheet = Worksheets("Views")
Set OutputSheet = Worksheets("Output")

'sets which sheet applies to which variable

x2 = 4
While OutputSheet.Cells(x2, 1) <> ""
OutputSheet.Cells(x2, 1) = ""
x2 = x2 + 1
Wend

'This begins a while loop which removes any text in column 1
'Clears worksheet Output

OutputSheet.Cells(4, 1) = "DELETE FROM MB_TABBUILD;"
x2 = 4
'adds the text to row 4 in the output sheet and resets the row to 4

InsertStart = "INSERT INTO MB_TABBUILD (MENU_ID, DATA_SOURCE) VALUES ("

x = 5 'start on row 5

While ViewsSheet.Cells(x, 1) <> "" 'loop until we find a blank cell in column 1

InsertCmd = InsertStart & ViewsSheet.Cells(x, 5) & ", '" 'add the menu id to the insert statement

'construct the DataSource string
DataSource = "Datastream: " 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & " " 'add a line feed
DataSource = DataSource & "Loader Script: " 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & " "
DataSource = DataSource & "Import Script: "
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & " "
DataSource = DataSource & "Import Schedule: "
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & " "

InsertCmd = InsertCmd & DataSource & ");"

OutputSheet.Cells(x, 1) = InsertCmd

x = x + 1

Wend

End Sub

[/vba]

All I want to do is add an IF statement that, when the loop is run, ignores any rows which has one piece of information missing. For example, if row 6 had the last column field missing, then ignore that row and do not add it to the string of text. But it could be any column, not just 6.

Hope it is clear what I want to achieve from the code above

Many thanks in advance,

Sam

**Bob Phillips** · 09-22-2011, 12:32 AM

[vba]

While ViewsSheet.Cells(x, 1) <> "" 'loop until we find a blank cell in column 1

If Application.CountIf(ViewsSheet.Cells(x, 5).Resize(, 4), "=") = 0 Then

InsertCmd = InsertStart & ViewsSheet.Cells(x, 5) & ", '" 'add the menu id to the insert statement

'construct the DataSource string
DataSource = "Datastream: " 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & " " 'add a line feed
DataSource = DataSource & "Loader Script: " 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & " "
DataSource = DataSource & "Import Script: "
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & " "
DataSource = DataSource & "Import Schedule: "
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & " "

InsertCmd = InsertCmd & DataSource & ");"

OutputSheet.Cells(x, 1) = InsertCmd
End If

x = x + 1
Wend
[/vba]

**sammclean23** · 09-22-2011, 12:40 AM

Thank you!

As I like to know how things work, could you please explain why the values are 5,4, and the purpose of.Resize and "=":
If Application.CountIf(ViewsSheet.Cells(x, 5).Resize(, 4), "=") = 0 Then

Thanks mate

**Bob Phillips** · 09-22-2011, 12:44 AM

5 because you data start in column 5 to check.

4 because you have 4 columns of data to check.

Resize resizes the original range (1 cell) by that value (4 columns).

"=" because it tests how many cells in that resized range are empty.

**sammclean23** · 09-22-2011, 12:50 AM

Thank you, appreciate the help!

**sammclean23** · 09-22-2011, 02:26 AM

One step further,

When the code does find empty cells, how could I highlight the cells that are empty?

I thought the below, but this doesnt seem to do anything at all

[VBA]
Range(x, 5).Interior.ColorIndex = 2
[/VBA]

**Bob Phillips** · 09-22-2011, 03:13 AM

Use Cells not Range

**sammclean23** · 09-22-2011, 05:48 AM

Okay iv modified the code a bit the color highlight part does not work at all, the rest of the code is fine and does as intended:

Bascially what I want is - if any row in column D or E is empty, then highlight the cell.

It is the IF statement part I am having trouble with

[vba]
Private Sub CommandButton1_Click()
Dim x As Integer
Dim x2 As Integer

'x is the Views row
'x2 is the Output roew

Dim ViewsSheet As Object
Dim OutputSheet As Object
Dim InsertStart As String
Dim InsertCmd As String
Dim DataSource As String

'declares the above as Objects or Strings

Set ViewsSheet = Worksheets("Views")
Set OutputSheet = Worksheets("Output")

'sets which sheet applies to which variable

x2 = 4
While OutputSheet.Cells(x2, 1) <> ""
OutputSheet.Cells(x2, 1) = ""
x2 = x2 + 1
Wend

'This begins a while loop which removes any text in column 1
'Clears worksheet Output

OutputSheet.Cells(4, 1) = "DELETE FROM MB_TABBUILD;"
x2 = 4
'adds the text to row 4 in the output sheet and resets the row to 4

InsertStart = "INSERT INTO MB_TABBUILD (MENU_ID, DATA_SOURCE) VALUES ("

x = 5 'start on row 5

If ViewsSheet.Cells(x, 5) = "" And ViewsSheet.Cells(x, 4) = "" Then

ViewsSheet.Cells(x, 5).Interior.ColorIndex = 2

Else

While ViewsSheet.Cells(x, 1) <> "" 'loop until we find a blank cell in column 1

InsertCmd = InsertStart & ViewsSheet.Cells(x, 5) & ", '" 'add the menu id to the insert statement

'construct the DataSource string
DataSource = "Datastream: " 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & " " 'add a line feed
DataSource = DataSource & "Loader Script: " 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & " "
DataSource = DataSource & "Import Script: "
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & " "
DataSource = DataSource & "Import Schedule: "
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & " "

InsertCmd = InsertCmd & DataSource & ");"

OutputSheet.Cells(x, 1) = InsertCmd

x = x + 1

Wend
End If

End Sub
[/vba]

I had to change the code you suggested as it caused the program to stop responding, anyway, the modified one works. However, I cant change the color of cells using what I have tried. Maybe because im using the wrong values for thew column.

Thanks again in advance, and thank you for your help so far

**mancubus** · 09-22-2011, 07:20 AM

Originally Posted by sammclean23

Bascially what I want is - if any row in column D or E is empty, then highlight the cell.

[VBA]
Range("D5

" & Cells(Rows.Count, "D").End(xlUp).Row). _
SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 2

Range("E5:E" & Cells(Rows.Count, "E").End(xlUp).Row). _
SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 2
[/VBA]

**sammclean23** · 09-22-2011, 07:41 AM

Thanks will give it a try!

Though I still cant see why this wouldnt work

[VBA]
If ViewsSheet.Cells(x, 5) = "" And ViewsSheet.Cells(x, 4) = "" Then

ViewsSheet.Cells(x, 5).Interior.ColorIndex = 2
[/VBA]

**mancubus** · 09-22-2011, 08:45 AM

you're wellcome.

search for "vba logical operators."

with AND operator we're saying "do stg if both conditions are met at the same time."

you're saying that (for x = 6)

if both D6 and E6 are blanks then
make E6's fill color white
else
do stg else
...

"if any row in column D or E is empty, then highlight the cell."
look through column D from D5 to last cell with data in the same column. if there are any blank cells then highlight them.
then look through column E from E5 to last cell with data in the same column. if there are any blank cells then highlight them.

specialcells method enables selecting special cells. Blanks are one of them.

in a worksheet, press "F5" key, hit "Special" button, click "Blanks" then "OK".
you will see all the blank cells in the used range are selected.
(you may try with a manuel selection of ranges.)

VBA equivalent is Range("D5: D5000").SpecialCells(xlCellTypeBlanks)

you must take into account the limit of special cells, if you are dealing with huge data...
see: http://www.rondebruin.nl/specialcells.htm

**sammclean23** · 09-23-2011, 12:36 AM

Thanks a lot for the explanation there. I reliase what you mean with the AND operator.

Just had a look at the specialcells too. Thanks a lot.

**sammclean23** · 09-23-2011, 01:27 AM

Okay im struggling with this now. Back to basics.

Just opened a completely blank excel sheet and did the following:
[VBA]

Sub Highlight()
If Range("C6").Value = "" Then
Range("C6").Activate
ActiveCell.Interior.ColorIndex = 10
End If
End Sub
[/VBA]

Very very simple. Now just so I can try and get my head around this. How would I then say "IF any cell in column C is blank, highlight the cell"

I have been advised to try and use numbers for the columns such as (x,5) so examples in this format would be excellent, although not neccesary as I just want to try and unserstand this.

Thank you in advance, this forum has been great with helping me out!

**Bob Phillips** · 09-23-2011, 01:32 AM

[vba]

Sub Highlight()
Dim cell As Range

For Each cell In Range(Range("C2"), Range("C2").End(xlDown))

If cell.Value = "" Then

cell.Interior.ColorIndex = 10
End If
Next cell
End Sub
[/vba]

**sammclean23** · 09-23-2011, 01:38 AM

thankyou, that works, but stops highlighting when it reaches a populated cell?

**Bob Phillips** · 09-23-2011, 01:47 AM

Oops, try this

[vba]

Sub Highlight()
Dim lastrow As Long
Dim i As Long

lastrow = Cells(Rows.Count, "C").End(xlUp).Row

For i = 2 To lastrow

If Cells(i, "C").Value.Value = "" Then

Cells(i, "C").Interior.ColorIndex = 10
End If
Next i
End Sub
[/vba]

**sammclean23** · 09-23-2011, 01:49 AM

This returns "Object Required". Had a look myself but cant see why

**Bob Phillips** · 09-23-2011, 01:50 AM

Two Values, change the .Value.Value in line 9 to just .Value

**sammclean23** · 09-23-2011, 02:03 AM

Brilliant! thanks helps a lot to understanding how it works.

Now back to my primary objective:

If any row in column D or E is blank, then highlight the blank cell under either column or both (whichever is blank). Else.....and the rest of my code continues.

I have a feeling that i what I want to do is not the most simplest. I have tried to incorporate the above into my code but struggling

Im guessing it is just a variation of

[vba]
While ViewsSheet.Cells(x, 1) <> "" 'loop until we find a blank cell in column 1

If Application.CountIf(ViewsSheet.Cells(x, 5).Resize(, 4), "=") = 0 Then

InsertCmd = InsertStart & ViewsSheet.Cells(x, 5) & ", '" 'add the menu id to the insert statement

'construct the DataSource string
DataSource = "Datastream: " 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & " " 'add a line feed
DataSource = DataSource & "Loader Script: " 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & " "
DataSource = DataSource & "Import Script: "
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & " "
DataSource = DataSource & "Import Schedule: "
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & " "

InsertCmd = InsertCmd & DataSource & ");"

OutputSheet.Cells(x, 1) = InsertCmd
End If

x = x + 1
Wend
[/vba]

Though something makes me think I will require multiple IF statements?

**Bob Phillips** · 09-23-2011, 02:14 AM

Do you mean

[vba]

With ViewsSheet

lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row

For x = 1 To lastrow

If .Cells(x, "D").Value = "" Then

.Cells(x, "D").Interior.ColorIndex = 10
End If

If .Cells(x, "E").Value = "" Then

.Cells(x, "e").Interior.ColorIndex = 10
End If

If Application.CountIf(.Cells(x, 5).Resize(, 4), "=") = 0 Then

InsertCmd = InsertStart & .Cells(x, 5) & ", '" 'add the menu id to the insert statement

'construct the DataSource string
DataSource = "Datastream: " 'add the text "Datastream: " in bold
DataSource = DataSource & .Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & " " 'add a line feed
DataSource = DataSource & "Loader Script: " 'add the text "Loader Script: " in bold
DataSource = DataSource & .Cells(x, 8)
DataSource = DataSource & " "
DataSource = DataSource & "Import Script: "
DataSource = DataSource & .Cells(x, 6)
DataSource = DataSource & " "
DataSource = DataSource & "Import Schedule: "
DataSource = DataSource & .Cells(x, 7)
DataSource = DataSource & " "

InsertCmd = InsertCmd & DataSource & ");"

OutputSheet.Cells(x, 1) = InsertCmd
End If
Next x
End With[/vba]

Thread: Skipping Cells on a Loop

Thread Tools

Display

Skipping Cells on a Loop

Posting Permissions