Very Very slow VB code..?????

S

sezuh

Well-known Member
Joined
Nov 19, 2010
Messages
708
Hi,
Thanks to wonderful,wonderful help from “GlennUK “ solved one part of this tread;
http://www.mrexcel.com/forum/showthread.php?t=535167
but i’m having trouble with one of the Vb Code it’s very very slow,i started on Friday 21.00 pm
by Monday 06.00 it only finished 5883 rows out 20825 only is that normal??? Even after “Glenn” modified the excel formula still very very slow. Would someone please look at the code and give me some idea to sort the code??
I’m using excel 2007 would that be the problem?thanks in advance for any help.
example51_3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt">INPUT</TD><TD>Output</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1 2 3</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">17296</TD><TD style="TEXT-ALIGN: right">6200</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1 2 3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1 2 4</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">1 2 4</TD><TD> </TD><TD> </TD><TD>Test cells51</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">1 2 5</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1 2 5</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">45</TD><TD style="TEXT-ALIGN: right">49</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">1 2 6</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1 2 6</TD><TD> </TD><TD> </TD><TD>Output51</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">1 2 7</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1 2 7</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">8 19 50</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">1 2 8</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L1</TD><TD>=NOT(ISNA(MATCH(H1,$D$4:$F$4,0)))</TD></TR><TR><TD>M1</TD><TD>=NOT(ISNA(MATCH(I1,$D$4:$F$4,0)))</TD></TR><TR><TD>N1</TD><TD>=NOT(ISNA(MATCH(J1,$D$4:$F$4,0)))</TD></TR><TR><TD>O1</TD><TD>=OR(L1:N1)</TD></TR><TR><TD>P1</TD><TD>=COUNTIF($O$1:O1,FALSE)</TD></TR><TR><TD>Q1</TD><TD>=MAX(P1:P20825)</TD></TR><TR><TD>R1</TD><TD>=RANDBETWEEN(1,Q1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Code: 
Sub generatecells_51()
    Application.Calculation = xlCalculationManual
    For Each c In Range("A2:A20826")
        myarray = Split(c.Value, " ")
        For i = LBound(myarray) To UBound(myarray)
        [testcells51].Cells(i + 1).Value = Val(myarray(i)): Next
        ActiveSheet.Calculate
        c.Offset(0, 1) = [output51]
    Next
    Application.Calculation = xlCalculationAutomatic
End Sub
Regards
sezuh
 
Sort by date Sort by votes
Mrkowz thanks for your time and effort much appreciated.
I hope we can solve it tomorrow.
Regards
sezuh
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sezuh, I have found a potential solution to this (just have to test it a bit more and work out a few little bugs). This is going to take significantly longer than the last macro I supplied, as it now has four points to check instead of one.


I do have one question for you regarding how balanced you want the data. Given these conditions, it is even harder for Excel to find a unique value to place in column B, so I feel that we need to have two rules set in place to limit the amount of time Excel spends on finding unique data (otherwise it could go on for a LONG while).
  1. If 97.5% of the data finds a unique value, then directly assign data to the remaining 2.5%. (described below)
  2. If Excel loops 100,000 times to find unique values, then directly assign data to the remaining 2.5%. (described below)
Whichever of the above rules comes first (97.5% matched or 100,000 loops) would push the macro to the next stage of directly assigning data without regard to randomness, but still maintaining unique data.

Then after it assigns without regard to randomness, but still maintains unique data, it will directly assign data without regard to uniqueness (basically, occurances where it is impossible to find a unique match because there isn't enough data left over).

If you want, I can have the user input how "balanced" the data should be by entering their own percent threshold and loop threshold. That way you can say "I want at least 97.5% of the data to have a unique value OR loop through the data 100,000 times" or "I want at least 90% of the data to have a unique value OR loop through the data 500,000 times".
 
Upvote 0
Sezuh, in testing this a bit further and monitoring how many matches are found, I am seeing that a 2.5% threshold is way too much (I disabled this threshold for one test).

After 500 loops, the data was 99.78% balanced (20780 unique entries)
After 1000 loops, the data was 99.88% balanced (20802 unique entries)
After 2000 loops, the data was 99.93% balanced (20811 unique entries)
After 10000 loops, the data was 99.99% balanced (20823 unique entries)
After 20000 loops, no change. Still only 20823 unique entries.

Therefore, I'm going to give the macro instead a singlular condition of 10,000 loops. Then for the few cells that remain, I'm going to have the macro directly assign a value (whether it is unique or not), and also highlight that cell so you can easily find it.

Just a few more tests and I'll have the final macro posted.
 
Upvote 0
Hi Mrkowz,
As long as any number string in rows “A2:A20826” does not repeat in rows “B2:B20826”that would be fine.regarding balancing, every number is paired with any other number 49 times ,lets take 1 and 2 for an example,remaining numbers used once with them ,so when assigning i want remaining number to be used equally atleast once, but it doesn’t have to be 100%.
I would not mind if it takes about an hour to complete.this table showing num ber 1 and 2 pairing.i put them in 5 columns so it does not take space.
<TABLE class=MsoNormalTable style="BACKGROUND: white; mso-cellspacing: 0cm; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 2.0pt 0cm 2.0pt" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed"></TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed">
A<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed">
B<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed">
C<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed">
D<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed">
E<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
1<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 3<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 13<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 23<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 33<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 43<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
2<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 4<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 14<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 24<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 34<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 44<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
3<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 5<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 15<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 25<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 35<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 45<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
4<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 6<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 16<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 26<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 36<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 46<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
5<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 7<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 17<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 27<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 37<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 47<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
6<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 8<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 18<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 28<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 38<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 48<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
7<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 9<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 19<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 29<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 39<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 49<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
8<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 10<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 20<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 30<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 40<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 50<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
9<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 11<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 21<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 31<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 41<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 51<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 10; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; BACKGROUND: #cacaca; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt">
10<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 12<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 22<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 32<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent">
1 2 42<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #ebe9ed; PADDING-RIGHT: 2pt; BORDER-TOP: #ebe9ed; PADDING-LEFT: 2pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ebe9ed; PADDING-TOP: 0cm; BORDER-BOTTOM: #ebe9ed; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Thanks again
sezuh
 
Upvote 0
I suppose I meant "repeating" instead of balancing, per your post yesterday.

Try this macro. It took 26 min to run on my end, and it found a unique, non-repeating, entry for all but one entry (which could vary slightly due to randomness). Wherever it can't find a unique, non-repeating, entry, it will highlight that cell red and assign it a unique, but repeating, entry.

Code: 
Public Sub RandomSet()
Dim i           As Long, _
    j           As Long, _
    LR          As Long, _
    rng         As Range, _
    cnt         As Long, _
    loopcnt     As Long, _
    n           As Long, _
    retrow      As Long, _
    bool        As Boolean, _
    tStart      As Date, _
    tEnd        As Date, _
    numarray    As Variant, _
    dupcnt      As Long
 
'******************************** Initialization *********************************
'* Define initial variables and disable ScreenUpdating and Automatic Calculation *
'*********************************************************************************
tStart = Now
loopcnt = 1
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & rows.Count).End(xlUp).row
'************************************ Phase 1 ************************************
'* Populate column B with a randbetween formula used to generate an index number *
'*********************************************************************************
For i = 2 To LR
   Application.StatusBar = "Phase 1: Currently creating formula on row " & i
   Range("B" & i).Formula = "=Randbetween(2," & LR & ")"
Next i
'************************************ Phase 2 ************************************
'*   Loop to determine if value in column B is unique.  Will only check cells    *
'*   that have not yet found a unique index.  Will continue to loop until        *
'*   97.5% of the data has a unique index or it loops 100,000 times.             *
'*********************************************************************************
Do
    ActiveSheet.Calculate
    For Each rng In Range("B2:B" & LR).SpecialCells(xlCellTypeFormulas)
        Application.StatusBar = "Phase 2: Checking row " & rng.row & " on loop count " & loopcnt & ". " & cnt & " unique numbers found. " & Format(cnt / (LR - 1), "00.00%") & " balanced."
        numarray = Split(rng.Offset(0, -1).Value, " ")
        If Application.CountIf(Range("B1:B" & LR), rng.Value) = 1 Then
            For j = LBound(numarray) To UBound(numarray)
                If InStr(" " & Range("A" & rng.Value) & " ", " " & numarray(j) & " ") > 0 Then
                    bool = True
                    Exit For
                End If
            Next j
            If Not bool Then
                rng.Value = rng.Value
                rng.Offset(0, 1).Value = Range("A" & rng.Value).Value
                cnt = cnt + 1
            End If
        End If
        bool = False
    Next rng
    loopcnt = loopcnt + 1
Loop While loopcnt < 10000 And cnt < (LR - 1)
'************************************ Phase 3 ************************************
'*    The last bits of the data that has not yet found a unique index will be    *
'*    directly assigned a non-random unique index.                               *
'*********************************************************************************
n = 2
For Each rng In Range("B2:B" & LR).SpecialCells(xlCellTypeFormulas)
    retrow = LR
    bool = False
    numarray = Split(rng.Offset(0, -1).Value, " ")
    For i = n To LR
        Application.StatusBar = "Phase 3: Currently checking row " & rng.row & " for value " & i & ". " & cnt & " unique numbers found."
        If Application.CountIf(Range("B2:B" & LR), i) = 0 Then
            For j = LBound(numarray) To UBound(numarray)
                If InStr(" " & Range("A" & rng.Value) & " ", " " & numarray(j) & " ") > 0 Then
                        bool = True
                        retrow = Application.Min(retrow, i)
                        Exit For
                End If
            Next j
            If Not bool Then
                rng.Value = i
                rng.Offset(0, 1).Value = Range("A" & rng.Value).Value
                cnt = cnt + 1
                retrow = Application.Min(retrow, i)
                Exit For
            End If
        End If
    Next i
    n = retrow
Next rng
 
'************************************ Phase 4 ************************************
'*    Remaining data directly assigned an index number without regard to         *
'*    duplucation.                                                               *
'*********************************************************************************
n = 2
For Each rng In Range("B2:B" & LR).SpecialCells(xlCellTypeFormulas)
    rng.ClearContents
    bool = False
    For i = n To LR
        Application.StatusBar = "Phase 4: Currently assigning row " & rng.row & " a value.  Checking index " & i
        If Application.CountIf(Range("B2:B" & LR), i) = 0 Then
            rng.Value = i
            With rng.Offset(0, 1)
                .Interior.ColorIndex = 3
                .Value = Range("A" & rng.Value).Value
            End With
            n = i
        End If
    Next i
Next rng
'************************************ Cleanup ************************************
'*  Reorganizes data and turns ScreenUpdating and Automatic Calculation back on  *
'*********************************************************************************
Columns(3).Copy Destination:=Columns(2)
Columns(3).ClearContents
tEnd = Now
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
MsgBox "Time started: " & tStart & " -- Time Ended: " & tEnd & vbLf & cnt & " unique numbers found."
End Sub
 
Upvote 0
thanks very much for the code like you said it found a unique, non-repeating, entry for all but one entry, but before it finish it gave
"error 400 "and stopped without knowing which row does not have the uniqe number,any idea why???.i tried it in different combination lists unfortunetely it did not work on them , i believe you said
HTML: 
**This will work on any amount (and any type) of data in Column A, so it can
'       be universally used to generate a random list in column B (without repeating
that was the reason i tried it on.but my main goal is to sort this list ,so we accomlished that, the rest would be icing on the cake.

thanks for your invaluable help and effort,i realy appreciated.
regards
sezuh
 
Upvote 0
By that statement "Any amount of data in column A", that was before I was aware of the requirement to not repeat each individual number. That statement refers to being any number of string entries in column A. Given your repeat-condition, that statement is false, and has since been omitted from the comments.

I will try to run it on the data again to see if it errors on my end.
 
Last edited:
Upvote 0
I got an Error 1004 message on mine, but getting that error is actually good! That means that it was able to find a unique match for 100% of the data, and when it got to Phase 4, it was not able to continue since there were no rows that didn't have a match. If you get the Error 400 message again, let me know what line it highlights, in what phase it is highlighted, and also the entire accompanying error message.
 
Upvote 0
It's MsgBox with "400" in the middle with two option "OK" or "Help"
in it.unfortunetely i dont know how to "debug" the code.But i'll check at what phase the error appear.at the moment i'm running the code
sezuh
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
706
smozgur
smozgur
E
  • Question Question
Modify system Report
Replies
3
Views
91
Skyybot
Skyybot
V
  • Question Question
Tracking individual codes by date and quantity
Replies
1
Views
71
Vorkosigan
V
E
  • Question Question
VBA code clean up
Replies
9
Views
612
mumps
mumps
R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
165
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,155
Messages
6,183,218
Members
453,152
Latest member
ChrisMd

Share this page

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