Find blank cells and array items addresses to arraylist

[Vincent88]

Hi Guys, I want to find the cell addresses to a Arraylist (blankarray) of those values in an array (bArray). It seems that the code adds those blank cells' addresses only but not those values to Arraylist. Please advise correction to my code to make it works.
Also I want to list those findings of each row in individual column in the Arraylist (meaning findings of row 3 in rngData shown in column 1 of Arraylist , that of row 4 shown in column 2 of Arraylist and so on).
Thanks.

Sub rngOver32()

Dim cl As Range, rngData As Range

Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False


Dim lastrow As Long, lastcol As Long
lastrow = Range("A3").End(xlDown).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column

'LIST OF VALUE TO FIND
Dim bArray As Variant
bArray = Array("AL", "SL", "BL", "CL", "PL", "VL", vbNullString)
Dim bArraystr As String


'EXPORT CELL ADDRESSES TO ARRAYLIST
Dim blankarray As Object
Set blankarray = CreateObject("System.Collections.ArrayList")

'SET DATA RANGE TO SEARCH
Set rngData = Range(Cells(3, 3), Cells(lastrow, lastcol))
Debug.Print rngData.Address

On Error GoTo ErrorHandler

For Each cl In rngData
    If cl.Value = bArraystr Then
       If Not blankarray.contains(cl.Address) Then blankarray.Add cl.Address
    End If
Next cl

ErrorHandler:
Application.EnableEvents = True
   
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

AgentProposal_Roster0728_1025M.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	MDate	Attendance	26-Nov	27-Nov	28-Nov	29-Nov	30-Nov	1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec	7-Dec	8-Dec	9-Dec	10-Dec	11-Dec	12-Dec	13-Dec	14-Dec	15-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	21-Dec	22-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	28-Dec	29-Dec	30-Dec	31-Dec
2	Date	Summary	(5)	(4)	(3)	(2)	(1)	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
3	Xia M	T:22 L:2.5 D:16.5 E:7 N:0	G	G	G	G	G	G	VL	VL	G			G	D	K	E	E	E		D	D	D	D	D		AM	D	D	D	D	D	D		E	E	E	E
4	Zita V	T:22 L:1 D:12 E:11 N:0	D			D	D	D		D	D	D		E	E	E	E	E		D	D	D	D	D			D	D	D		CL	E	E	E	E	E	E
5	Ken C	T:22 L:6 D:5 E:5 N:11	K			K	K	K	K	K	K	K		AL	N	N	N	N	N			E	E	E	E	E		AL	N	N	N	N	N	N	AL	AL	AL	AL
6	Larry Q	T:22 L:0 D:15 E:0 N:0	D3			D3	D3	D3	D3	D3	D3		D4	D4	D4	D4	D4				D4	D4	D4	D4	D4	G
7	John G	T:22 L:0.5 D:17.5 E:5 N:0		E	E			E	E	E	E	E				D1	D1	D1			G	G	G	G	G		PM	G	G	G	G	G			G	G	G	G
202112

Cells with Data Validation
Cell	Allow	Criteria
A3:A7	List	=HelpAgent
H3:AL7	List	=ShiftcodeNew
A1	List	=Data!$U$2:$U$15

 

[Vincent88]

the "indi" shown 1 when error occurs.

 

[offthelip]

That makes sense, what it means is that No matches have been found, so in the line:

 .Range(.Cells(1, 1), .Cells(indi - 1, 1)) = blankarray

indi-1 = 0, there is no row zero so you get an error. So you must be running the code on the wrong worksheet or else there really aren't any matches for the the string in Barray.
One other alternative you might have copied the code incorrectly, so what about posting the code you are actually running.
It is easy to put some protection for this case if you need it

 

[offthelip]

I just had another look at your debug listing and it shows LASTROW=2, this means there is NO DATA to check because the checks only start on row 3, so the code will crash. I suspect you are running it on the wrong sheet

 

[Vincent88]

Hi offthelip,
Please help how to apply the sorting as shown in the image in arraylist not in physical cells.
The code that I make the sorting in physical cells is listed.
The sorting is as follows :
I need to sort those cells' addresses in column AA in following order
1. sort the row number first (as shown in column AD)
2. then sort the column number (col AC) (in order to sort the column, I use ToColNum to convert the column letters (Column AB) to column numbers (Column AC))

Sub rngOver33()

'Dim cl As Range,
Dim rngData As Variant

Dim lastrow As Long, lastcol As Long
lastrow = Range("A2").End(xlDown).Row
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column

'LIST OF VALUE TO FIND
Dim bArray As Variant
bArray = Array("AL", "SL", "BL", "CL", "PL", "VL", "ML", vbNullString)
Dim bArraystr As String

Debug.Print lastrow, lastcol


Dim blankarray() As Variant

ReDim blankarray(1 To lastrow * lastcol, 1 To 1)
'Load DATA RANGE TO SEARCH
rngData = Range(Cells(1, 1), Cells(lastrow, lastcol)) ' this load the data into a variant array (much faster)


'On Error GoTo ErrorHandler don't do this it just hides any error
indi = 1
For i = 1 To UBound(bArray)
bArraystr = bArray(i)
 For j = 3 To lastrow ' Note we are starting on row 3 as per the original
   For k = 3 To lastcol
    If rngData(j, k) = bArraystr Then
       blankarray(indi, 1) = colno2let(k) & j
       indi = indi + 1
    End If
   Next k
 Next j
Next i
With Worksheets("Data")
 .Cells(1, 27).CurrentRegion.Value = vbNullString
 .Range(.Cells(1, 27), .Cells(indi - 1, 27)) = blankarray
 
'End With

Application.ScreenUpdating = False
Application.EnableEvents = False
'With Worksheets("Data")
'Dim lrow As Long
 'lrow = .Range("AA1").End(xlDown).Row
 lRow = .Cells(1, 27).End(xlDown).Row
 
For Each c In .Range(.Cells(1, 28), .Cells(.Cells(1, 27).End(xlDown).Row, 28))
     c.Value = tex(c.Offset(, -1))
Next c

For Each k In .Range(.Cells(1, 29), .Cells(.Cells(1, 27).End(xlDown).Row, 29))
     k.Value = ToColNum(k.Offset(, -1))
Next k

For Each n In .Range(.Cells(1, 30), .Cells(.Cells(1, 27).End(xlDown).Row, 30))
     n.Value = num(n.Offset(, -3))
Next n

'.Range("AA1:AB" & lrow).Sort Key1:=.Range("AB1"), _

.Cells(1, 27).CurrentRegion.Sort key1:=.Cells(1, 30), _
                     Order1:=xlAscending, _
                     key2:=.Cells(1, 29), _
                     order2:=xlAscending, _
                     Header:=xlNo, _
                     Orientation:=xlTopToBottom

Application.ScreenUpdating = True
Application.EnableEvents = True
End With


End Sub

Function colno2let(colno As Variant) As String
let2 = colno / 26

If let2 > 1 Then
 let22 = Round(let2)
 txt = Chr(let22 + 64)
 txt = txt & Chr(64 + colno - (26 * let22))
Else
txt = Chr(colno + 64)
End If
colno2let = txt

End Function


Function num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
num = num & Mid(rng, n, 1)
End If
Next n
End Function

Function tex(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[A-Z]" Then
tex = tex & Mid(rng, n, 1)
End If
Next n
End Function


Public Function ToColNum(ColN)
    ToColNum = Range(ColN & 1).Column
End Function

 

[offthelip]

you are making very hard work of what is very simple to do: because the code is doing a triple loop through Barray, then rows , then columns, you can solve your "sorting" probelm just by changing the order of the three loops. so all your code and your additional functions are not necessary. So thy this which will create the list in a "sorted order"

Sub rngOver33()

'Dim cl As Range,
Dim rngData As Variant

Dim lastrow As Long, lastcol As Long
lastrow = Range("A2").End(xlDown).Row
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column

'LIST OF VALUE TO FIND
Dim bArray As Variant
bArray = Array("AL", "SL", "BL", "CL", "PL", "VL", "ML", vbNullString)
Dim bArraystr As String

Debug.Print lastrow, lastcol
Dim blankarray() As Variant

ReDim blankarray(1 To lastrow * lastcol, 1 To 1)
'Load DATA RANGE TO SEARCH
rngData = Range(Cells(1, 1), Cells(lastrow, lastcol)) ' this load the data into a variant array (much faster)


'On Error GoTo ErrorHandler don't do this it just hides any error
indi = 1
For k = 3 To lastcol   ' note the change in order of these 3 loops
 For j = 3 To lastrow '    
  For i = 1 To UBound(bArray)
    bArraystr = bArray(i)
    If rngData(j, k) = bArraystr Then
       blankarray(indi, 1) = colno2let(k) & j
       indi = indi + 1
    End If
   Next i    ' and here
 Next j
Next k
With Worksheets("Data")
 .Cells(1, 27).CurrentRegion.Value = vbNullString
 .Range(.Cells(1, 27), .Cells(indi - 1, 27)) = blankarray
 

End With


End Sub

Function colno2let(colno As Variant) As String
let2 = colno / 26

If let2 > 1 Then
 let22 = Round(let2)
 txt = Chr(let22 + 64)
 txt = txt & Chr(64 + colno - (26 * let22))
Else
txt = Chr(colno + 64)
End If
colno2let = txt

End Function

If you really want the column numbers and row numbers in separate columns. you don't need to decode them from the Alpah numeric address , they are already available because the indices j and k are row and column nuumbers so if you want then they just need to be saved in a variant array and written to the worksheet.
Note: I actually needed to include the function colno2let, specifically to convert column numbers to letters, because that is what you asked for. Column numbers are much easier to deal with in VBA than column letters.

 

[Akuini]

@offthelip
When I tried your function like this:

Debug.Print colno2let(40) 'result B4

the result is B4 which is wrong

A simpler code to get column letter from column number:

colno = 40
Debug.Print Split(Cells(1, colno).Address, "$")(1) 'result AN

 

[offthelip]

You are correct, thank you for pointing it out. I got the round down wrong, I had only tested it up the first half of the alphabet. this modification works:

Function colno2let(colno As Variant) As String
let2 = colno / 26

If let2 > 1 Then
 let22 = Int(let2)
 txt = Chr(let22 + 64)
 txt = txt & Chr(64 + colno - (26 * let22))
Else
txt = Chr(colno + 64)
End If
colno2let = txt

End Function

I prefer to make my functions independent of any access to worksheets because it slow the functions down.

 

[Vincent88]

You are correct, thank you for pointing it out. I got the round down wrong, I had only tested it up the first half of the alphabet. this modification works:

Function colno2let(colno As Variant) As String
let2 = colno / 26

If let2 > 1 Then
 let22 = Int(let2)
 txt = Chr(let22 + 64)
 txt = txt & Chr(64 + colno - (26 * let22))
Else
txt = Chr(colno + 64)
End If
colno2let = txt

End Function

I prefer to make my functions independent of any access to worksheets because it slow the functions down.

Hi offthelip,
What about if I want to sort it by row number ?

 

[Vincent88]

I also recognize that the code did not find cell addresses of the value "AL". Very strange !
My screenshot shown cell address E3 and M3 with value "AL"