Vb net excel screenupdating
b) In each case where there is a match of ALL 6 drawn numbers, the said matching 6 numbers in columns A to F will be coloured RED, and then the number ‘6’ is inserted in column H for that particular row (to indicated that 6 numbers have been matched).
Status Bar = "Searching for Records..." Set cmd = Create Object("ADODB. Close If i = 0 Then Msg Box "Nothing Found, Exiting" Exit Sub End If Application. Display Status Bar = True ' '**** ' found something so create the output files and write the headers '**** ' Application. Bold = True ' '**** ' Copy Field names to header row of worksheet 2 '**** ' Set objsheet = Worksheets(2) For h = 1 To numheader2 objsheet. Status Bar = "Writing Group " & j & " of " & i Set objgroup = Get Object(grouppaths(j)) Set objsheet = Worksheets(1) cl = cl 1 objsheet. Execute ' '**** ' process the results of the query into our arrays for later '**** ' i = 0 While rs. Command Text = "SELECT adspath,cn from 'LDAP://" & get NC & _ "' WHERE object Category = 'Group' and cn = '" & groupname & "'" cmd.activeconnection = cn Set rs = cmd. Status Bar = "Records Found..." & i ' '**** ' Turn off updates and calculations '**** ' Application. Status Bar = "Creating Worksheet headers..." If i 0 Then ' '**** ' Copy Field names to header row of worksheet 1 '**** ' Set objsheet = Worksheets(1) objsheet. Valid AD group found so process results with the info in the v AD_Members() array... Command Text = "SELECT adspath,cn from 'LDAP://" & Get NC & "' WHERE object Category = 'Group' and cn = '" & s Group_Name & "'" cmd.activeconnection = cn Set rs = cmd.
Status Bar = "Sorting Worksheets..." Set objworksheet = Worksheets(1) objworksheet. Dim v AD_Members As Variant, v Tableau Group As Variant For Each v Tableau Group In v Group List v AD_Members = AD_Info(v Tableau Group) 'Grab AD possible group If v AD_Members(0, 0, 0) = "Empty Set" Then Go To Get Next '...
Const s No Entry As String = "No Entry" Dim Group Paths() As Variant, Group Names() As Variant, cmd As Object, cn As Object, rs As Object, obj Group As Object, l Record Count As Long, l Record Total As Long Dim l Index Count As Long, obj Member As Variant, l Member Count As Long, v Array() As Variant, x As Long, l Current Member Count As Long, l Member Counter As Long Re Dim v Array(0, 0, 0) v Array(0, 0, 0) = "Empty Set" AD_Info = v Array 'Set default value If s Group_Name = "" Then Exit Function 'Nothing passed in so exit 'Remove wildcard characters If b Exclude Wild Card Then s Group_Name = Replace(s Group_Name, "*", "") End If 'Set up ADO query and excute to find group matches Application. EOF True l Record Total = l Record Total 1 Re Dim Preserve Group Paths(l Record Total) Re Dim Preserve Group Names(l Record Total) Group Paths(l Record Total) = rs. Value 'AD comprehensive info Group Names(l Record Total) = rs. Value 'Just the AD group name(s) rs.movenext Wend cn.
Get Next: Next v Tableau Group End Function Option Explicit Function AD_Info(By Val s Group_Name As String, Optional b Exclude Wild Card As Boolean = True) As Variant 'Based on specific AD/LDAP info for a given group. Execute 'Process the results of the query into arrays l Record Total = -1 On Error Go To Error_Handler While rs.
That’s fine, I am using Excel 2010 that can handle up to a million rows. I also have a list of all the drawn lottery results.
a) With the generated 6-number combinations occupying columns A to F, I intend to paste the drawn results in adjacent columns J to P – with columns J to O for the weekly drawn 6 numbers, and column P for the bonus number. The task at this stage is:- a) To get a VBA code that will take each set of drawn results (including the bonus number) in columns J to P and compare/match with all the generated combinations (A to F).
d) Finally, if ONLY 5 of the drawn numbers (without the bonus) are matched in columns A to F, the 5 numbers will be coloured RED, and the number “5” inserted in column H for that row. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next Hi Pete, I did a search on the VB6 forum for ‘generating number combinations’ and only 5 threads came up.