Help With Code - Array and Range
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0
Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf & CellAddresses
Cell Addresses is the part of the MsgBox I need. If numError is 0, I have
to loop through cells in G and cells in I and list them in the MsgBox, five
cells per line with commas between each cell.
I did something similar to this a few days ago and thought I could just plug
in that code, however, this time, there are two columns to consider -- if the
G cell is blank it needs to be displayed in the list, or if the G cell has
"No" or "N/A" then Column I has to be checked for a blank; if it is blank, it
has to be listed too.
Any help will be greatly appreciated!!
--
Sandy
|