Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Iterating Through An Array Of Numbers

Here's what I need to do. I am working on a program which stores some values in a 2-d array. I need to be able to go through the array and sum up the values in all of the rows, but for every combination of columns. I don't know if that's explains it very well so I'll give a little example.Array: 0 1 2 ---------- 0 | a b c 1 | d e f 2 | g h i 3 | j k lI need something that can return the total for all 4 rows but EVERY combination of columns (ie. {a+d+g+j}, {b+d+g+j}, {b+f+g+l}, etc...).I'm pretty sure it's gotta be done with recursion, but for some reason I'm braindead today and can't figure it out. Any help would be greatly appreciated.Thanx,Chris

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Iterating Over An Object Group (non-array)
If I have a group of non-array objects (don't ask why they're not in an array... too late for that) which essentially share the same name, how can I iterate through them via a loop? Example:

I have 6 different picture boxes named "Picture1", "Picture2", etc. I have a basic "For..." loop, from 1 to 6. On each pass of the loop, I want to handle the associated picture box. So, for x = 1, I want to alter Picture1. For x=4, I want to alter Picture4.

How can I do this?

Iterating Through An Array Of Boolean Values

can somebody help? I am trying to iterate through a column
of stored BITs in my DB. BITs appear as Booleans (0 = False,
1 = True) on the server.

The compiler keeps complaining that I cannot loop through
boolean values.


Dim defaultpic As New ArrayList

Dim j As Integer
Dim k As Boolean (i have also tried this as Integer and String)
Dim row As Integer
Dim HasDefault As Boolean

For j =0 To Counter.ToString

For Each k In defaultpic(j) (this is the trouble line)
if k
HasDefault = True
row = j
HasDefault = False
row = j
end if

when i declare k as String, it returns each letter in
the "True" or "False" strings. so, if the counter has 2 rows
and the first row is "False", i get "F" and "a". If i could
figure out a way to get the full string with each iteration ("False") i could use that for my logic.

anyway, i know there are all sorts of errors above, suggestions

thanks in advance.

How To Add Numbers (5 Different Numbers) To An Array And How To Get These Numbers Out Of The Array?
How to add numbers (5 different numbers) to an array and how to get these numbers out of the array? -thanks detailed code please

Array Of Numbers
I need to make an array of numbers like [1,2,4,6,10,55,26] that i can use sortof like they are in a list box like ListCount, List(Integer). does it have something to do with 'variant'?

Array Of Numbers
Hi all,

Let's say i want to populate an array with a string value


Function ATT(strValue as string)

dim Array() as integer

Array = val(strValue)

end function

How can i put the integer strValue into and array on one line without looping


Random Numbers In Array
i just have a quick query
im using the following code and generate random numbers from 1 to 12 to store using an array and display them on labels. How can i make the random numbers different to one another, thanx in advance.

Private Sub Command1_Click()

For i = 1 To 12
c(i) = Int((Rnd * 12) + 1)
Label1(i).Caption = c(i)
Next i

End Sub

Plugging Numbers Into Array

I have this problem I can't figure out, and it's starting to irk me.. I need to place all odd numbers from 13 to 93 in array A.. So far I have this:

Dim a()

For n = 13 To 93 Step 2
c = 0
c = c + 1
a(c, 1) = n
Next n

ReDim a(c, 1)

Print a(c, 1)

Help? You all know I need it!

Please and thanks.

Comparing Numbers In An Array
Hi I have a problem. I have written this subroutine below for comparing 2 arrays, each array has a length of 60 and consists of 60 individual digits in each position,which are to be taken as one long number when used. Basically I want to look at the first array MemA and then see if it is bigger than Numbr which is the second array. Digits is a constant of 60 for the length of the arrays. My code as below should work but I get a Next without For error. Whats the problem ? Thanks


Private Sub Comparenums()
For x = digits To 1 Step -1
If memA(x) = numbr(x) Then GoTo NxtLoop
If memA(x) + numbr(x) = 0 Then GoTo NxtLoop
If memA(x) > numbr(x) Then
IsMemALarger = True
GoTo EndLoop
If memA(x) < numbr(x) Then
IsMemALarger = False
GoTo EndLoop
Next x
End Sub

Randomizing Numbers In UDT Array
I have an array of an UDT that is supposed to set a random value to each of the x and y integers of each element of the array. For some reason, it is giving the same coordinates for each element, so each time the scene is rendered, it displays every "food" circle in the same place.

This is the part GetFood sub that is called when the form loads, and is supposed to randomize the x and y integers of each element of the array. I have it as a public sub in a module.

'put food in random locations
For i = i To UBound(food)
food(i).x = Rnd * 400
food(i).y = Rnd * 400
Next i

And here is my renderloop, which is called during the main loop.

'Draw map food
For i = 1 To UBound(food) Step 1
Circle (food(i).x, food(i).y), food(i).amount, vbYellow
Next i

The foodtype has however many elements the user defines, and is public. It has 3 variables, all integers, x, y, and size.
I think it's probably something stupid, but it's been bugging me for a couple hours while I was working on other parts of the program.

RND...testing Numbers In An Array
Hello all,

seasons greetings and many happy eggnogs to you.

For some time I have been writing a dice game (Arrgghhh that was for the hard drive crash) and so I find myself starting over. I did have a lot of logic problems worked out. I still have one that is nagging me to distraction.

there are six dice, I can store the rolled values something like die(1).value = 1
where i am stuck is trying to test die(1) against die(2) and then die(3) and so on, until I have tested all the dice against each other, the scoring depends on knowing if any of the die are the same value
and it goes something like this.
a 1 = 100
a 5 = 50
3 of a kind = that die value * 100
4 of a kind = that die value * 100 +
example ( 3 1's = 1000 (that die value * 100) + 1 more 1 = 2000)
also if you do not roll 3 of a kind or better
and if you do not roll a 1 or a 5 then your roll is finished

so if any one can point me to a good (I am thinking maybe a bubble sort routine) link for testing each die against the rest of them I would be great full.

Randon Numbers From An Array
how would i pick a number from an array of numbers such as

dim randombadguys(1 to 20) as byte

and the array would have say ,... 3,8,5,9,8,0,0,0 and the rest zeros or something. So how do I randomly pick a number from that array excluding the zeros?

2D Array &amp; Random Numbers
I've got a two dimensional array which needs the first and third rows to match. In a "for" loop, I'm creating a random number (0-9)and assigning it to the first element in the array, and then incrementing the column element and repeating the process, giving me four random numbers in row1 (element[0]). Here's what I'm working with:

int i;
int Grid[3][4] = {0};

for (i=0; i<=3; i++)
Grid[0][i] = rand() % 10;
cout << Game[0][i];
Grid[2][i] = Grid[0][i];

Is there a way to set one row equal to another without looping through the two as I have here? Also, each time I run this piece of code I get the same "random" numbers. I tried using randomize() and random() but couldn't make it work. I kept getting erros that randomize and random were not declared variables. How can I ensure the numbers I'm getting differ each time?

Adding Two Numbers In An Array
how can i add a set of numbers in an array. So far my code is multilyng numbers and know i want it to add the numbers and display them in a box.

Option Explicit
Dim iaryNumber(1 To 5) As Integer

Private Sub cmdClear_Click()
'Confirm that the user wishes to exit the program
If MsgBox("Are You Sure you wish to exit ?", vbYesNo, "Confirm Exit ?") = vbYes Then
End If
End Sub

Private Sub cmdEx1_Click()
Dim intResult As Integer
Dim intNumber As Integer
Dim strPrompt As String

For intResult = 1 To 5
strPrompt = "Enter a number"
intNumber = CInt(InputBox(strPrompt, "Numbers"))
iaryNumber(intResult) = intNumber
intNumber = intNumber * 5
picResult.Print intNumber
Next intResult


End Sub

Random Numbers From An Array
I have an array with numbers 1 to 37, I want to randomly select 10 numbers from the array five times and post each number drawn to a textbox, list the 1st ten then the second, third, etc.

Such as;
30 . . .
4) . . .
5) . . .
with no duplicates on any single line.

Thanks in Advance!

If you wish to email me.

Help With Array Problem, Sorting Numbers
Hey guys I need to make a program that takes randomly generated numbers (filled in an array) and then transfers it into another array (byref probably) and in sorts the numbers for lowest to highest in order. Example

array 1 has 98 in slot 1, 56 in 2 ,and 79 in 3
i want it to transfer into array 2 and have for 1 56, and for 2 79, and for 3 98.

Here is what my code has so far. (I ask for the size of the array in my prog.)

Private Sub Form_Load()
End Sub

Private Sub cmdCreate_Array_Click()

Dim intArray() As Integer
Dim intCounter As Integer
Dim intA As Integer
ReDim intArray(1 To txtSize.Text)
intCounter = 1

For intA = 1 To UBound(intArray)
intArray(intCounter) = Int(99 * Rnd + 1)
lst1.AddItem intCounter & vbTab & intArray(intCounter)
intCounter = intCounter + 1
Next intA

End Sub

Private Sub cmdDone_Click()
Unload Me
End Sub

Adding Numbers To A Textbox Using An Array
I am a newbie at this. I have made a calculator and have made an array with the name CmdNumbers. What i need to know is what code string do I use to make the numbers appear in the texbox above?

Calculate Median Value Of Array Of Numbers
I am trying to find out how to calculate the median of an array of numbers. THe array is unsorted. I know how to get the median of a sorted array but not an unsorted. Do I have to sort it to determine the median value

Parsing A String Of Numbers Into An Array
Simple one prob... I am just looking for an efficent way to put an array as such:

string = 21223123131234

and put each numer into an individual array.

array(0) = 2
array(1) = 1
array(2) = 2
array(3) = 2
array(4) = 3


have it loop until the end which is undetermined. I tried and ended up with a mess...

Can anyone help me get started?


Unique Random Numbers In An Array
Could someone please help me out with understanding part of this code.  I can not remember where I got it from a few years ago.  Can someone explain how the mainloop does not generate dupes when ran?  Thanks.

Private Sub GenerateUniqCode()
    ' ---------------------------------------
    ' Produces a series of X random numbers
    ' without repeating any
    ' *** Results can be used by using array B(X) ***
    MaxNumber = 205151 ' Must equal the Dim above in General Declarations Section

    ' Generates the list of numbers
    For seq = 0 To MaxNumber
        A(seq) = seq
    Next seq

    ' Main Loop (mix em all up)
    For MainLoop = MaxNumber To 0 Step -1
        ChosenNumber = Int(MainLoop * Rnd)
        B(MaxNumber - MainLoop) = A(ChosenNumber)
        Dict.Add A(ChosenNumber), A(ChosenNumber)
        A(ChosenNumber) = A(MainLoop)
    Next MainLoop

End Sub


Unique Random Numbers In An Array
Hi everbody,

I have this array called rotor which needs to be filled with random unique numbers from 1 to 127. Every number can be used only once. If I use this code:
Code:For y = 1 To 127
     rotor(y) = Int(127 * Rnd() + 1)
Next y
There is a chance a number occurs more then once and some numbers are missing.

Who has a better piece of code for me?


Please put [RESOLVED] in the subject line off your first post when your done.

Edited by - johnminkjan on 5/30/2004 11:00:37 AM

Find A Slope In Vb6 (numbers Are In An Array)
Hello everyone,

I have this array

Dim mArr1(1 To 10)

mArr1(1) = -46.0
mArr1(2) = 17.1
mArr1(3) = 63.5

and some values that come from a table, ex. 600, 425, 300
Dim mArr2(1 To 10)

mArr2(1) = 600
mArr2(2) = 425
mArr2(3) = 300

now I need to find the SLOPE and INTERCEPT of those values

Presently in the Excel spreadsheet looks like this:


1 -46.0 600
2 17.1 425
3 63.5 300

=SLOPE (A1:A3, B1:B3)

Slope = -2.740219041
Intercept= 473.2539779

very simple ! since the excel functions do all the calculations, but
how can I do it from vb6 ?

Thanks a lot


Odometer Style Array ; Placing Numbers
Hi there!

Say I'm working on an odometer style array for numbers.
I have a routine made, but I don't think its very good

The idea is to place numbers in a position in an array based on the total value of the number.

The array is set up for 6 digits. The odometer will "roll over" after 999,999

Say the number is 512,465

The array's indexes would look like this

NumberPos(0) = 5
NumberPos(1) = 1
NumberPos(2) = 2
NumberPos(3) = 4
NumberPos(4) = 6
NumberPos(5) = 5

So each number from the total is placed into the right position of the array.

Some background:
I'm working on a game where the total score is displayed from custom number bitmaps, rather than using a regular font. The position of each number is stored in an array and needs to be referenced for drawing.

I wrote a little sub to handle the positions, but it's not very elegant at all.
I figured there must be a better way to do this

Here's the prototype:

Option Explicit

Public myNumber As Long

Private Sub Command1_Click()

myNumber = Val(Text1.Text)
SortNumberPosition (myNumber)

End Sub

Public Sub SortNumberPosition(ByRef theNumber As Long)

Dim t As String
Dim NumberPos(5) As String

t = Val(theNumber)

If t > 0 And t < 10 Then
NumberPos(5) = t
ElseIf t >= 10 And t < 100 Then
NumberPos(5) = t Mod 10
NumberPos(4) = Left$(t, 1)
ElseIf t >= 100 And t < 1000 Then
NumberPos(5) = t Mod 10
NumberPos(4) = Mid$(t, 2, 1)
NumberPos(3) = Left$(t, 1)
ElseIf t >= 1000 And t < 10000 Then
NumberPos(5) = t Mod 10
NumberPos(4) = Mid$(t, 3, 1)
NumberPos(3) = Mid$(t, 2, 1)
NumberPos(2) = Left$(t, 1)
ElseIf t >= 10000 And t < 100000 Then
NumberPos(5) = t Mod 10
NumberPos(4) = Mid$(t, 4, 1)
NumberPos(3) = Mid$(t, 3, 1)
NumberPos(2) = Mid$(t, 2, 1)
NumberPos(1) = Left$(t, 1)
ElseIf t >= 100000 And t < 1000000 Then
NumberPos(5) = t Mod 10
NumberPos(4) = Mid$(t, 5, 1)
NumberPos(3) = Mid$(t, 4, 1)
NumberPos(2) = Mid$(t, 3, 1)
NumberPos(1) = Mid$(t, 2, 1)
NumberPos(0) = Left$(t, 1)
ElseIf t > 0 And t >= 1000000 Then
'Roll back to zero
theNumber = 0
'Redisplay number with 2 leading zeros to start
NumberPos(5) = "0"
NumberPos(4) = "0"
NumberPos(3) = ""
NumberPos(2) = ""
NumberPos(1) = ""
NumberPos(0) = ""
End If

'Print for testing
Print NumberPos(0); NumberPos(1); NumberPos(2); NumberPos(3); _
NumberPos(4); NumberPos(5)

End Sub
As you can see, I converted the number to a string and did a bunch of manipulation to it.

There's got to be a better approach than that

Thanks for looking!


Display The Average And Highest Numbers In An Array
Currently this command button tells me the lowest number in the array and how many times it is in the array. I am trying to get it to also tell me the highest number in the array, the average score of the array and how many numbers are in the array. My goal is to have these three items displayed via a message box. I appreciate any input.


Private Sub cmdFind_Click()
Dim intScore As Integer, IntLow As Integer
IntLow = 1
For intScore = 2 To 20
If intNums(intScore) < intNums(IntLow) Then
IntLow = intScore
End If
Next intScore
lblLowNum.Caption = intNums(IntLow)
lblNumStud.Caption = "3"
End Sub

Array Help (generating Unique Random Numbers)
ok the problem i have is i want 6 random numbers to be generated inside 6 text boxes i then want the 6 boxes to be checked so they dont have


spent = spent + "1"
lblSpent = ("" & spent)

For x = 0 To 10

ball(x) = Int(Rnd() * 10) + 1

Next x

For x = 0 To 5

lblResult(x) = ball(x)
Next x

lstWinningNums.AddItem (lblResult(0) & " " & lblResult(1) & " " & lblResult(2) & " " & lblResult(3) & " " & lblResult(4) & " " & lblResult(5))

that happily givs me mynumbers, so how do i use an array to check each box for the same numbers then get it to genrate a new number

Read Numbers From Txt File, Then Create Array
Hi there,
I want to count the amount of lines in a txt file (one number per line and total of lines in file might change every time),Then create an array(based on the amount of lines),Then read the numbers from that txt file into created array.
I do not work with vb on daily basis, and can not get it to work.

Pick Random Numbers From Prime Number Array
Hi all,

I am wondering if someone can show me how to create an array of prime numbers and then get the vb program to randomly select 2 random number from an array of prime numbers already coded into the program.

The array does not have to be created just a static array with 20 prime numbers for the program to chose from.

I would have 2 text boxes then the program randomly pick 2 random prime numbers and puts them into each of the 2 text boxes.


Math Question - How To Remove Values Way Different From Others In An Array Of Numbers
Lets say in an array I have 20 items


how can i 'detect' that 353622 is wildly different from others in the list, as is '534'? And if i wanted to be mroe stringent.. detect '89233' as different too?


What is the best way to iterate a For...Next loop ?

Assume I have the next code

X="This is a test"

For I=1 to words("This is a test")

if word(X,I)="is" then ???? <--- Jump back to >>For<< statement
next I

I want to jump back to the >> For I=1 to words... << line when the word >> "is" << is found

I am more used to REXX and there one would use >> Iterate << where the question marks are.
No doubt VB has something similar... but I cannot find it.

I am using VB6.

Best regards, Jan

Iterating The ROT !
Is there a way of iterating the ROT(Running Objects Table) via the API so I can find and automate other running applications ?


How Do I Fill An Array With Specific Numbers (loaded From A Text File)?
I want to create an array of 100 elements which will store a series of both positive + negative Single and Integer numbers. When the program starts, each element in the array needs to be filled with a specific value (which is assigned directly from a text file). When the program is running, I will need to be able to change the value of each element in the array using different math operators. The array must be accessible throughout the program. I also need to save the new array back to a (another) text file when the program finishes.

Let's say the first 5 elements (of 100) should be as follows:
-5, 0, 6.2, 1056, -17.4

(a) How do I create an array solely for use with numercial data?
(b) How do I fill that array with specific values loaded from a text file (let's assume each value in the text file is separated by a comma)?
(c) How do I easily update/change an element within the array after it has been loaded?
(d) How do I write the updated array to a new text file?

I'm very new to this, so your comments and assistance would be very much appreciated.


Iterating Through Controls
hi all

i have a form in Access 97.. and i want to iterate through all the controls which i have on the form using the beforeupdate event of my form.. the controls which i have are as follows below, but its just giving me the error message and going to add a new record.. i want it to populate a message box stating which controls are missing and setting focus to the first missing control is finds.. i dont know if i am thinking right but i was thinking of iterating through all the controls... can someone help and provide me with an example..

thanks in advance

If [txtEmp_IdCard_No] = "" Or [year] = 0 Or [txtDate_From] = Null Or [txtDate_To] = Null Or [Frame47] = Null Or [Frame53] = Null ThenIf [txtEmp_IdCard_No] = "" Or [year] = 0 Or [txtDate_From] = Null Or [txtDate_To] = Null Or [Frame47] = Null Or [Frame53] = Null Then

msgbox "missing information"
end if

Iterating Through TextBoxes
Hello everyone, just found this site and it seems to be an amazing resouce, however, I have been searching for the past couple hours and have come up with very little on my problem. Here it is:

I have a UserForm with 64 text boxes representing different values to be inputed. Now, I can brute force the Textbox1.value =, textbox2.value =, etc. etc. but I would rather not because the code is becoming extremely complex (There are a variable amount of colums up to 8, and variable amount of rows up to 8), which makes a whole HUGE mess out of case statements and for loops that I would rather avoid.

So, my question would be how to write a loop that could basically do the following, except generalize it for bnAdd1,2,3...n, and of course Levels 11,12,..., ij.

Public Sub bnAdd1_Click()

vStartDate = cbStartDate.Value
vEndDate = cbStartDate.Value
vWinner = Winner1.Value
vTradeSize = FinanceQuoteCreation.TradeSize1.Value

If vTradeSize < 0 Then
vType = False 'this means you are borrowing
vType = True 'this means that you are lending
End If

For i = 0 To numinstruments - 1
iLineNum = i + 1
Select Case iLineNum
Case 1
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID1
vLevel1 = Level11.Value
Case 2
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID2
vLevel2 = Level21.Value
Case 3
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID3
vLevel3 = Level31.Value
Case 4
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID4
vLevel4 = Level41.Value
Case 5
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID5
vLevel5 = Level51.Value
Case 6
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID6
vLevel6 = Level61.Value
Case 7
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID7
vLevel7 = Level71.Value
Case 8
vTradeInstanceID1 = FinanceQuoteCreation.InstrumentID8
vLevel8 = Level81.Value
Case Else
Debug.Print "Cannot Enable."

End Select
Next i
I do not need all the code, just how one would iterate through something like this. The rest I can figure out by myself.

Thank you very much for any help that you can provide. I would be happy to let everyone know the final solution, as it is no doubt useful for people working with input forms like this in Excel.

- Pete

Iterating Objects
I've created a form with about 50 checkboxes. I would like to create a loop that will sequence through all 50 checkboxes to find the one that is 'checked'. I need something like:

dim checkbox as variant
for i = 1 to 50
checkbox = "Check" & i & ".Value"
if checkbox = true then
call function()
next i

This loop doesn't work because checkbox = "Check1.Value" rather than "true", so the if-then statement returns a false and doesn't call the function. How do I fix this?

Iterating Through Comboboxes
I'm working in Excel 2000 and I'm trying to iterate through a bunch of combo boxes (1000+) to change the properties of the combo boxes (changing the linked cell to different values), but I don't know how. I've searched far and wide on the internet and seen a few solutions to this issue, but none of them seem to work for me. The control arrays don't seem to work in Excel 2000 and the for each ctrl in me.controls or msforms.controls method creates a 438 run time error (I have the vba in a private sub under sheet 1). Any suggestions? Thanks in advance.


A Little Help {Iterating Through All Forms}
This code is just searching through the forms that have already been loaded. Is there a way to search through all the forms in the project?

For Each CurForm In Forms
MsgBox CurForm.Name

Iterating Files
which is better for iterating files?

-Dir$ ()


-the Shell interface

anything else?

Iterating Through Combo Box.
I need to figure out how to iterate though a combo box.
I am pulling text from a MSFlexGrid from one form and checking to see if it exists in the combo box of a different form... if it does, then it assigns the combo box that text, otherwise, it selects the text "unassigned" in the combo box.

Here is the code that I have... I think that it looks like it is partially correct, but I can't figure out how to iterate through the items in the combo box.

With frmMain
.MSFlexGrid.Col = 0
txtCRNumber.Text = .MSFlexGrid.Text
.MSFlexGrid.Col = 1 'Col 1 holds the Category
For i = 0 To cboCategory.ListCount
'Need to iterate though the combo box here... I think.
If cboCategory.Text = .MSFlexGrid.Text Then
cboCategory.Text = .MSFlexGrid.Text
'This would happen if someone deleted the category from the list items, then try to edit the CR.
cboCategory.Text = "Unassigned"
End If
End With

Any suggestions on this?

Help! Iterating On Enums
Hi , i there a way to iterate on the elements of an enum, or at least to know if a long number belongs to that enum ???

Iterating Through Files
I want to write a backup app.

How do you iterate through each directory on a specific drive and each file within a specific directory?

Iterating Through A Type
Is it possible to make a cycle through the elements of a Type?

When we manage a dynamic array, we use lbound and ubound functions to iterate through the array.

If I declare a Type, then make it dynamic, its easy to iterate through the records, but... through the fields????

Type MyType
str_A as string
str_B as string
int_I as integer
End Type

dim X() as MyType
redim X(0 to 5)

Any idea???

VBA - Iterating Through Custom Collections
Is it possible to replicate completely the functionality of a Collection object in a custom Collection?

The .Add, .Count, .Item and .Remove members are easy to implement, but can a custom collection be iterated with a For Each...Next loop just as normal Collection by any means? In these boards I came across an interesting tutorial to do that in VB, but it doesn't seem to work for VBA, so I have to use a For...Next loop instead.

This is my draft until now:

'Code in a class module called "DoubleSpaces"
Option Explicit
Dim DoubleSpacesCollection As New Collection

Public Function Item(Index As Variant) As Range

Set Item = DoubleSpacesCollection.Item(Index)
End Function

Public Function Count() As Long

Count = DoubleSpacesCollection.Count
End Function

Public Sub Correct()
Dim ar As Range

For Each ar In DoubleSpacesCollection
ar.Text = " "
Set ar = Nothing
End Sub

Public Sub Highlight()
Dim ar As Range

For Each ar In DoubleSpacesCollection
ar.HighlightColorIndex = wdBlue
Set ar = Nothing

End Sub

Private Sub Class_Initialize()

Dim DocRange As Range
Dim PutIn As Range

Set DocRange = ActiveDocument.Range

With DocRange.Find
.Text = " "

While .Execute
DocRange.MoveEndWhile " "
Set PutIn = ActiveDocument.Range
PutIn.SetRange DocRange.Start, DocRange.End
DoubleSpacesCollection.Add PutIn

DocRange.Collapse wdCollapseEnd
Set PutIn = Nothing

End With

Set DocRange = Nothing
End Sub

ADO: Iterating Through The Fields Collection
I'm using an ADO recordset to load data from a text file. I'm running into a problem reporting back errors in the data.

I'm able to identify the rows that are erroring by using the Status property of the recordset:

Do while not m_objAdoRs.EOF
Debug.print m_objAdoRs.Status
However, I also want to identify the specific columns/fields that are causing the errors, so I though this would work.

Do while not m_objAdoRs.EOF
If m_objAdoRs.Status = 4096 Then 'these are rows that violate integrity constraints
For i = 0 to (m_objAdoRs.Fields.Count - 1)
If m_objAdoRs.Fields(i).Status <> 0 Then
Debug.print m_objAdoRs.Fields(i).Name
End If
End If
But the problem here is that it reports the same field errors for every row. For instance, say if there is a problem with column 3 (fname) in row 1 and column 8 (age) in row 2, I will get this as a result

It will report the following:
Line 1 Errors: fname, age
Line 2 Errors: fname, age

When it should report:
Line 1 Errors: fname
Line 2 Errors: age

Anyone have any experience with this and can offer a solution??

Thanks in advance.

Iterating Blank Fields
I have a field which is a substring of a line coming from a text file I am looping through.
Do While Not EOF(1)
Line Input #1, LineOfText
fielda=Mid(LineOfText, 39,30)

I am checking to see if the field/substring is not blank.
IF Fielda <> " "

The problem I'm running into is the field/substring has anywhere from 1 blank character to 30 blanks characters in it.
Fielda is equal to
" " and anything inbetween or up to 30 blank characters

Is there a way I can check to see if the field is blank regardless of how many blank characters the field has?


Iterating All Worksheets In Excel
This is to be working for me just fine for the FIRST workbook I open, then if I try to open another, the For Each s in Sheets throws this error:
Method 'Sheets' of object '_Global' failed

I've done quite a bit of interfacing to Excel but I always get confused with the Workbook, Worksheet, etc. Declarations. Maybe I need another?

VB Code:
Option Explicit Dim objExcel As New Excel.Application Private Sub Command1_Click() On Error GoTo ErrHandler         Dim s As Worksheet   Dim Filepath As String   Dim SheetNames(25) As String      dlgCommon.DialogTitle = "Open File"   dlgCommon.Filter = "Excel Files(*.xls)|*.xls"   dlgCommon.ShowOpen   Filepath = dlgCommon.FileName   With objExcel      .Workbooks.Open Filepath      For Each s In Sheets         .Sheets(s.Name).Select         GetSum      Next            .ActiveWorkbook.Save      .Workbooks.Close   End With   MsgBox ("DONE") ErrHandler:   Debug.Print Err.Description   Debug.Print Err.Number   objExcel.Workbooks.Close   objExcel.Quit   Set objExcel = NothingEnd Sub Private Sub GetSum()      Dim Cell As Integer      With objExcel      .Range("E100").Select      .Selection.End(xlUp).Select 'ctrl      Cell = Right(.ActiveCell.Address, 1)      .Cells(Cell + 2, 5) = "=SUM(E1:E" & Cell & ")"   End WithEnd Sub Private Sub Form_Unload(Cancel As Integer)    objExcel.Quit   Set objExcel = NothingEnd Sub

Iterating Over ASCII Characters

I want to use a for loop that iterates through the ASCII chars.

in c/c++ you can do this.

char asciichar = 'A';
for(int i = 0; i < 25; i++)

At the end, asciichar = 'Z'

Any help appreciated...TIA

Iterating All Worksheets In Excel
How can I iterate all the worksheets in Excel with the Macro->Visual Basic? Or does this matter if it's a Macro or not. Not totally familiar with VB6 so bear with me.

Iterating Through A Checked ListView
I have a ListView with checkboxs that contain some information on our insurance agents. I need to iterate through the ListView and make an update to my SQL tables for each item in the ListView that is checked. However, when I run this code, it does iterate through the listview but the only thing that ever gets plugged into my UPDATE is date from the very first row in the listview. This happens whether the first row is checked or not. Where am I going wrong?
VB Code:
With lvwAgents    For i = .ListItems.Count To 1 Step -1        If .ListItems.Item(i).Checked = True Then          sSQL = "UPDATE agents_commission SET agent_fee = NULL "          sSQL = sSQL & "WHERE agent_id = '" & lvwAgents.SelectedItem.Text & "' "          sSQL = sSQL & "AND week_end_date = '" & lvwAgents.SelectedItem.SubItems(2) & "' "          sSQL = sSQL & "AND agent_code = '" & lvwAgents.SelectedItem.SubItems(4) & "' "          sSQL = sSQL & "AND account_code = '" & lvwAgents.SelectedItem.SubItems(5) & "' "          Debug.Print sSQL        End If     NextEnd With
Incidentally, this code is being run from a command button that will be clicked once all of the desired items have been checked.

Iterating Through A Folder And Sub Folders
Ok, what Im attempting to do is to look at all files in a folder and all its sub folders. I found the following code in my reference book Programing Visual Basic 6.0, and it works find for finding all files in the Folder path.

VB Code:
' The following function below needs to be modified to do the following:' 1). Verify that there are files in the directory'     a). If there are files, to check the time stamp on them and compare that'         against what was the previous time stamp stored on the .ini file'  2). If there are sub directories, check the time stamp on these also against'         what was the previous time stamp stored in the .ini file. Private Function GetFiles(filespec As String, Optional Attributes As _    VbFileAttribute) As String()    Dim result() As String    Dim filename As String, count As Long, path2 As String    Const ALLOC_CHUNK = 50    ReDim result(0 To ALLOC_CHUNK) As String    filename = Dir$(filespec, Attributes)    Do While Len(filename)        count = count + 1        If count > UBound(result) Then            ' Resize the result array if necessary.            ReDim Preserve result(0 To count + ALLOC_CHUNK) As String        End If        result(count) = filename        ' Get ready for the next iteration.        filename = Dir$    Loop    ' Trim the result array.    ReDim Preserve result(0 To count) As String    GetFiles = resultEnd Function

I walked this and watched the value of variable filename, and never seen a subdirectory appear. Now, the value of filespec at the time this function was called was c:* so I thought sub folders would also be caught with the Dir$
c:* is not what this value will be set in real time, but I was walking the code to see if what I was trying to accomplish would work.
Any sugestions on how I can also see the sub directories in the directory list???
Any and all help always appriceated.

*Answered* Re-iterating Characters
Just a quickie.

How can I write:




in short code. I want to fill a string variable with a variable number of the same character.


Copyright 2005-08, All rights reserved