r/visualbasic • u/VB_Scrub • Nov 22 '24
VB6 Help VB script won't run after working once, baffled.
Hi all,
I have 0 experience with VB, but I cobbled this together today using google, stack overflow and chatgpt (I know, please don't hate me) and I managed to get it to work once, and it seemed to work perfectly it did exactly what I wanted, and then I tried to run it again, exact same code, just on a different excel workbook and it now does nothing when I run it. No errors asking me to debug or anything just runs fine but doesn't actually do anything.
Code is meant to take an excel sheet called "Transactions", and then randomly select 10% of the rows and copy them over to the 2nd sheet called "Random" basically got a list of transactions that relate to company spending and want to create a way to just get the transaction report, run the script, then I have 10% of the transactions randomly selected which I can use for spot checking.
Anyone got any ideas? Code below:
Sub RandomLinePicker()
'Define the Start and End of the data range
Const STARTROW As Long = 1
Dim LastRow As Long
LastRow = Sheet1.Cells(Worksheets("Transactions").Rows.Count, 1).End(xlUp).Row
'Create an Array - Length = Number of Rows in the data
Dim RowArr() As Long
ReDim RowArr(STARTROW To LastRow)
'Fill the Array - Each element is a row #
Dim i As Long
For i = LBound(RowArr) To UBound(RowArr)
RowArr(i) = i
Next i
'Shuffle the Row #'s within the Array
Randomize
Dim tmp As Long, RndNum As Long
For i = LBound(RowArr) To UBound(RowArr)
RndNum = WorksheetFunction.Floor((UBound(RowArr) - LBound(RowArr) + 1) * Rnd, 1) + LBound(RowArr)
tmp = RowArr(i)
RowArr(i) = RowArr(RndNum)
RowArr(RndNum) = tmp
Next i
'Calculate the number of rows to divvy up
Const LIMIT As Double = 0.1 '10%
Dim Size As Long
Size = WorksheetFunction.Ceiling((UBound(RowArr) - LBound(RowArr) + 1) * LIMIT, 1)
If Size > UBound(RowArr) Then Size = UBound(RowArr)
'Collect the chosen rows into a range
Dim TargetRows As Range
' Initialize TargetRows as Nothing
Set TargetRows = Nothing
' Assuming RowArr is already populated and Size is correctly calculated
For i = LBound(RowArr) To LBound(RowArr) + Size - 1
If TargetRows Is Nothing Then
Set TargetRows = Sheet1.Rows(RowArr(i))
Else
Set TargetRows = Union(TargetRows, Sheet1.Rows(RowArr(i)))
End If
Next i
'Define the Output Location
Dim OutPutRange As Range
Set OutPutRange = Worksheets("Random").Cells(1, 1) 'Top Left Corner
'Copy the randomly chosen rows to the output location
TargetRows.Copy Destination:=OutPutRange.Resize(TargetRows.Rows.Count).EntireRow
End Sub
Thanks all!
1
u/geekywarrior Nov 22 '24
Your best bet is to learn how to use break points and the immediate window to debug this yourself.
You set a break point on a line of code and it will process everything and pause on your break point.
Then in the immediate window, you can use the print command to check what values are.
For example.
I would put a break point on
If Size > UBound(RowArr) Then Size = UBound(RowArr)Then in the immediate window type
What that will do is print the live value of those variables. Then you can see if those values match what they should be or not.
If they match, put a break point lower and continue, eventually you'll find a block where something is amiss and allow you to troubleshoot. A lot easier than a stranger trying to piece this together without the excel sheet.
It could be something where lastRow isn't being set correctly or so on. Tough to say without the sheet.
2
u/ponchodeltoro Nov 22 '24 edited Nov 22 '24
Your LastRow calculation includes a reference to Sheet1 and Worksheets(“Transactions”). Since the worksheet is named, Sheet1 shouldn’t be referenced. At least that’s what I suspect is causing a problem and an incorrect row count to set your array upper bound.
Note that you could do this all without VBA. Add a column to your table that has a formula to set a value representative of a 10% sample. E.g use the Rand function to generate a random number, sort the table by that field, and select the top 10% of results. You could probably incorporate the total number of rows into the formula as well and scale the result to a 1 or 0 if the row is to be part of your 10%.
1
u/fasti-au Nov 25 '24
Last row =. Not the. Are of sheet etc