Help with messed-up code!!!

S

Sandy

Hello -

I hope someone can help me on this one -- this project was due yesterday!
Scenario: If a cell in G contains "No" or "N/A" then check Column I to make
sure there's text in there. If Cell G contains a "Yes," then Col. I can be
blank. Return msgboxes for different scenarios when a button is clicked.

There is a msgbox that works that is generic (I have marked it below), but
these people decided they need to have more detail.

On testing, MsgBox #1 and #2 work. When I get to #3, it gives me the error
"object variable or with block not set." It points to the line in code which
is the condition for MsgBox #1 which is:
If numBlanks > 0 And sStr <> "" And blank_cells.Count > 0 And sStrI <>
"" Then

Also, if I fill the entire worksheet in correctly, I get the same error (in
other words, I should have gotten the "Congratulations" message. Conversely,
if the columns are completely blank and I click on the button, I should get a
list of each blank item, but I get an error. What am I messing up here
(other than the entire piece of code)? This is driving me crazy!!!! Any
help will be EXTREMELY appreciated!

Dim cell As Range
Dim Grng As Range
Dim ICell As Range
Dim blank_cells As Range
Dim sStrI As String
Dim rngA As Range
Dim rngB As Range
Dim j As Long, i As Long
j = 0
sStrI = ""

With ActiveSheet

Set rngA = .Range("G9, G13:G19, G22, G25:G26, G30, G35, G39, G43," & _
"G50, G51, G54:G58, G65, G68, G71, G74, G82, G83, G87," & _
"G88, G98, G99, G104, G110, G114, G118:G120, G122")
Set rngB = .Range("G126:G128, G130, G131, G135, G139, G142, G145, G148,"
& _
"G149, G153, G155, G159, G163, G164, G167, G169, G171," & _
"G176:G179, G189, G191, G194, G195")
Set Grng = Union(rngA, rngB)

For Each cell In Grng

Set ICell = .Cells(cell.Row, "I")

If (cell.Value = "No" Or cell.Value = "N/A" Or cell.Value = "") And _
(ICell.Text = False Or IsEmpty(ICell) Or ICell.Value = "") Then
If blank_cells Is Nothing Then
Set blank_cells = ICell
Else
Set blank_cells = Union(blank_cells, ICell)
sStrI = blank_cells.Address(0, 0) & ","
End If
End If
Next cell
sStrI = Left(sStrI, Len(sStrI) - 1)
For i = 1 To Len(sStrI)
If Mid(sStrI, i, 1) = "," Then
j = j + 1
If j = 6 Then
Mid(sStrI, i, 1) = vbCrLf
j = 0
End If
End If
Next

For Each c In Grng.Cells
If c.Value = "" Or c.Value = "0" Then
numBlanks = numBlanks + 1
End If
Next c

End With
Dim sStr As String
Dim rRng As Range
Dim rCell As Range

sStr = ""
For Each rCell In Grng.Cells
If rCell.Value = "" Or rCell.Value = "0" Then
sStr = sStr & rCell.Address(0, 0) & ","
End If
Next rCell
sStr = Left(sStr, Len(sStr) - 1)

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 6 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next
'***********
'MsgBox ("Number of blank cells: " & numBlanks _
& vbCrLf & vbCrLf & "Blank cell(s): " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "All blanks must be filled in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
'***************
'THIS MSGBOX WORKS
'If numBlanks > 0 Or sStr <> "" Or blank_cells.Count > 0 Or sStrI <> "" Then
' MsgBox "Number of blanks in G: " & numBlanks & vbCrLf & vbCrLf & _
' "Blank cell(s) in G: " & vbCrLf & sStr & vbCrLf & vbCrLf & _
' "Number of blanks in I: " & blank_cells.Count & vbCrLf & vbCrLf & _
' "Blank cell(s) in I: " & vbCrLf & sStrI
'***************
'#1 If blanks in both G and I
If numBlanks > 0 And sStr <> "" And blank_cells.Count > 0 And sStrI <>
"" Then
MsgBox ("Warning 1 - " & numBlanks & " question(s) remain(s)
unanswered." & vbCrLf _
& "Return to AUDIT tab to identify blank cell(s). " _
& vbCrLf & vbCrLf _
& "Warning - " & blank_cells.Count & " cell(s) require(s) a reason for
an N/A or No Response. " _
& vbCrLf & "Cell(s): " & sStrI), [vbOKOnly], ["Blank Cells!"]
'#2If all of G answered, but I incomplete
ElseIf numBlanks = 0 And sStr = "" And blank_cells.Count > 0 And sStrI
<> "" Then
MsgBox ("Warning - 2 " & blank_cells.Count & " cell(s) require(s) a
reason for an N/A or No Response." _
& vbCrLf & "Cell(s): " & sStrI), [vbOKOnly], ["Blank Cells!"]

'#3 If blanks in G but none in I (numBlanks = X, They are: sStr)
ElseIf numBlanks > 0 And sStr <> "" And blank_cells.Count = 0 And sStrI
<> "" Then
MsgBox ("Warning - 3 " & numBlanks & " question(s) remain(s) unanswered.
" & vbCrLf _
& "Return to AUDIT tab " _
& " to identify blank cell(s). "), [vbOKOnly], ["Blank Cells!"]

ElseIf numBlanks = 0 And blank_cells.Count = 0 Then
MsgBox ("Congratulations! This file is ready to upload " & vbCrLf _
& "using the Web Tool."), [vbOKOnly], ["Congratulations!"]
Else MsgBox "There is an error in my code"
End If
 
P

Peter T

Hi Sandy,

I haven't tried your code but at a glance I expect the range var
"blank_cells" has not been set. It's probably better to figure out why not,
but in the mean time adapt with something like this

Dim lBlankCells as long

'code
If blank_cells is nothing then
lBlankCells = 0
else
lBlankCells = blank_cells.count
End if

Then in your Msgbox's substitute blank_cells.count with lBlankCells

In passing, this line cought my attention
sStrI = Left(sStrI, Len(sStrI) - 1)
Click to expand...

At this point are you sure sStrI cannot be an empty string, if so the Left
function would fail. What occurs if all cells pass the test.

Regards,
Peter T

Sandy said:
Hello -

I hope someone can help me on this one -- this project was due yesterday!
Scenario: If a cell in G contains "No" or "N/A" then check Column I to make
sure there's text in there. If Cell G contains a "Yes," then Col. I can be
blank. Return msgboxes for different scenarios when a button is clicked.

There is a msgbox that works that is generic (I have marked it below), but
these people decided they need to have more detail.

On testing, MsgBox #1 and #2 work. When I get to #3, it gives me the error
"object variable or with block not set." It points to the line in code which
is the condition for MsgBox #1 which is:
If numBlanks > 0 And sStr <> "" And blank_cells.Count > 0 And sStrI <>
"" Then

Also, if I fill the entire worksheet in correctly, I get the same error (in
other words, I should have gotten the "Congratulations" message. Conversely,
if the columns are completely blank and I click on the button, I should get a
list of each blank item, but I get an error. What am I messing up here
(other than the entire piece of code)? This is driving me crazy!!!! Any
help will be EXTREMELY appreciated!

Dim cell As Range
Dim Grng As Range
Dim ICell As Range
Dim blank_cells As Range
Dim sStrI As String
Dim rngA As Range
Dim rngB As Range
Dim j As Long, i As Long
j = 0
sStrI = ""

With ActiveSheet

Set rngA = .Range("G9, G13:G19, G22, G25:G26, G30, G35, G39, G43," & _
"G50, G51, G54:G58, G65, G68, G71, G74, G82, G83, G87," & _
"G88, G98, G99, G104, G110, G114, G118:G120, G122")
Set rngB = .Range("G126:G128, G130, G131, G135, G139, G142, G145, G148,"
& _
"G149, G153, G155, G159, G163, G164, G167, G169, G171," & _
"G176:G179, G189, G191, G194, G195")
Set Grng = Union(rngA, rngB)

For Each cell In Grng

Set ICell = .Cells(cell.Row, "I")

If (cell.Value = "No" Or cell.Value = "N/A" Or cell.Value = "") And _
(ICell.Text = False Or IsEmpty(ICell) Or ICell.Value = "") Then
If blank_cells Is Nothing Then
Set blank_cells = ICell
Else
Set blank_cells = Union(blank_cells, ICell)
sStrI = blank_cells.Address(0, 0) & ","
End If
End If
Next cell
sStrI = Left(sStrI, Len(sStrI) - 1)
For i = 1 To Len(sStrI)
If Mid(sStrI, i, 1) = "," Then
j = j + 1
If j = 6 Then
Mid(sStrI, i, 1) = vbCrLf
j = 0
End If
End If
Next

For Each c In Grng.Cells
If c.Value = "" Or c.Value = "0" Then
numBlanks = numBlanks + 1
End If
Next c

End With
Dim sStr As String
Dim rRng As Range
Dim rCell As Range

sStr = ""
For Each rCell In Grng.Cells
If rCell.Value = "" Or rCell.Value = "0" Then
sStr = sStr & rCell.Address(0, 0) & ","
End If
Next rCell
sStr = Left(sStr, Len(sStr) - 1)

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 6 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next
'***********
'MsgBox ("Number of blank cells: " & numBlanks _
& vbCrLf & vbCrLf & "Blank cell(s): " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "All blanks must be filled in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
'***************
'THIS MSGBOX WORKS
'If numBlanks > 0 Or sStr <> "" Or blank_cells.Count > 0 Or sStrI <> "" Then
' MsgBox "Number of blanks in G: " & numBlanks & vbCrLf & vbCrLf & _
' "Blank cell(s) in G: " & vbCrLf & sStr & vbCrLf & vbCrLf & _
' "Number of blanks in I: " & blank_cells.Count & vbCrLf & vbCrLf & _
' "Blank cell(s) in I: " & vbCrLf & sStrI
'***************
'#1 If blanks in both G and I
If numBlanks > 0 And sStr <> "" And blank_cells.Count > 0 And sStrI <>
"" Then
MsgBox ("Warning 1 - " & numBlanks & " question(s) remain(s)
unanswered." & vbCrLf _
& "Return to AUDIT tab to identify blank cell(s). " _
& vbCrLf & vbCrLf _
& "Warning - " & blank_cells.Count & " cell(s) require(s) a reason for
an N/A or No Response. " _
& vbCrLf & "Cell(s): " & sStrI), [vbOKOnly], ["Blank Cells!"]
'#2If all of G answered, but I incomplete
ElseIf numBlanks = 0 And sStr = "" And blank_cells.Count > 0 And sStrI
<> "" Then
MsgBox ("Warning - 2 " & blank_cells.Count & " cell(s) require(s) a
reason for an N/A or No Response." _
& vbCrLf & "Cell(s): " & sStrI), [vbOKOnly], ["Blank Cells!"]

'#3 If blanks in G but none in I (numBlanks = X, They are: sStr)
ElseIf numBlanks > 0 And sStr <> "" And blank_cells.Count = 0 And sStrI
<> "" Then
MsgBox ("Warning - 3 " & numBlanks & " question(s) remain(s) unanswered.
" & vbCrLf _
& "Return to AUDIT tab " _
& " to identify blank cell(s). "), [vbOKOnly], ["Blank Cells!"]

ElseIf numBlanks = 0 And blank_cells.Count = 0 Then
MsgBox ("Congratulations! This file is ready to upload " & vbCrLf _
& "using the Web Tool."), [vbOKOnly], ["Congratulations!"]
Else MsgBox "There is an error in my code"
End If
Click to expand...
 
S

Sandy

Hi Peter -

Thank you so much! You put me on the right track. The code is working now.
Whew!!

--
Sandy


Peter T said:
Hi Sandy,

I haven't tried your code but at a glance I expect the range var
"blank_cells" has not been set. It's probably better to figure out why not,
but in the mean time adapt with something like this

Dim lBlankCells as long

'code
If blank_cells is nothing then
lBlankCells = 0
else
lBlankCells = blank_cells.count
End if

Then in your Msgbox's substitute blank_cells.count with lBlankCells

In passing, this line cought my attention
sStrI = Left(sStrI, Len(sStrI) - 1)
Click to expand...

At this point are you sure sStrI cannot be an empty string, if so the Left
function would fail. What occurs if all cells pass the test.

Regards,
Peter T

Sandy said:
Hello -

I hope someone can help me on this one -- this project was due yesterday!
Scenario: If a cell in G contains "No" or "N/A" then check Column I to make
sure there's text in there. If Cell G contains a "Yes," then Col. I can be
blank. Return msgboxes for different scenarios when a button is clicked.

There is a msgbox that works that is generic (I have marked it below), but
these people decided they need to have more detail.

On testing, MsgBox #1 and #2 work. When I get to #3, it gives me the error
"object variable or with block not set." It points to the line in code which
is the condition for MsgBox #1 which is:
If numBlanks > 0 And sStr <> "" And blank_cells.Count > 0 And sStrI <>
"" Then

Also, if I fill the entire worksheet in correctly, I get the same error (in
other words, I should have gotten the "Congratulations" message. Conversely,
if the columns are completely blank and I click on the button, I should get a
list of each blank item, but I get an error. What am I messing up here
(other than the entire piece of code)? This is driving me crazy!!!! Any
help will be EXTREMELY appreciated!

Dim cell As Range
Dim Grng As Range
Dim ICell As Range
Dim blank_cells As Range
Dim sStrI As String
Dim rngA As Range
Dim rngB As Range
Dim j As Long, i As Long
j = 0
sStrI = ""

With ActiveSheet

Set rngA = .Range("G9, G13:G19, G22, G25:G26, G30, G35, G39, G43," & _
"G50, G51, G54:G58, G65, G68, G71, G74, G82, G83, G87," & _
"G88, G98, G99, G104, G110, G114, G118:G120, G122")
Set rngB = .Range("G126:G128, G130, G131, G135, G139, G142, G145, G148,"
& _
"G149, G153, G155, G159, G163, G164, G167, G169, G171," & _
"G176:G179, G189, G191, G194, G195")
Set Grng = Union(rngA, rngB)

For Each cell In Grng

Set ICell = .Cells(cell.Row, "I")

If (cell.Value = "No" Or cell.Value = "N/A" Or cell.Value = "") And _
(ICell.Text = False Or IsEmpty(ICell) Or ICell.Value = "") Then
If blank_cells Is Nothing Then
Set blank_cells = ICell
Else
Set blank_cells = Union(blank_cells, ICell)
sStrI = blank_cells.Address(0, 0) & ","
End If
End If
Next cell
sStrI = Left(sStrI, Len(sStrI) - 1)
For i = 1 To Len(sStrI)
If Mid(sStrI, i, 1) = "," Then
j = j + 1
If j = 6 Then
Mid(sStrI, i, 1) = vbCrLf
j = 0
End If
End If
Next

For Each c In Grng.Cells
If c.Value = "" Or c.Value = "0" Then
numBlanks = numBlanks + 1
End If
Next c

End With
Dim sStr As String
Dim rRng As Range
Dim rCell As Range

sStr = ""
For Each rCell In Grng.Cells
If rCell.Value = "" Or rCell.Value = "0" Then
sStr = sStr & rCell.Address(0, 0) & ","
End If
Next rCell
sStr = Left(sStr, Len(sStr) - 1)

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 6 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next
'***********
'MsgBox ("Number of blank cells: " & numBlanks _
& vbCrLf & vbCrLf & "Blank cell(s): " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "All blanks must be filled in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
'***************
'THIS MSGBOX WORKS
'If numBlanks > 0 Or sStr <> "" Or blank_cells.Count > 0 Or sStrI <> "" Then
' MsgBox "Number of blanks in G: " & numBlanks & vbCrLf & vbCrLf & _
' "Blank cell(s) in G: " & vbCrLf & sStr & vbCrLf & vbCrLf & _
' "Number of blanks in I: " & blank_cells.Count & vbCrLf & vbCrLf & _
' "Blank cell(s) in I: " & vbCrLf & sStrI
'***************
'#1 If blanks in both G and I
If numBlanks > 0 And sStr <> "" And blank_cells.Count > 0 And sStrI <>
"" Then
MsgBox ("Warning 1 - " & numBlanks & " question(s) remain(s)
unanswered." & vbCrLf _
& "Return to AUDIT tab to identify blank cell(s). " _
& vbCrLf & vbCrLf _
& "Warning - " & blank_cells.Count & " cell(s) require(s) a reason for
an N/A or No Response. " _
& vbCrLf & "Cell(s): " & sStrI), [vbOKOnly], ["Blank Cells!"]
'#2If all of G answered, but I incomplete
ElseIf numBlanks = 0 And sStr = "" And blank_cells.Count > 0 And sStrI
<> "" Then
MsgBox ("Warning - 2 " & blank_cells.Count & " cell(s) require(s) a
reason for an N/A or No Response." _
& vbCrLf & "Cell(s): " & sStrI), [vbOKOnly], ["Blank Cells!"]

'#3 If blanks in G but none in I (numBlanks = X, They are: sStr)
ElseIf numBlanks > 0 And sStr <> "" And blank_cells.Count = 0 And sStrI
<> "" Then
MsgBox ("Warning - 3 " & numBlanks & " question(s) remain(s) unanswered.
" & vbCrLf _
& "Return to AUDIT tab " _
& " to identify blank cell(s). "), [vbOKOnly], ["Blank Cells!"]

ElseIf numBlanks = 0 And blank_cells.Count = 0 Then
MsgBox ("Congratulations! This file is ready to upload " & vbCrLf _
& "using the Web Tool."), [vbOKOnly], ["Congratulations!"]
Else MsgBox "There is an error in my code"
End If
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Identical words 0
Working outlook via vba code with xlsx - error 0
VBA Export to PDF 0
Excel VB to create and send an outlook appointment with html code 1
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
VBA Coding Help for Beginner 0
For-Next by a row Counting consecutive Zero in the column 4
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0

Top