VBA Code to Randomly Replace a Cell Value in a Table Range

[hassanleo1987]

Hi,

I have a table range to 30 columns and 100 rows with some blank cells in it. The cells values are 6 , 8 , 10 , SL , VC and RE other than the blanks.
Lets suppose the current sum of table range mentioned above is 6000. I need a VBA code that will target the cells with numerical values only ignoring the blanks and other values i.e. SL, VC or RE, change it to a specific value of 8 until the sum of table range is 5560.

The targeted cells that are being changed must be random and loop should continue until the target sum of 5560 is achieved.

Target sum value is in a fixed range along with specific value "8" . (Fixed cell references in the same sheet for reference in VBA code).

Actual application is a big table of more than 100 columns and 6000 rows, doing it manually takes a lot of time!

Can somebody please help with this!

 

[Peter_SSs]

How do we know that the result is even possible?

How many cells in the 30 column x 100 row range contain numbers?

 

[hassanleo1987]

How do we know that the result is even possible?

How many cells in the 30 column x 100 row range contain numbers?

The number of cells in 30 column x 100 rows is 3000!

Yes, I think it is possible with a good randomization code!

 

[Peter_SSs]

The number of cells in 30 column x 100 rows is 3000!

Yes, I can do that calculation.

You said that your range contains blanks and certain text values.
My question was

How many cells in the 30 column x 100 row range contain numbers?

 

[hassanleo1987]

How do we know that the result is even possible?

How many cells in the 30 column x 100 row range contain numbers?

The way I understand, It is doable in VBA is this!

Suppose we have the fixed range of 30 columns x 100 rows, the total cells will be fixed 3000 cells, each having a unique reference.

The first part of the code will exclude the cell which does not fit our criteria i.e. if the cell value is SL, VC, RE or blank, it will be ignored. Lets suppose we have 300 such cells. Remaining cells will be 2700 with numerical values of 6, 8 and 10.

Since the sum of table range is already fixed along with the target sum to be achieved, a simple calculation is need to determine the number of cell to be changed in order to achieve the goal.

Let suppose out of remaining 2700 cells, 2000 cells contain the value of 10 which can be decreased to the specific value of 8 in order to achieve the target sum.

The target sum being 5560 gives us the total negative value to be applied. 6000 - 5560 = 440

Since we are going to replace 10 with 8, so a decrement of 2 will be applied on randomly selected cells.

440 / 2 = 220 will give us the total number of random cells to be selected out of 2000 cells with cell value of 10, where the decrement will be applied.

The code will randomly select 220 cell out of 2000 cells that fits our criteria and change the cell values from 10 to 8, achieving the target sum of 5560.


This is my understanding on how this code should work.
I hope this explains the problem and solution for anybody to try and write a code for it.

 

[hassanleo1987]

Yes, I can do that calculation.

You said that your range contains blanks and certain text values.
My question was

The number of cells containing numbers is not fixed, hence need to be calculated and identified by the code either by using a function or something like ISNUMBER... I don't know what is the best way to do it. But a loop should do it. checking each cell based on the number criteria.

This way it will work faster by identifying and recording the cells with only numbers, ignoring text and blanks.

 

[hassanleo1987]

I don't know if array are possible in VBA but an array will help since the table range is fixed and an array will iterate through every cell in it, finding the cells based on the number criteria and then using a randomization code to implement the change.

 

[Peter_SSs]

Let suppose out of remaining 2700 cells, 2000 cells contain the value of 10 which can be decreased to the specific value of 8 in order to achieve the target sum.

The target sum being 5560 gives us the total negative value to be applied. 6000 - 5560 = 440

I don't follow that.
If you have 2000 cells containing 10 you start with 20,000. Reducing them all to 8 still leaves you way over your target of 5560.

Perhaps there is a typo there that you can clarify, but that is the sort of (impossible) thing I was trying to get at.

 

[Akuini]

@hassanleo1987
Let's say, the initial sum is 1000 and the target sum is 980 which is lower than the initial sum. Do you want only some 10 changes to 8, or it's fine that some 6 also changes to 8?

 

[hassanleo1987]

I don't follow that.
If you have 2000 cells containing 10 you start with 20,000. Reducing them all to 8 still leaves you way over your target of 5560.

Perhaps there is a typo there that you can clarify, but that is the sort of (impossible) thing I was trying to get at.

Sorry, I think the numbers are confusing. the current of table range 6000 is just assumed along with the target sum for easy calculation of decrement and number of cells to be targeted.

Please see this mini sheet where I have shown before and after change.

aa.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Original Table
3		SR #	A	B	C	D	E	F	G	H	I	J
4		1	10	6	10	10	10	10	10	10	10	10
5		2	10	10	6	10	SL	10	10	8	10	8
6		3	6	10	10	10	10	10	10	10	10	10
7		4	10	SL	10	6	10		10	RE	6	10
8		5	10	10	10	10	6	10	6	10	10	10
9		6	10		8	10	10	10	10	10	10	RE
10		7	8	10	10	10	VC	10	10	10	10	10
11		8	10	10	10		10	8	10	VC	10	10
12		9	10	8	10	10	10	10	10	10	10	6
13		10	10	10	10	10	10	10	10	10	10	10
14
15		Current sum					866
16		Target sum					850
17		Sum Variance					-16
18		Current Cell with Value of 10					77
19		Number of cell to be randomly changed to 8					8
20
21		After implementing the random change
22		SR #	A	B	C	D	E	F	G	H	I	J
23		1	8	6	10	8	10	10	10	10	10	10
24		2	8	10	6	10	SL	10	8	8	10	8
25		3	6	10	8	10	8	8	10	10	8	10
26		4	10	SL	10	6	10	8	10	RE	6	10
27		5	8	10	8	10	6	8	6	10	10	10
28		6	10	8	8	8	10	10	10	8	10	RE
29		7	8	10	10	10	VC	10	8	10	8	10
30		8	10	10	8	10	10	8	10	VC	10	10
31		9	8	8	10	8	10	10	10	10	10	6
32		10	10	10	8	10	8	10	10	10	8	10
33
34		Sum		850
35
Sheet2

The 1st table shows the target range or table.
The 2nd table shows the result where 8 cells where changed from 10 to 8 get the total sum to target sum of 850.