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

Board Regular
Joined
Sep 14, 2015
Messages
169
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
[TABLE="width: 324"]
<tbody>[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]35[/TD]
[/TR]
</tbody>[/TABLE]


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

Code:
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
 
Upvote 0
it Worked Great!!! ill be in touch soon when i can work out hese other request.
Thank you Very much!!
Sincerely,
Dennis
 
Upvote 0
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!
[TABLE="width: 1562"]
<colgroup><col><col span="5"><col><col><col><col span="5"><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/8/2016[/TD]
[TD][/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/8/16[/TD]
[TD][/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/7/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/7/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/6/2016[/TD]
[TD]16-18-26-29-32[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/6/16[/TD]
[TD][/TD]
[TD]C9[/TD]
[TD]C6[/TD]
[TD]E1[/TD]
[TD]D14[/TD]
[TD]D8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/5/2016[/TD]
[TD]3-8-13-14-26[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/5/16[/TD]
[TD][/TD]
[TD]A16[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]B4[/TD]
[TD]D6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/4/2016[/TD]
[TD]3-8-13-27-28[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/4/16[/TD]
[TD][/TD]
[TD]A15[/TD]
[TD]B19[/TD]
[TD]A14[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/3/2016[/TD]
[TD]1-24-27-28-30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/3/16[/TD]
[TD][/TD]
[TD]A35[/TD]
[TD]D1[/TD]
[TD]D9[/TD]
[TD]E4[/TD]
[TD]D7[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/2/2016[/TD]
[TD]5-10-12-24-33[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/2/16[/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]A7[/TD]
[TD]A15[/TD]
[TD]C7[/TD]
[TD]E6[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/1/2016[/TD]
[TD]4-14-15-17-31[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/1/16[/TD]
[TD][/TD]
[TD]A7[/TD]
[TD]B5[/TD]
[TD]B3[/TD]
[TD]B2[/TD]
[TD]D6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/31/2016[/TD]
[TD]2-11-18-19-22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/31/16[/TD]
[TD][/TD]
[TD]A7[/TD]
[TD]B23[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/30/2016[/TD]
[TD]7-17-18-26-28[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/30/16[/TD]
[TD][/TD]
[TD]B10[/TD]
[TD]B8[/TD]
[TD]B4[/TD]
[TD]D2[/TD]
[TD]D11[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/29/2016[/TD]
[TD]5-15-19-32-34[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/29/16[/TD]
[TD][/TD]
[TD]B18[/TD]
[TD]B8[/TD]
[TD]C4[/TD]
[TD]E6[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/28/2016[/TD]
[TD]6-9-16-26-34[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/28/16[/TD]
[TD][/TD]
[TD]B22[/TD]
[TD]C12[/TD]
[TD]C17[/TD]
[TD]D6[/TD]
[TD]E9[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/27/2016[/TD]
[TD]9-14-22-30-33[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/27/16[/TD]
[TD][/TD]
[TD]C11[/TD]
[TD][/TD]
[TD]C17[/TD]
[TD]E5[/TD]
[TD]E3[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/26/2016[/TD]
[TD]10-18-24-31-35[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/26/16[/TD]
[TD][/TD]
[TD]A7[/TD]
[TD]B1[/TD]
[TD]D2[/TD]
[TD]E1[/TD]
[TD]E5[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/25/2016[/TD]
[TD]4-18-19-27-31[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/25/16[/TD]
[TD][/TD]
[TD]B1[/TD]
[TD]C4[/TD]
[TD]D17[/TD]
[TD]C2[/TD]
[TD]E9[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/24/2016[/TD]
[TD]2-4-20-24-33[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/24/16[/TD]
[TD][/TD]
[TD]A8[/TD]
[TD]A2[/TD]
[TD]D3[/TD]
[TD]C5[/TD]
[TD]E4[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/23/2016[/TD]
[TD]20-25-27-29-32[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/23/16[/TD]
[TD][/TD]
[TD]D2[/TD]
[TD]C1[/TD]
[TD]D6[/TD]
[TD]E6[/TD]
[TD]E9[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/22/2016[/TD]
[TD]4-17-25-26-30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/22/16[/TD]
[TD][/TD]
[TD]B10[/TD]
[TD]C7[/TD]
[TD]C4[/TD]
[TD]D2[/TD]
[TD]D7[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/21/2016[/TD]
[TD]13-15-18-20-35[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/21/16[/TD]
[TD][/TD]
[TD]C1[/TD]
[TD]B4[/TD]
[TD]B3[/TD]
[TD]B20[/TD]
[TD]E17[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/20/2016[/TD]
[TD]3-7-13-26-33[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/20/16[/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]A12[/TD]
[TD]C6[/TD]
[TD]D11[/TD]
[TD]E5[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/19/2016[/TD]
[TD]10-21-24-28-34[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/19/16[/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]D3[/TD]
[TD]E9[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/18/2016[/TD]
[TD]12-18-25-28-34[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/18/16[/TD]
[TD][/TD]
[TD]B3[/TD]
[TD]C5[/TD]
[TD]C1[/TD]
[TD]E10[/TD]
[TD]E6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/17/2016[/TD]
[TD]3-15-25-27-29[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/17/16[/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]A20[/TD]
[TD]D4[/TD]
[TD]D13[/TD]
[TD]E25[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/16/2016[/TD]
[TD]2-8-9-21-31[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/16/16[/TD]
[TD][/TD]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]A12[/TD]
[TD]D5[/TD]
[TD]D9[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/15/2016[/TD]
[TD]10-12-17-30-33[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/15/16[/TD]
[TD][/TD]
[TD]C9[/TD]
[TD]B11[/TD]
[TD]D1[/TD]
[TD]E12[/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/14/2016[/TD]
[TD]2-8-13-17-32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/14/16[/TD]
[TD][/TD]
[TD]A2[/TD]
[TD]B1[/TD]
[TD]B7[/TD]
[TD]B14[/TD]
[TD]E12[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/13/2016[/TD]
[TD]3-8-18-25-33[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/13/16[/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]C1[/TD]
[TD]C6[/TD]
[TD]C4[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/12/2016[/TD]
[TD]2-4-8-33-34[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/12/16[/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]A2[/TD]
[TD]A7[/TD]
[TD]E3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/11/2016[/TD]
[TD]2-5-16-21-23[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/11/16[/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]B1[/TD]
[TD]C3[/TD]
[TD]D6[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/10/2016[/TD]
[TD]4-5-22-23-24[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/10/16[/TD]
[TD][/TD]
[TD]A8[/TD]
[TD]A14[/TD]
[TD]B1[/TD]
[TD]E4[/TD]
[TD]C13[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/9/2016[/TD]
[TD]3-22-25-26-33[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/9/16[/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]C8[/TD]
[TD]C6[/TD]
[TD]C13[/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/8/2016[/TD]
[TD]7-11-16-19-28[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/8/16[/TD]
[TD][/TD]
[TD]B13[/TD]
[TD]B17[/TD]
[TD]C3[/TD]
[TD]C16[/TD]
[TD]D6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/7/2016[/TD]
[TD]2-13-18-31-33[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/7/16[/TD]
[TD][/TD]
[TD]B30[/TD]
[TD]B2[/TD]
[TD]D1[/TD]
[TD]D20[/TD]
[TD]E2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/6/2016[/TD]
[TD]3-6-10-18-23[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/6/16[/TD]
[TD][/TD]
[TD]B7[/TD]
[TD]A3[/TD]
[TD]C11[/TD]
[TD]D29[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/5/2016[/TD]
[TD]8-13-16-21-33[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/5/16[/TD]
[TD][/TD]
[TD]C6[/TD]
[TD]B33[/TD]
[TD]D6[/TD]
[TD]C5[/TD]
[TD]E22[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/4/2016[/TD]
[TD]9-12-23-27-35[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/4/16[/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]A3[/TD]
[TD]B1[/TD]
[TD]D1[/TD]
[TD]E4[/TD]
[/TR]
</tbody>[/TABLE]
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top