Vba macro to list un colored data remaining but instead of the number that is uncolored it would list the position it is on.

[Estatefinds]

So for the following data: The individual numbers are in columns BB Through BF.
so I need a macro to list the remaining uncolored numbers data but instead of the number itself listed it would be the position in which it sits. so the first example will show the numbers and the second example will show the positions. and place the list in column AN on the same row its starts reading data at from top to bottom. Please! Thank you

3	8	13	14	26
3	8	13	27	28
1	24	27	28	30
5	10	12	24	33
4	14	15	17	31
2	11	18	19	22
7	17	18	26	28
5	15	19	32	34
6	9	16	26	34
9	14	22	30	33
10	18	24	31	35
4	18	19	27	31
2	4	20	24	33
20	25	27	29	32
4	17	25	26	30
13	15	18	20	35
3	7	13	26	33
10	21	24	28	34
12	18	25	28	34
3	15	25	27	29
2	8	9	21	31
10	12	17	30	33
2	8	13	17	32
3	8	18	25	33
2	4	8	33	34
2	5	16	21	23
4	5	22	23	24
3	22	25	26	33
7	11	16	19	28
2	13	18	31	33
3	6	10	18	23
8	13	16	21	33
9	12	23	27	35
6	23	25	27	30
4	6	27	28	32
12	20	22	27	30
9	17	21	22	35
1	3	8	16	32
3	4	9	27	28
15	16	24	25	27
5	22	26	30	32
4	7	10	16	20
3	10	16	20	32
4	10	25	27	35
6	17	19	28	29
6	11	12	16	25
1	3	9	25	28
6	11	16	22	25
1	7	14	17	22
15	22	24	31	32
3	5	17	23	29
15	16	23	25	28
1	8	9	21	24
14	21	22	32	33
7	14	17	25	29
6	14	22	30	33
10	20	23	28	33
6	9	10	16	27
1	9	20	27	30
1	2	10	18	20
8	19	23	29	33
9	18	19	29	33
15	18	19	22	26
6	18	20	27	35
8	13	23	28	34
5	15	23	34	35
6	13	24	28	29
1	7	27	30	35
1	5	7	18	31
9	12	16	18	35

<tbody>
</tbody>

second example:

A1	B1	C1	D1	E1
A2	B2	C2	D2	E2
A3	B3	C3	D3	E3
A4	B4	C4	D4	E4
A5	B5	C5	D5	E5
A6	B6	C6	D6	E6
A7	B7	C7	D7	E7
A8	B8	C8	D8	E8
A9	B9	C9	D9	E9
A10	B10	C10	D10	E10
A11	B11	C11	D11	E11
A12	B12	C12	D12	E12
A13	B13	C13	D13	E13
A14	B14	C14	D14	E14
A15	B15	C15	D15	E15
A16	B16	C16	D16	E16
A17	B17	C17	D17	E17
A18	B18	C18	D18	E18
A19	B19	C19	D19	E19
A20	B20	C20	D20	E20
A21	B21	C21	D21	E21
A22	B22	C22	D22	E22
A23	B23	C23	D23	E23
A24	B24	C24	D24	E24
A25	B25	C25	D25	E25
A26	B26	C26	D26	E26
A27	B27	C27	D27	E27
A28	B28	C28	D28	E28
A29	B29	C29	D29	E29
A30	B30	C30	D30	E30
A31	B31	C31	D31	E31
A32	B32	C32	D32	E32
A33	B33	C33	D33	E33
A34	B34	C34	D34	E34
A35	B35	C35	D35	E35
A36	B36	C36	D36	E36
A37	B37	C37	D37	E37
A38	B38	C38	D38	E38
A39	B39	C39	D39	E39
A40	B40	C40	D40	E40
A41	B41	C41	D41	E41
A42	B42	C42	D42	E42
A43	B43	C43	D43	E43
A44	B44	C44	D44	E44
A45	B45	C45	D45	E45
A46	B46	C46	D46	E46
A47	B47	C47	D47	E47
A48	B48	C48	D48	E48
A49	B49	C49	D49	E49
A50	B50	C50	D50	E50
A51	B51	C51	D51	E51
A52	B52	C52	D52	E52
A53	B53	C53	D53	E53
A54	B54	C54	D54	E54
A55	B55	C55	D55	E55
A56	B56	C56	D56	E56
A57	B57	C57	D57	E57
A58	B58	C58	D58	E58
A59	B59	C59	D59	E59
A60	B60	C60	D60	E60
A61	B61	C61	D61	E61
A62	B62	C62	D62	E62
A63	B63	C63	D63	E63
A64	B64	C64	D64	E64
A65	B65	C65	D65	E65
A66	B66	C66	D66	E66
A67	B67	C67	D67	E67
A68	B68	C68	D68	E68
A69	B69	C69	D69	E69
A70	B70	C70	D70	E70

<tbody>
</tbody>

 

[Estatefinds]

The bolded numbers are the uncolored remaining.

 

[Estatefinds]

The number 7 is bolded as well i missed that one when i was bold typing them, on the first example first column below the 6th number.

 

[Eric W]

Like this?

Sub ListCells()
Dim FirstRow As Long, ResultRow As Long, r As Long, c As Long

    FirstRow = -1
    For r = 1 To 1000
        If Cells(r, "BB") <> "" Then
            If FirstRow = -1 Then
                FirstRow = r - 1
                ResultRow = r
            End If
            For c = 54 To 58
                If Cells(r, c).Interior.ColorIndex = xlNone Then
                    Cells(ResultRow, "AN") = Chr(c + 11) & r - FirstRow
                    ResultRow = ResultRow + 1
                End If
            Next c
        End If
    Next r
        
End Sub

 

[Estatefinds]

it Worked Great!!! ill be in touch soon when i can work out hese other request.
Thank you Very much!!
Sincerely,
Dennis

 

[Estatefinds]

I have a code that needs to be adjusted meaning when I select the data in A:E it the macro will read the data in J:N and out put the the position in which the numbers I selected are found below. so there are gaps in the data because i believe it not looking far enough down to find the number which will then return the position. need help please!

A	B	C	D	E			2/8/2016		J	K	L	M	N			2/8/16		S	T	U	V	W
							2/7/2016									2/7/16
16	18	26	29	32			2/6/2016	16-18-26-29-32	16	18	26	29	32			2/6/16		C9	C6	E1	D14	D8
3	8	13	14	26			2/5/2016	3-8-13-14-26	3	8	13	14	26			2/5/16		A16	B1	C1	B4	D6
3	8	13	27	28			2/4/2016	3-8-13-27-28	3	8	13	27	28			2/4/16		A15	B19	A14	C1	D1
1	24	27	28	30			2/3/2016	1-24-27-28-30	1	24	27	28	30			2/3/16		A35	D1	D9	E4	D7
5	10	12	24	33			2/2/2016	5-10-12-24-33	5	10	12	24	33			2/2/16		A4	A7	A15	C7	E6
4	14	15	17	31			2/1/2016	4-14-15-17-31	4	14	15	17	31			2/1/16		A7	B5	B3	B2	D6
2	11	18	19	22			1/31/2016	2-11-18-19-22	2	11	18	19	22			1/31/16		A7	B23	C1	C2	C4
7	17	18	26	28			1/30/2016	7-17-18-26-28	7	17	18	26	28			1/30/16		B10	B8	B4	D2	D11
5	15	19	32	34			1/29/2016	5-15-19-32-34	5	15	19	32	34			1/29/16		B18	B8	C4	E6	E1
6	9	16	26	34			1/28/2016	6-9-16-26-34	6	9	16	26	34			1/28/16		B22	C12	C17	D6	E9
9	14	22	30	33			1/27/2016	9-14-22-30-33	9	14	22	30	33			1/27/16		C11		C17	E5	E3
10	18	24	31	35			1/26/2016	10-18-24-31-35	10	18	24	31	35			1/26/16		A7	B1	D2	E1	E5
4	18	19	27	31			1/25/2016	4-18-19-27-31	4	18	19	27	31			1/25/16		B1	C4	D17	C2	E9
2	4	20	24	33			1/24/2016	2-4-20-24-33	2	4	20	24	33			1/24/16		A8	A2	D3	C5	E4
20	25	27	29	32			1/23/2016	20-25-27-29-32	20	25	27	29	32			1/23/16		D2	C1	D6	E6	E9
4	17	25	26	30			1/22/2016	4-17-25-26-30	4	17	25	26	30			1/22/16		B10	C7	C4	D2	D7
13	15	18	20	35			1/21/2016	13-15-18-20-35	13	15	18	20	35			1/21/16		C1	B4	B3	B20	E17
3	7	13	26	33			1/20/2016	3-7-13-26-33	3	7	13	26	33			1/20/16		A3	A12	C6	D11	E5
10	21	24	28	34			1/19/2016	10-21-24-28-34	10	21	24	28	34			1/19/16		A4	D3	E9	D1	E1
12	18	25	28	34			1/18/2016	12-18-25-28-34	12	18	25	28	34			1/18/16		B3	C5	C1	E10	E6
3	15	25	27	29			1/17/2016	3-15-25-27-29	3	15	25	27	29			1/17/16		A4	A20	D4	D13	E25
2	8	9	21	31			1/16/2016	2-8-9-21-31	2	8	9	21	31			1/16/16		A2	B2	A12	D5	D9
10	12	17	30	33			1/15/2016	10-12-17-30-33	10	12	17	30	33			1/15/16		C9	B11	D1	E12	E2
2	8	13	17	32			1/14/2016	2-8-13-17-32	2	8	13	17	32			1/14/16		A2	B1	B7	B14	E12
3	8	18	25	33			1/13/2016	3-8-18-25-33	3	8	18	25	33			1/13/16		A4	C1	C6	C4	D1
2	4	8	33	34			1/12/2016	2-4-8-33-34	2	4	8	33	34			1/12/16		A5	A2	A7	E3
2	5	16	21	23			1/11/2016	2-5-16-21-23	2	5	16	21	23			1/11/16		A4	B1	C3	D6	D1
4	5	22	23	24			1/10/2016	4-5-22-23-24	4	5	22	23	24			1/10/16		A8	A14	B1	E4	C13
3	22	25	26	33			1/9/2016	3-22-25-26-33	3	22	25	26	33			1/9/16		A3	C8	C6	C13	E2
7	11	16	19	28			1/8/2016	7-11-16-19-28	7	11	16	19	28			1/8/16		B13	B17	C3	C16	D6
2	13	18	31	33			1/7/2016	2-13-18-31-33	2	13	18	31	33			1/7/16		B30	B2	D1	D20	E2
3	6	10	18	23			1/6/2016	3-6-10-18-23	3	6	10	18	23			1/6/16		B7	A3	C11	D29	C2
8	13	16	21	33			1/5/2016	8-13-16-21-33	8	13	16	21	33			1/5/16		C6	B33	D6	C5	E22
9	12	23	27	35			1/4/2016	9-12-23-27-35	9	12	23	27	35			1/4/16		A4	A3	B1	D1	E4

<colgroup><col><col span="5"><col><col><col><col span="5"><col><col><col><col span="6"></colgroup><tbody>
</tbody>

Sub count_STUVW_Step35()
Application.ScreenUpdating = False
firstRow = Cells(1, "S").End(xlDown).Row
lastRow = Cells(Rows.Count, "S").End(xlUp).Row
ro = firstRow

' all data
While Not (Cells(ro, "AO") = "")
For c = 19 To 23 'for each column S to W
colA = 42 + (c - 19) * 6
Set searchRange = Range(Cells(ro, c), Cells(ro + 34, c))
Set outputRange = Range(Cells(ro, colA), Cells(ro + 34, colA + 4))
outputRange.clear
outputRange.NumberFormat = "0"
outputRange.BorderAround Weight:=xlThin
For Each cel In outputRange
searchString = Cells(firstRow - 1, cel.Column) & Cells(cel.Row, "AO")
countt = Application.WorksheetFunction.CountIf(searchRange, searchString)
If countt > 0 Then
cel.Value = countt
End If
Next cel
Next
ro = ro + 35
Wend
Application.ScreenUpdating = True
End Sub

 

[Estatefinds]

I want it to search at least a 100 below

 

[Estatefinds]

Let me know if you get a chance to read last message and of you need me to post as a new post. Thank you!
sincerely, Dennis

 

[Eric W]

Frankly, that code doesn't make a lot of sense. You say it's supposed to read columns A-E, but those are never referenced in the code. I tried stepping through it, and failed because it does reference Column AO, which you did not include on your screen shot. The macro name "Count_STUVW" does not match your description of what it's supposed to do. Further, it has the "magic numbers" of 34 and 35 hard-coded in it. I suspect that those are references to lastRow, which in this example calculates to 35. If you change 34 to (lastRow - 1) and 35 to lastRow in your code, maybe it will solve your problem. But then the line "ro = ro + 35" (or "ro = ro + lastRow") will guarantee that the main loop will only be executed once.

Try making that change and see if it helps. If it doesn't, I'd suggest opening a new thread. Put a before screenshot with all the columns, an after screenshot (manually manipulated to show your expected results), and an explanation of what you're looking for. I don't think posting your code will help, most of the VBA gurus here can probably come up with something cleaner.

Good luck!

 

[Estatefinds]

ok no problem thanks for looking at it! so what this does is i select say the data A:E on row 84 the same data that is exactly the same is in columns J:N. so when i select the data on row 84 in A:E the position of the numbers are resulted to the right in columns S:W. then i go up the the next row and select the data in columns A:E on row 85 and the resulted positions of these numbers are resulted in S:W. Let me know if this helps? this is all the codes for this worksheet I think these are all connected to work together
Thank You.
Sub count_STUVW_Step35()
Application.ScreenUpdating = False
firstRow = Cells(1, "S").End(xlDown).Row
lastRow = Cells(Rows.Count, "S").End(xlUp).Row
ro = firstRow

' all data
While Not (Cells(ro, "AO") = "")
For c = 19 To 23 'for each column S to W
colA = 42 + (c - 19) * 6
Set searchRange = Range(Cells(ro, c), Cells(ro + 34, c))
Set outputRange = Range(Cells(ro, colA), Cells(ro + 34, colA + 4))
outputRange.clear
outputRange.NumberFormat = "0"
outputRange.BorderAround Weight:=xlThin
For Each cel In outputRange
searchString = Cells(firstRow - 1, cel.Column) & Cells(cel.Row, "AO")
countt = Application.WorksheetFunction.CountIf(searchRange, searchString)
If countt > 0 Then
cel.Value = countt
End If
Next cel
Next
ro = ro + 35
Wend
Application.ScreenUpdating = True
End Sub


Sub clear()
S = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
For Each r In Selection
v = r.Value
For i = 0 To 9
v = Replace(v, S(i), "")
Next
r.Value = v
Next


Sub count_STUVW()
For r = 0 To Selection.Rows.Count - 1
For c = 0 To 4
Cells(Selection.Row + r, 28 + 2 * c) = WorksheetFunction.CountIf(Selection, Cells(Selection.Row + r, 27 + 2 * c))
Next
Next
End Sub





End Sub