VBA Code needed
Hi,
I have the following code which allows me to randomly choose numbers( as much required ) from a set of data available in column A.
==============================================================
Sub test()
Dim myMax As Long, a, i As Long
myMax = [c2].Value
If myMax < 1 Then Exit Sub
With Cells(1).CurrentRegion.Offset(1)
.Columns("b").ClearContents
a = .Resize(, 2).Value
Randomize:
For i = 1 To UBound(a, 1) - 1
a(i, 2) = Rnd
Next
VSortM a, 1, UBound(a, 1) - 1, 2
.Columns(2).Resize(myMax).Value = a
End With
Call Choosen
End Sub
Private Sub VSortM(ary, LB, UB, ref)
Dim M As Variant, i As Long, ii As Long, iii As Long, temp
i = UB: ii = LB
M = ary(Int((LB + UB) / 2), ref)
Do While ii <= i
Do While ary(ii, ref) < M: ii = ii + 1: Loop
Do While ary(i, ref) > M: i = i - 1: Loop
If ii <= i Then
For iii = LBound(ary, 2) To UBound(ary, 2)
temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
ary(i, iii) = temp
Next
ii = ii + 1: i = i - 1
End If
Loop
If LB < i Then VSortM ary, LB, i, ref
If ii < UB Then VSortM ary, ii, UB, ref
End Sub
==============================================================
Now, I want an extension to this code. What I want is that the extended code should first search for the number 1234567899 in column A and if found should place the same in the result first & then pick rest of the numbers.If the number(1234567899) is not there in column A then the existing code should straight way get execute skipping the search for number 1234567899.Hope, I am able to make you understand what I wanted.
| Attachment | Size |
|---|---|
| MO_LuckyDraw.xlsm | 18.43 KB |
Comments
I’ve a had a quick look at your code and I have a couple of suggestions, you shouldn’t see commenting as an chore that needs to be completed once your routine works, it is an integral part of the coding process. I’d rather have code that didn’t work but was properly commented than code that does work with no comments. I comment first code second, but this is a personal preference you’ll have to find what works for you. But, I hear you say, this for my own personal use, nobody else is going to see it. If you come back to something you’ve written a couple of months ago and can’t immediately work out what is going then something’s wrong.
A couple of suggestions, every block of code within a sub should have a at least a sentence in English, or your language of choice explaining what it is trying to do, not what is does. Coding isn’t a competition to fit as much in the fewest number of lines, does the line continuation character ‘:’ help you understand the flow of the program? Variable naming: the name of a variable should describe what it is, a guess is that ary is an array. If you got a pet cat you wouldn’t call it cat. The standard naming conventions for looping variables is i, j, k etc.
Some specific VBA things: my personal preference is to prefer functions that return a value even if it is only the Boolean success or fail rather than subs. If you do use subs then always use the call syntax, that is call YourSub rather than just YourSub. If a variable declaration doesn’t have as type after then it is a variant, which isn’t usually what you want. If you do want a variant then tell us why. Always use option explicit. Most of the code I see uses range(“A1”).value rather than [A1].value. For other tips on coding style look at http://en.wikipedia.org/wiki/Code_Complete
I hope you take this in the spirit it was intended, as useful guidance not criticism. If I think you don’t understand the problem you are trying to solve then I’m not going to waste my time trying to solve if for you. HTH