Prev: How To Assigned "SelectionChange" Event To different Worksheets
Next: Default Ribbon on File Open
From: Douglas Evans on 27 May 2010 18:02
Hi all.
I'm trying to use for my first time, a single dimension array to use within
an external sql query.
I build the array from column A. This can be a variable amount, from 2 to 800.
However, I can only use 150 rows at a time in the query.
Below is what I have so far, in building the array. It's just the pulling
out of 150 records at a time, or the remainder to put into the sql query.

I will be building the sql query string as such:
bug_number = (bug1,bug2,bug3) --these are the numbers in the array. (must
have comma's between them)
strQuery_main = "select distinct(bug_number) from applied_bugs"

I'm unsure on how to begin pulling out the records in the array. i.e. start
off with a Do Loop then use a For Next statement, and how to actually keep
track of where I am at.

The query return will be put into an array also.

Any assistance would be greatly appreciated.

Cheers,
Dougy

---------------------



Sub QueryBugsProcedure

'Declare variables
Dim rngList As Range
Dim intMax As Integer
Dim rwCnt As Integer
Dim intArray As Integer
Dim Arr() As Long
Dim ArrResults() As Long

'Number of bugs to be included in each sql query (max 250)
intMax = 150

'Count number of bugs
Range("A1").Activate
If ActiveCell.Offset(1, 0) = Empty Then
rwCnt = 1
Else
Range(Selection, Selection.End(xlDown)).Select
rwCnt = Selection.Count
End If

'Create array
ReDim Arr(1 To rwCnt)

For Each c In Selection
If c.Value <> Empty Then
i = i + 1
Arr(i) = c.Value
End If
Next c

'Move to this cell for query return
Range("B12").Activate

'count records in array checkpoint
Debug.Print UBound(Arr)




 | 
Pages: 1
Prev: How To Assigned "SelectionChange" Event To different Worksheets
Next: Default Ribbon on File Open