PDA

View Full Version : 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:


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 = "<b>Datastream: </b>" 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & "<br>" 'add a line feed
DataSource = DataSource & "<b>Loader Script: </b>" 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Script: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Schedule: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & "<br>"



InsertCmd = InsertCmd & DataSource & ");"

OutputSheet.Cells(x, 1) = InsertCmd

x = x + 1

Wend


End Sub



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
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 = "<b>Datastream: </b>" 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & "<br>" 'add a line feed
DataSource = DataSource & "<b>Loader Script: </b>" 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Script: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Schedule: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & "<br>"

InsertCmd = InsertCmd & DataSource & ");"

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

x = x + 1
Wend

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


Range(x, 5).Interior.ColorIndex = 2

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


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 = "<b>Datastream: </b>" 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & "<br>" 'add a line feed
DataSource = DataSource & "<b>Loader Script: </b>" 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Script: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Schedule: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & "<br>"



InsertCmd = InsertCmd & DataSource & ");"

OutputSheet.Cells(x, 1) = InsertCmd

x = x + 1

Wend
End If

End Sub


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
Bascially what I want is - if any row in column D or E is empty, then highlight the cell.




Range("D5:D" & 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

sammclean23
09-22-2011, 07:41 AM
Thanks will give it a try!

Though I still cant see why this wouldnt work


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

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

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:


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


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
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

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



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

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 :banghead:

Im guessing it is just a variation of


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 = "<b>Datastream: </b>" 'add the text "Datastream: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & "<br>" 'add a line feed
DataSource = DataSource & "<b>Loader Script: </b>" 'add the text "Loader Script: " in bold
DataSource = DataSource & ViewsSheet.Cells(x, 8)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Script: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 6)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Schedule: </b>"
DataSource = DataSource & ViewsSheet.Cells(x, 7)
DataSource = DataSource & "<br>"

InsertCmd = InsertCmd & DataSource & ");"

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

x = x + 1
Wend


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

Bob Phillips
09-23-2011, 02:14 AM
Do you mean



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 = "<b>Datastream: </b>" 'add the text "Datastream: " in bold
DataSource = DataSource & .Cells(x, 5) 'add the datastream value in column 5
DataSource = DataSource & "<br>" 'add a line feed
DataSource = DataSource & "<b>Loader Script: </b>" 'add the text "Loader Script: " in bold
DataSource = DataSource & .Cells(x, 8)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Script: </b>"
DataSource = DataSource & .Cells(x, 6)
DataSource = DataSource & "<br>"
DataSource = DataSource & "<b>Import Schedule: </b>"
DataSource = DataSource & .Cells(x, 7)
DataSource = DataSource & "<br>"

InsertCmd = InsertCmd & DataSource & ");"

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

sammclean23
09-23-2011, 02:39 AM
Yes! However this returns "subscript out of range"

But it is along those lines yes! Thank you

Edit: worked it out:)

Bob Phillips
09-23-2011, 09:53 AM
AT which point? Any chnace of uploading a workbook?

Bob Phillips
09-23-2011, 01:10 PM
Post your solution for the archives.

sammclean23
09-24-2011, 02:35 PM
worked it out :) many thanks for the help!

I will post the solution monday as it is stored on computer at work,

Thanks again for the help

sammclean23
09-26-2011, 03:16 AM
The solution


While ViewsSheet.Cells(x, 1) <> "" 'loop until we find a blank cell in column 1
If ViewsSheet.Cells(x, 4) = "" Or ViewsSheet.Cells(x, 5) = "" Then

If ViewsSheet.Cells(x, 4) = "" Then
ViewsSheet.Cells(x, 4).Interior.ColorIndex = 8
End If

If ViewsSheet.Cells(x, 5) = "" Then
ViewsSheet.Cells(x, 5).Interior.ColorIndex = 8
End If

Else