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 = "<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
[/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