Super User Asked by Erson Cokaj on November 4, 2021
I have a problem with VBA excel.
In column F, I have a number 57.07%. This column has 102 Rows. The idea is to generate random values in percentage, but the total average of these random numbers has to be 57.07%
I managed to generate random full numbers. But when it comes to adding and removing decimals for some reason I get no results.
Sub RandomiseSum()
Dim countries As Range, country As Range, pageviews As Range, clicks As Range, impressions As Range, col_f As Range, col_g As Range, col_h As Range, earnings As Range
Dim arr() As Double, i, z, y As Integer
'~~>Count the result of the range of countries
Set countries = Range("B4:B102")
Set pageviews = Range("C4:C102")
Set impressions = Range("D4:D102")
Set clicks = Range("E4:E102")
Set col_f = Range("F4:F102")
Set col_g = Range("G4:G102")
Set col_h = Range("H4:H102")
Set earnings = Range("I4:I102")
ReDim arr(countries.count - 1)
For i = 0 To countries.count - 1
arr(i) = Rnd
Next i
i = i - 1 '~~> Remove 1 from the total cell number in order to put the decimals/diferences in it at the end
'~~> Totals
TotalC = Range("C2").Value
TotalD = Range("D2").Value
TotalE = Range("E2").Value
avg_f = Range("F2").Value
avg_g = Range("G2").Value
avg_h = Range("H2").Value
TotalI = Range("I2").Value
col_h = avg_h
half1 = i / 2
half2 = (i / 2) + 1
z = half2
y = 4
x = 0
Do Until x = half1
this_nr = "H" & y
xnum = WorksheetFunction.RandBetween(0, 42) / 100
Range(this_nr).Value = Range(this_nr).Value - xnum
y = y + 1
z_nr = "H" & z
Range(z_nr).Value = Range(z_nr).Value + xnum
z = z + 1
x = x + 1
Loop
rnr = 0
Do Until rnr = half1
x12 = WorksheetFunction.RandBetween(4, 102)
x22 = WorksheetFunction.RandBetween(4, 102)
x12 = "H" & x12
x22 = "H" & x22
x3 = x12
Range(x12).Value = Range(x22).Value
Range(x22).Value = Range(x3).Value
rnr = rnr + 1
Loop
y = 4
z = half2
For x = 0 To x = half1
this_nr = "H" & y
z_nr = "H" & z
Range(this_nr).Value = Range(this_nr).Value - 0.3
Range(z_nr).Value = Range(z_nr).Value + 0.03
z = z + 1
x = x + 1
Next x
End Sub
As you can see from the picture, I get that .07 in all cells but I’m not being able to remove that value from some cells and add it to other ones.
Any ideas?
Is my loop wrong?
I managed to solve it. If anyone else will encounter the same problem, here's the code. What I did here is that I added another variable named "xnum2" where I would generate a random decimal number and then for each loop I would remove that value from the CELLx.value and add it to another CELLy.value, so that the average would still be the same.
col_h = avg_h
half1 = i / 2
half2 = (i / 2) + 1
z = half2
y = 4
x = 0
Do Until x = half1
this_nr = "H" & y
xnum = WorksheetFunction.RandBetween(0, 42) / 100
Range(this_nr).Value = Range(this_nr).Value - xnum
xnum2 = WorksheetFunction.RandBetween(0, 9) / 10000
Range(this_nr).Value = Range(this_nr).Value - xnum2
y = y + 1
z_nr = "H" & z
Range(z_nr).Value = Range(z_nr).Value + xnum
Range(z_nr).Value = Range(z_nr).Value + xnum2
z = z + 1
x = x + 1
Loop
rnr = 0
Do Until rnr = half1
x12 = WorksheetFunction.RandBetween(4, 102)
x22 = WorksheetFunction.RandBetween(4, 102)
x12 = "H" & x12
x22 = "H" & x22
x3 = x12
Range(x12).Value = Range(x22).Value
Range(x22).Value = Range(x3).Value
rnr = rnr + 1
Loop
Answered by Erson Cokaj on November 4, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP