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

Decreasing For Loop

How do I make a for loop from high value to low, something similiar to this:

for (x=100;x<2;x--)

Will the above work or how do I do that kind of loop?

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
For Loop In Decreasing Order
I will input a number, maximum is 10 only,
if I input 9
how can I display the number 7 in label1?

I think my code is wrong...

VB Code:
Private Sub Command1_Click()Dim x, y As Integery = Val(Text1)For x = 0 To y Step -1    If (y = 2) Or (y = 3) Or (y = 5) Or (y = 7) Then    Label1.Caption = Val(Text1)    End IfNextEnd Sub

Ever Decreasing Circles...
I have a picture box that I have been drawing tiles on using the Polygon API.

What I now want to do is, based on a user entered value relating to X and Y of the picturebox, draw a circle on the picturebox. I then want to erase this circle and draw a smaller one, and a smaller one etc to a point.

How do I do this? I can use the ellipse API to draw each of the circles, but how do I:

a.) Erase the circle that I've just drawn?
b.) Not erase the image already there?

I was thinking that I could have another picturebox under the first one, draw the first circle on it, then erase it and draw the second etc.

Would this be the best way?

Decreasing Counter
Here's what I am trying to do :

For cCounter = 10 To 1

I remember it could be done this way but it seems it doesn't work

The program simply pass this instruction, as if the condition was met.
How can I code a decreasing counter ?

Many Thanks

Decreasing Image Size (from 2.2mb To Under 1mb)
Im trying to send a picture via winsock but the thing is the picture is 2.2mb in size and takes about 1.5 seconds to transferre. How would I reduce that images size to under 1mb (preferably around 200kb) to speed things up?

Increasing Or Decreasing Volume?
Hey guys

I think this is the right place to post this. Is there an API to either decrease or increase the volume being outputted to my speakers?


Decreasing Figures In A Database From VB
Hi, I have a school assignment where I have to link between Access and VB. My assignment is kind of like a mini virtual games store.

In Access I have a table titled "Stock" and in that table I have a field named "InStock".

In VB, under each game there is a "Buy" command button. I want to be able to click the buy button and then my stock decreases by how many times I press it (InStock) for that game.

The table looks like this (In Access):

StockIDStockDescription InStockStockTypeCost
1Warcraft III: Frozen Throne24Game $29.95
2Warhammer 40000 26Game $39.95
3Age Of Empiries III 23Game $59.95
4Lord Of The Rings 65Game $29.95
5Lord Of The Rings II34Game $99.95
6StarCraft: Brood War64Game $29.95
7Rome: Total War 23Game $49.95
8Age Of Mythology 34Game $39.95
9Rise Of Nations 53Game $89.95

Can anyone help me?

EDIT: The table looks dodgy when pasted in here.

Decreasing Month On The Date

the command in increasing the Month on the Date is DateAdd what about if u r going to decreasing the month of the Date. help please....


Decreasing Memory Consumption ?

I have written a client application in VB.
This client application calls few dlls in the code.
I divided the code, so that it may take less memory and load the dlls whenever needed only.
But still my Exe is taking more memory.
When i call dll , for ex: dim a as new DLL and then at the end of the routine if i say, set a=nothing; will it not release the memory took for dll ?

Can anybody give some good suggestions to consume less memory ?
my exe size is just 236 KB and i compiled to p-code.

Thnking you


Decreasing Memory Of 30 Forms
Hello everyone...I am still new here.
I work for a project that will need about 30 forms. I do this using the usual form (not MDI form). But many times I run this project or when I typed additional code, it gave message "Out of Memory".
My project work like this : It begin with the Form1. After all routines in Form1 is completly done, the Form2 is shown up and the Form1 is unloaded. The Form2 will also work like the Form1. This will continue until the Form30.
Could anyone help me how to decrease the overloaded memory ?



Using .sort Method To Decreasing Records

I need to sort an recordset on decreasing order, how can i do this using recordset.sort? .sort is increasing default. Im no using an SQL query so no way to use "ORDER BY DESC", thanks all!

Decreasing Size Of Word File ?

i have one word file, empty (no text, no image). it contents code and forms.

i have made it all in one time (saved only one time, so the size have not been increase...).

But, its weight is more 500Ko...
Is There a possibility to compact it more ??? as a compiling the forms ?

Thx for any help


sorry for bad english, it is not my main language...

A Couple Of Simple Questions On Decreasing Values And An Error Message
VB Code:
Private Sub Command1_Click()Dim First, Second, Third First = Int((9 * Rnd) + 1)Second = Int((9 * Rnd) + 1)Third = Int((9 * Rnd) + 1) Text1 = FirstText2 = SecondText3 = Third If First = 7 And Second = 7 And Third = 7 Then    MsgBox "Congratulations! You spinned a triple 7!": EndIf First = Second And Second = Third Then    MsgBox "Triple same number!": End End Sub

This is a code for a slot machine game.
I also want to apply money system in there, but I can't figure out how to even start.

Putting a dollar($) sign is a next thing. I can't even figure out how to decrease the value every time a person clicks the Spin command button.

I've tried Money = -50, which, of course, didn't work.
I've tried Money - 50 and didn't work either.

What should I do?

And how can I put a dollar sign before the integer value? Like, on the main form, there's a label saying "Money: " and another label saying "9000" for the default money value. I've tried "Money: $" but it placed a space between $ and the value. I need help on this one also.

Lastly, when I clicked the command button, it said,
"Compile Error:
Block If without End If"
What does it mean Block If and why doesn't it like it?
I've tried putting End If before End Sub but it still said the same thing.

Thanks in advance.


Decreasing Security In Outlook, (program Is Trying To Access Data From Outlook)
When I run macro I get following message:
A program is trying to access data from Outlook that may include address book information. Do you want to allow this?

How to avoid it? (Outlook 2003 on Windows 2000 professional)

Have nice day

Do/Loop,Need To Nest Another Loop To Check For Duplicates(FIGURED IT OUT)
check it out, what i am doing is reading in from my DB, customer orderID's. The table that i am reading from will have multiple OrderID's and some will be duplicates. That all fine but i dont want to have order number 115 show up 3 times when 1 time would be enough. This is the loop that i am using and am loading the values in the the combo box. What i would like to do, is once i hit this line of code

Combo2.List(i) = ![OrderID]

then do either a for each , or for next loop to walk through the contents of combo2 and check for a duplicate, if a duplicate is found i would like to take the current orderID that was just loaded out of the combo2 box. I have tried a for each loop but i am not geting the syntax correct of what exactly i am trying to search through. That where you guys come into play. could some one please help with this problem

Do Until .EOF
   Combo2.List(i) = ![OrderID]
   i = i + 1

This is what i would like to accomplish.
DO Until .EOF
    load orderID into combo box
        For Each orderID in combo box
             If duplicate found then
                  delete current entry
             end if
     continue loading orderid's

Edited by - lmf232s on 7/24/2003 1:59:30 PM

Game Loop Without Creating An Endless Loop?
im trying to setup a loop for my game to draw the scene as many times as it can each second, however, i end up simply creating a loop that stops basically everything. timers do not function, command buttion clicks are ignored, not what i wanted.

so, then i tried a timer, but even at a setting of 1 it was too slow for my purpose. (unless you want to play the game limited to 10 FPS ) so, is there any faster timer system, or a differnt loop system i didnt think of?

Creating A Dataset In A Loop (i.e. For Loop)
Dear Expert,

I'm having problems creating a dataset in a for __ next loop which I intend loading into a crystal report writer.

Please provide me with a sample code to perform the above.


Loop The Loop: Adding List Item And Sub-items Assigned To Items.
Hello there,

below are the codes i've written to record all the items (which are qnsID) in list box (List1) into qnsdata_table.

Private Sub Command6_Click()
Dim x As Integer
Dim y As Integer

x = List1.ListCount
y = 0

rst.Open "SELECT * from qnsdata_table", cn, adOpenDynamic, adLockOptimistic

Do While Not y = x
rst1.Open "SELECT qnsdataID from autonumber", cn, adOpenDynamic, adLockOptimistic
rst!qnsDataID = "qd" & Right("000000" & CStr(CInt(rst1!qnsDataID) + 1), 6)
rst!qnsID = Me.List1.ItemData(y)
rst1.Open "UPDATE autonumber SET qnsdataID = qnsdataID +1", cn, adOpenDynamic, adLockOptimistic
y = y + 1

End Sub

The problem is that for each item (qnsID) there are several assigned (ansID) and i need them to be recorded along with the qnsID, into qnsdata_table.
| qnsdataID | qnsID | ansID |
| qd0001 | qns0002 | ans0012 |
| qd0002 | qns0002 | ans0016 |
| qd0003 | qns0002 | ans0001 |

In this case, should i have another Do While Loop within the current Loop, to find out how many 'ansID' there are which are assigned to each 'qnsID'.

I've tried

rst2.Open "SELECT Count(ansID) AS ansID_count FROM ans_table where....(conditions)

Me.Text2 = rst2!ansID_count '(ive no idea where to store the count) value)

a = Me.Text2 '(shift value into variable a)
b = 0
and here goes another loop, but it didn't work.

Need some advice and comments from you guys =)

Thanks a million.


Loop Inside Of A Loop?
Is there a way to do this, for some reason this code prints 0s in columns 2,3 and 4. Any ideas? Thanks in advance!

Sub Monte_Carlo()

Dim portfolio_Percentage(), avg_Return(), st_Dev()
Dim stock_Num, var_A, var_B, k(), rDraw
Dim sNew(), sOld()


' *Read input data and redimensions dynamic arrays
stock_Num = Worksheets("Inputs").Cells(2, 9).Value

ReDim portfolio_Percentage(1 To stock_Num)
ReDim avg_Return(1 To stock_Num)
ReDim st_Dev(1 To stock_Num)
ReDim k(1 To stock_Num)
ReDim sOld(1 To stock_Num)
ReDim sNew(1 To stock_Num)

'*Initialize sOld
For var_B = 1 To stock_Num

sOld(var_B) = 0

Next var_B
'*User Input Variable Storage Loop
For var_A = 1 To stock_Num

var_B = 1

portfolio_Percentage(var_B) = Worksheets("Inputs").Cells(var_A + 1, 3).Value
avg_Return(var_B) = Worksheets("Inputs").Cells(var_A + 1, 4).Value
st_Dev(var_B) = Worksheets("Inputs").Cells(var_A + 1, 5).Value

var_B = var_B + 1
Next var_A

'*Brownian Element
var_A = 1

For var_A = 1 To stock_Num
k(var_A) = avg_Return(var_A) - st_Dev(var_A) ^ 2 / 2

Next var_A

For var_A = 1 To 250
For var_B = 1 To stock_Num

rDraw = Application.NormSInv(Rnd)

sNew(var_B) = sOld(var_B) + 1 - Exp(k(var_B) * 1 / 250 + st_Dev(var_B) * rDraw * Sqr(1 / 250))

Worksheets("output").Cells(var_A, var_B).Value = sNew(var_B)

sOld(var_B) = sNew(var_B)

Next var_B
Next var_A

End Sub

How To Change A For Loop Into A Do While Loop
I changed this:

intTotal = 0
For intOuterCounter = 2 To 5 Step 2
For intInnerCounter = 2 To 4
intTotal += intInnerCounter
Next intInnerCounter
Next intOuterCounter

into this:

intTotal = 0
intInnerCounter = 2
intOuterCounter = 2
Do While (intOuterCounter >= 2 And intOuterCounter <= 5)
intOuterCounter += 2
Do While (intInnerCounter >= 2 And intInnerCounter <= 4)
intInnerCounter += 1
intTotal += intInnerCounter

In the first loop I get 18, then in the second I get 12. The goal is to get the same result. What's wrong?

Is Is Recommanded To Do The For .. Next Loop Into Another For .. Next Loop ?
I try to find another approach
For i = 0 To item
If number = 0
For ii = 5 To solution
Next ii
End If
Exit Sub
Next i

The Game Loop Must Be Achieved With &"Do Loop&"?
I found "Do loop" takes too much attention of windows, that other "call sub" within the "Do loop" were seldom done, or were delayed badly.
So how could we solve this usually?
Thank you!

To Loop Or Not To Loop
every redraw in my directx 7.0 directdraw mmorpg the client loops threw the map tiles array, the items, and the online characters. Should all this be done? cuz im getting like 4fps i know it has to be done but is there an easier way? ive heard of using classes burt i dun understand how 2 use them or how they would help. characters are based and registered in the array according to charID so say the persons id is 4 then when they connect an entry is placed in the array as Character(4) and so on. So can anyoone tell me a way to get my framerate up??? thanx


how can I Loop my code?
I want to check If a column is empty!
but now he checks only the first record and I want to check all records.
I hav ethis:

Set s = CurrentDb.openrecordset("tablename")
If (s![Columnname]) = " " Then
MsgBox "Columnname is empty", vbOKOnly, cstrAppTitle
GoTo Error
End If
If (s![Columnname]) = " " Then
MsgBox "Columnname is empty", vbOKOnly, cstrAppTitle
GoTo Error
End If

thanks in advance

I'm really desperate to solve this, I hope somebody can help me! the following is my code:

For each varItem In List.ItemsSelected
strName = List.Column(1, varItem) & vbCrLf
With oApp.Selection
.Goto What:=wdGoToBookmark, Name:="Bookmrk1"
.TypeText Text:=strName
End With
Next varItem

I have a multi-select listbox and this code is behind a command button and it will pass the selected Item to word document but it is in inverted order. Any advice?

Thanks a lot,

For Loop In VBA

In C,C++ and Java there is a For Loop that allows looping of more than one variables at the same time.

for (int i = 0, x= 0; <condition>; ++i, ++x){}

Can you do the same in VB.

My problem is :
Dim aRng As Range
Dim aCell As Range

Set copyCell = Range("B23:B27")
Set aRng = Range("A23:A27")

For Each copyCell In copyCell.Cells
For Each aCell In aRng.Cells

copyCell.Value = Application.WorksheetFunction.VLookup(aCell, Worksheets("Milestones").Range("A1:B15"), 2, False)

Next aCell, copyCell

Ideally i would like to loop through both aCell and copyCell together rather than continue looping aCell and when finished then loop through copyCell

Any help would be greatly appreciated.


The code below is a macro for a Word 97 document that looks for the occurrence of the word "None." and then selects the line and the the above 2 lines and then deletes all three lines.

The code works fine, but I need it to go one step further.

"None." occurs several times in the document and I need the macro to delete all occurrences of the "None." and the corresponding 2 lines above.

Any help of how to use a loop here would be great.


Sub FindNone()
Set myRange = ActiveDocument.Content
myRange.find.Execute FindText:="None.", Forward:=True
If myRange.find.Found = True Then myRange.Paragraphs(1).Range.Select
With Selection
.EndOf Unit:=wdParagraph, Extend:=wdMove
.MoveUp Unit:=wdParagraph, Count:=3, Extend:=wdExtend
End With
End Sub

Help With For Each Loop Please
The for each loops always throw me for a loop (my apologies for that one ). Anyway, I'm trying to cycle through each checkbox in a form. Could someone tell me what I'm doing wrong. Thanks!!!

Dim chkBox As CheckBox
For Each chkBox In Me.Controls
MsgBox chkBox.Name
Next chkBox

Using The Loop
I'm a newbie to vba.

My question is:
How do I use the loop to make a diagram with different position, size, colour and text connected to various rectangles?

I can do it as in the example below, but that takes a lot of lines, so I imagine there must be another way around, like using the loop.

Can anybody help me?


Set myDocument = Worksheets(1)

With myDocument.Shapes.AddLine(0, 350, 700, 350).Line
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(0, 0, 0)
.Weight = xlThin 'Tykkelse: xlThin, xlMedium xlThick
End With

' Bar 1
With myDocument.Shapes.AddShape(msoShapeRectangle, _
30, 300, 100, 50)
.Fill.ForeColor.RGB = RGB(255, 0, 0)
End With

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 360, _
35, 15).Select
Selection.Characters.Text = "Name 1"
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

' Bar 3

With myDocument.Shapes.AddShape(msoShapeRectangle, _
160, 150, 50, 200)
.Fill.ForeColor.RGB = RGB(0, 255, 0)
End With

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 360, _
35, 15).Select
Selection.Characters.Text = "Name 2"
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

' Bar 3

With myDocument.Shapes.AddShape(msoShapeRectangle, _
240, 250, 80, 100)
.Fill.ForeColor.RGB = RGB(0, 0, 255)
End With

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 250, 360, _
35, 15).Select
Selection.Characters.Text = "Name 3"
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

For Next Loop
Working with Excel VBA

What I have will work, I just want the code to look cleaner, make the file size smaller, and make the macro run faster. Will the For Next accomplish all this? How can I fix the code to make the "want" code work? Do I have to use arrays? I tried "KeyIssue & i" but it didn't work either.


this is what I have:

'Dump KeyIssue1
Application.EnableEvents = False 'off for speed
If KeyIssue1 <> Empty Then
shKeyIssuesCurrentRow = shKeyIssuesCurrentRow + 1
Sheets(shKeyIssues).Range("B" & shKeyIssuesCurrentRow).Value = IRC1
Sheets(shKeyIssues).Range("C" & shKeyIssuesCurrentRow).Value = PotentialImpact1
Sheets(shKeyIssues).Range("D" & shKeyIssuesCurrentRow).Value = TrackingID
Sheets(shKeyIssues).Range("E" & shKeyIssuesCurrentRow).Value = KeyIssue1
Sheets(shKeyIssues).Range("G" & shKeyIssuesCurrentRow).Value = B_Start1
Sheets(shKeyIssues).Range("H" & shKeyIssuesCurrentRow).Value = PRI1
Sheets(shKeyIssues).Range("I" & shKeyIssuesCurrentRow).Value = Owner1
Sheets(shKeyIssues).Range("J" & shKeyIssuesCurrentRow).Value = ActionRequired1
Sheets(shKeyIssues).Range("K" & shKeyIssuesCurrentRow).Value = B_Finish1
End If
'Dump KeyIssue2
If KeyIssue2 <> Empty Then
shKeyIssuesCurrentRow = shKeyIssuesCurrentRow + 1
Sheets(shKeyIssues).Range("B" & shKeyIssuesCurrentRow).Value = IRC2
Sheets(shKeyIssues).Range("C" & shKeyIssuesCurrentRow).Value = PotentialImpact2
Sheets(shKeyIssues).Range("D" & shKeyIssuesCurrentRow).Value = TrackingID
Sheets(shKeyIssues).Range("E" & shKeyIssuesCurrentRow).Value = KeyIssue2
Sheets(shKeyIssues).Range("G" & shKeyIssuesCurrentRow).Value = B_Start2
Sheets(shKeyIssues).Range("H" & shKeyIssuesCurrentRow).Value = PRI2
Sheets(shKeyIssues).Range("I" & shKeyIssuesCurrentRow).Value = Owner2
Sheets(shKeyIssues).Range("J" & shKeyIssuesCurrentRow).Value = ActionRequired2
Sheets(shKeyIssues).Range("K" & shKeyIssuesCurrentRow).Value = B_Finish2
End If

This is what i want:

'Dump KeyIssue1 to KeyIssue5
Application.EnableEvents = False 'off for speed
For i = 1 to 5
If KeyIssue(i) <> Empty Then
shKeyIssuesCurrentRow = shKeyIssuesCurrentRow + 1
Sheets(shKeyIssues).Range("B" & shKeyIssuesCurrentRow).Value = IRC(i)
Sheets(shKeyIssues).Range("C" & shKeyIssuesCurrentRow).Value = PotentialImpact(i)
Sheets(shKeyIssues).Range("D" & shKeyIssuesCurrentRow).Value = TrackingID
Sheets(shKeyIssues).Range("E" & shKeyIssuesCurrentRow).Value = KeyIssue(i)
Sheets(shKeyIssues).Range("G" & shKeyIssuesCurrentRow).Value = B_Start(i)
Sheets(shKeyIssues).Range("H" & shKeyIssuesCurrentRow).Value = PRI(i)
Sheets(shKeyIssues).Range("I" & shKeyIssuesCurrentRow).Value = Owner(i)
Sheets(shKeyIssues).Range("J" & shKeyIssuesCurrentRow).Value = ActionRequired(i)
Sheets(shKeyIssues).Range("K" & shKeyIssuesCurrentRow).Value = B_Finish(i)
End If
Next i
Edit by italkid: The use of [vb]][ and [/vb] tags makes your code better readable...

Something Better Than A Loop?
Hello All...

Is there a better (faster) way to do this?
As you can see I'm simply wanting to get rid of all instances of "QC", "EP" Etc...
I have thought of doing a "Find...Replace" and then filtering for the replacement value and deleting...?
Or is there an alltogether better way to execute this?

Dim EndRow As Integer
EndRow = Range("G65536").End(xlUp).Row
Do Until ActiveCell = ""
If ActiveCell = "QC" Or ActiveCell = "EP" _
Or ActiveCell = "ES" Or ActiveCell = "HP" _
Or ActiveCell = "BB" Or ActiveCell = "FB" _
Or ActiveCell = "SS" Then
GoTo ReRun
End If
ActiveCell.Offset(1, 0).Activate
Thanks for any help

While Loop
Basicly I have got myself a little stuck, I want my macro to run down the cells till it reaches the 1000th row and check if each cell contains 0, if they dont I want a 5 in the next coloum.

My idea was to use a while loop of 0 to 1000, but I dont know how to change the cell range each time.

Any help please...



For i = 0 To 1000

While i < 1000

If ActiveCell.Value = 0 Then


ActiveCell.FormulaR1C1 = 5

End If

For Loop

I need to put into a char the name of many serieslabel according to especeific cells. I really appreciate any help....

The code I tried is as follows:

For i = 1 To lrow3
ActiveChart.SeriesCollection(i).Name = "=Template!A" & lrow3 + 1
Next i

The original code that I am trying to automate is :

' ActiveChart.SeriesCollection(1).Name = "=Template!R2C1"
' ActiveChart.SeriesCollection(2).Name = "=Template!R3C1"
' ActiveChart.SeriesCollection(3).Name = "=Template!R4C1"
' ActiveChart.SeriesCollection(4).Name = "=Template!R5C1"
' ActiveChart.SeriesCollection(5).Name = "=Template!R6C1"
' ActiveChart.SeriesCollection(6).Name = "=Template!R7C1"
' ActiveChart.SeriesCollection(7).Name = "=Template!R8C1"
' ActiveChart.SeriesCollection(8).Name = "=Template!R10C1"
' ActiveChart.SeriesCollection(9).Name = "=Template!R10C1"
' ActiveChart.SeriesCollection(10).Name = "=Template!R11C1"
' ActiveChart.SeriesCollection(11).Name = "=Template!R12C1"
' ActiveChart.SeriesCollection(12).Name = "=Template!R13C1"
' ActiveChart.SeriesCollection(13).Name = "=Template!R14C1"
' ActiveChart.SeriesCollection(14).Name = "=Template!R15C1"
' ActiveChart.SeriesCollection(15).Name = "=Template!R16C1"
' ActiveChart.SeriesCollection(16).Name = "=Template!R17C1"
' ActiveChart.SeriesCollection(17).Name = "=Template!R18C1"
' ActiveChart.SeriesCollection(18).Name = "=Template!R19C1"
' ActiveChart.SeriesCollection(19).Name = "=Template!R20C1"

About For Loop
Hi, there. I want to add two values of textboxes to the specified ranges if a keyed-in date was found in my worksheet. However, the result turned out to be wrong one. The following is my vba codes.

Dim vali_date As Date
vali_date = CDate(TextBox4.Text)
ck_date = Cells(Rows.Count, 33).End(xlUp).Row

For ck = 2 To ck_date 'search through the designated rows
'here if the keyed-in date = date in the specified range value
If vali_date = Format(Range("ag" & ck), "dd/mm/yyyy") Then
Range("ak" & ck).Value = Application.WorksheetFunction.Text(TextBox5.Value, "[hh]:mm")
Range("al" & ck).Value = Application.WorksheetFunction.Text(TextBox6.Value, "[hh]:mm")
net_taken = Range("al" & ck).Value - Range("ak" & ck).Value
net_taken = Application.WorksheetFunction.Text(net_taken, "[hh]:mm")
Range("am" & ck).Value = net_taken
'here if keyed-in date not found, add keyed-in date to the last row
Else vali_date <> Format(Range("ag" & ck), "dd/mm/yyyy") <> Then
Range("ag" & ck_date + 1).Value = CDate(TextBox4.Value)
Range("ak" & ck_date + 1).Value = Application.WorksheetFunction.Text(TextBox5.Value, "[hh]:mm")
Range("al" & ck_date + 1).Value = Application.WorksheetFunction.Text(TextBox6.Value, "[hh]:mm")
net_taken = Range("al" & ck_date + 1).Value - Range("ak" & ck_date + 1).Value
net_taken = Application.WorksheetFunction.Text(net_taken, "[hh]:mm")
Range("am" & ck_date + 1).Value = net_taken
End If

However, when a keyed-in date was found, not only values of textbox5 and textbox6 were added to the specified ranges but a row of new date was added as well.

Can anyone please advise?

If Then Loop Help
I wrote a program where it reads row by row information and copies it by individual cell to a bookmark in word, then saves the document with the name being the data in the first column. Some of the rows are only contain a header explaining what is in the next few rows. I don't want to include those in the bookmark process. What kind of loop can I use? For the rows that I do not want to include, cell B1 will be empty, so it will have a value of null.

Option Explicit

Public Sub Excel()

Dim oExcel As Object
Dim oWB As Object
Dim oWS As Object
Dim oWdDoc As Object
Dim oRange As Object
Dim oNull As Object

Dim a, b As String
a = "C:Documents and SettingsMy DocumentsInventory.xls"
b = "C:Documents and SettingsMy"

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
Set oWB = oExcel.Workbooks.Open(a)
Set oWS = oWB.Worksheets("Sheet1")

With ActiveDocument

Dim j As Integer
Dim lngrow As Long
Dim GetDocumentName As String

For lngrow = 3 To 100
Set oRange = oExcel.ActiveSheet.Range("a" & lngrow)
Set oNull = oExcel.ActiveSheet.Range("b" & lngrow)
Set oWdDoc = Documents.Add(b)
GetDocumentName =

Selection.GoTo What:=wdGoToBookmark, name:="description"
Selection.TypeText (oWB.Sheets("Sheet1").Range("a" & lngrow))

Selection.GoTo What:=wdGoToBookmark, name:="descriptor"
Selection.TypeText (oWB.Sheets("Sheet1").Range("b" & lngrow))

Selection.GoTo What:=wdGoToBookmark, name:="definition"
Selection.TypeText (oWB.Sheets("Sheet1").Range("c" & lngrow))

Selection.GoTo What:=wdGoToBookmark, name:="purpose"
Selection.TypeText (oWB.Sheets("Sheet1").Range("d" & lngrow))

Selection.GoTo What:=wdGoToBookmark, name:="detail"
Selection.TypeText (oWB.Sheets("Sheet1").Range("e" & lngrow))

Selection.GoTo What:=wdGoToBookmark, name:="source"
Selection.TypeText (oWB.Sheets("Sheet1").Range("f" & lngrow))

Selection.GoTo What:=wdGoToBookmark, name:="freq"
Selection.TypeText (oWB.Sheets("Sheet1").Range("g" & lngrow))

Selection.GoTo What:=wdGoToBookmark, name:="owner"
Selection.TypeText (oWB.Sheets("Sheet1").Range("h" & lngrow))

ActiveDocument.SaveAs FileName:="C:Documents And SettingsMy Documents
eports" & oRange & ".doc"
Next lngrow

End With

On Error Resume Next
oExcel.DisplayAlerts = False

Call oWB.Close(Savechanges:=False)
Set oWB = Nothing
Set oExcel = Nothing

End Sub

For/Next Loop Help
How can I collect a unique number from a url when two identical numbers are present?

Here's the HTML code:

HTML Code:
<a href="http:/">Page6895</a><br />
<a href="http:/"><img src="http:/"></a>
<a href="http:/">Page10051</a><br />
<a href="http:/"><img src="http:/"></a>
<a href="http:/">Page14403</a><br />
<a href="http:/"><img src="http:/"></a>
What I have will always collect both numbers and place them in the listbox. I only want the first on and then move to the next number.

Private Sub cmdCollect_Click()
Dim a as object, c as string
For each a in wb.document.links
If a.href like "**" then
c = a.href
c = Mid(c, InStrRev(c, "/") +1)
List1.AddItem c
end if

End Sub

This obviously puts the same number twice in the listbox. I feel like I should exit the for/mext loop after finding the first number, but how would I start it back up? Maybe this isn't the right idea.

The Right Loop
Can anyone tell me the right way of doing the following.

I have a main menu which is an mdi form. from that i open other forms from menu's and for the main search form i double click anywhere on the mdi form.

I want to know the best way to:
if no forms are open except the main menu then loop round every 10 seconds to see if there are any files in a specified directory.
BUT, if i open any forms or double click the main menu for the loop to stop until no forms are open again.

I have the code for the checking of files etc.
But when i have tried doing a do while the program freezes, do i need to use timers, can you interact with the forms while there is a timer running in the background ?

Hey, i'm new to using looping commands. I need a step in the right direction.
I am making something that when a user enters in a number in a textbox (EX:4) it will do 4+3+2+1. or 10+9+8...+1.

Could someone please tell me why this loop isn't working? I'm getting an error saying Next without For.

Here's the code:

Private Sub SearchForParents(ByVal SearchItem1 As String, ByVal SearchItem2 As String, ByVal Total1 As Integer, ByVal Total2 As Integer, ByVal Filename)
SearchItem1 = "Yes"
SearchItem2 = "Smoker"
Counter = 1
Total1 = 0
Total2 = 0
For Counter = 1 To TotalRecords
If SmokingInfo.Parents = SearchItem1 Then
Total1 = Total1 + 1
If SmokingInfo.Parents = SearchItem1 And SmokingInfo.Status = SearchItem2 Then
Total2 = Total2 + 1
End If
Next Counter
End Sub



Using A For Loop
ive got an assignment with a bit of a problem. this is a direct paste:

5.The numbers need to be right justified and displayed with 2 numbers to the right of the decimal. To make this work, you need to use the FormatNumber command and pad the number with preceding blanks. Use the length of the number and a For loop to insert blanks at the beginning of each number

i cant figure the for loop out. for what? i want to say for each strprice but i know thats not right. here is a snippet of my code:

Dim strprice, stritem, strtruefalse As String
Dim decsubtotal, dectaxamnt, decfinaltotal As Decimal

Do While Not EOF(1)

Input(1, strprice)
Input(1, stritem)
Input(1, strtruefalse)

strprice = strprice.Remove(0, 4).Insert(2, ".")

WordsLabel.Text &= stritem & ControlChars.NewLine
numberslabel.Text &= strprice & ControlChars.NewLine
decsubtotal += CDec(strprice)
If strtruefalse.ToUpper = "TRUE" Then
dectaxamnt += CDec(strprice * 0.15)

End If


decfinaltotal = decsubtotal + dectaxamnt
TotalsLabel.Text = "Subtotal: " & decsubtotal & ControlChars.NewLine & "Tax Amount: " & dectaxamnt & ControlChars.NewLine & "Total: " & decfinaltotal

Do Loop In A For Loop
Im having a problem with placing a Do Loop in a For Loop in the following code:

For i = 1 to 20
Call DownloadFile
Open App.Path & "file.txt" For Input As #f
sText = Input$(LOF(f), #f)
Close #f
Kill App.Path & "file.txt"
sStartTag = Text1.Text
sEndTag = "</font>"
'Initialize variables.
lStart = 1
lEnd = 1

'Find the fourth set of tags. Call InStr() 4 times while incrementing
'the start position each time from the last found tag.
If lCount <> 4 Then

'Find the start of the first tag.
lStart = InStr(lEnd, sText, sStartTag, vbTextCompare)
lEnd = lStart + Len(sStartTag)

'Increment the counter.
lCount = lCount + 1


'Fourth tag found. Adjust the start position to the end of the tag.
lStart = lStart + Len(sStartTag)

'Find the end of the text.
lEnd = InStr(lStart, sText, sEndTag, vbTextCompare)

'Return the text to the immediate window.
sTaggedText = Mid$(sText, lStart, lEnd - lStart)
bufferx(i) = Replace(sTaggedText, ",", "")
Text2.Text = Text2.Text & bufferx(i) & vbCrLf
'Get out of the loop.
Exit Do

End If

Loop Until lStart < 1

Whenever I run this code, the Loop Until lStart < 1 doesnt go back to the Do portion. It skips right ahead to For i = 1 to 20, and i is now 2. I want the loop inside the for loop to keep on going until it reaches the statements after Else.

How would I do this?

Your help is appreciated.

For...Next Loop Help
I am having trouble understanding how to code the For...Next loop in my program. Can someone offer suggestions please?

For background:
I have a command button cmdRollDice that when clicked picks a random number between 0 and 10000 for the number of rolls (value stored in a variable named intLimit). This is reflected in the caption of my cmdRollNumber button to reflect number of rolls simulated.

Now the problem:
I also want to step through a loop intLimit times and increment a variable by 1 each time I go through the loop to:
Set the caption of my cmdCountValue button to reflect the current value of my counter each time I go through the loop (I know my cmdCountValue should count from one to intLimit but am having trouble understanding and setting it up). Something just doesn't look right.

My code:

Option Explicit

Private Sub cmdRollDice_Click()

'Define random variable and assign Rnd 0 to 10000
Dim intLimit As Integer
intLimit = Rnd * 10000 + 0
cmdRollNumber.Caption = "Rolls:" & intLimit
For intLimit = 0 To 10000 Step 1
cmdCountValue.Caption = intLimit

Dim intDie1 As Integer
Dim intDie2 As Integer
Dim intDie3 As Integer
Dim intDie4 As Integer
intDie1 = Rnd * 6 + 1
intDie2 = Rnd * 6 + 1
intDie3 = Rnd * 6 + 1
intDie4 = Rnd * 6 + 1
cmdDice1.Caption = intDie1
cmdDice2.Caption = intDie2
cmdDice3.Caption = intDie3
cmdDice4.Caption = intDie4

End Sub

Private Sub Form_Load()
Dim intLimit As Integer

End Sub


For Loop
how can I make a dynamic loop function, e.g.

loopit 3,0,13,msgbox "blah"

'would do

For i = 0 to 13
for i2 = 0 to 13
for i3 = 0 to 13
msgbox "blah"
next i3
next i2
next i


loopit 1,2,3,me.hide

'would do

For i = 2 to 3
next i

I wish I had more than High School training in VB, plus Self-Teaching

I have the vars(they are integers) d1 to d51(each 1 is set as a textbox value) and I want them to be mutiplied by z all at once. I was wondering how I would loop through those to get em all to multiply by z in a few lines instead of my big long idea.

Need Help With Loop
I'm making a game, and now I'm working with some codes for random starting values in the game(planet characteristics).
Now I'm looking for an easy way to do something like
"if grade is 1, then set (textbox) to "easy". if grade is 2, set (textbox) to "medium". if grade is 3, set (textbox) to "high" for every one of the 5 textboxes.
I'm pretty new to loops, and I wasn't able to think of a fast, easy way to use them, but I belive there should be. The only way I can think of doing for what I want, is by using if henelse for every textbox.

Private Sub Startgame()
Dim grade As Integer

grade = Int(3 * Rnd + 1)
Planetform.PlanetTemperature = grade
grade = Int(3 * Rnd + 1)
Planetform.PlanetMoisture = grade
grade = Int(3 * Rnd + 1)
Planetform.PlanetDensity = grade
grade = Int(3 * Rnd + 1)
Planetform.PlanetGravity = grade
grade = Int(3 * Rnd + 1)
Planetform.PlanetSoil = grade

End Sub
Btw, tell me if any of the language is wrong, I'm not sure if it's all right. (density means earth density, like if the earth is soft and loose or hard)

For Loop Help

Iam trying to call routine using the for loop. Suppose the user enters to values in the text1 and text2 as 3 and 20 the total count is 60. So iam trying to call a routine1 when the i value is in between 1 to 20 and routine 2 when from 20 to 40 and so on till it reaches 60. But here the text1 and 2 are variable. but Text2 and total count are the key values to run.

need ur thoughts on how to do this ,,,,,

How Do I Put A Loop Into This?
This is my code to enter data into a worksheet. I have looked at lots of tutorials on loops but i am unable to understand them. I want a loop to search for empty textboxes in my userform.

Private Sub OK_Click()



If IsEmpty(Range("B2")) Then
Row = 2
Row = Range("B1").End(xlDown).Row + 1
End If
Cells(Row, 2) = txtFor.Text

If IsEmpty(Range("C2")) Then
Row = 2
Row = Range("C1").End(xlDown).Row + 1
End If
Cells(Row, 3) = txtSur.Text

If IsEmpty(Range("D2")) Then
Row = 2
Row = Range("D1").End(xlDown).Row + 1
End If
Cells(Row, 4) = txtAge.Value

If IsEmpty(Range("E2")) Then
Row = 2
Row = Range("E1").End(xlDown).Row + 1
End If
Cells(Row, 5) = txtSex.Value

If IsEmpty(Range("F2")) Then
Row = 2
Row = Range("F1").End(xlDown).Row + 1
End If
Cells(Row, 6) = txtDate.Value & "/" & txtMonth.Value & "/" & txtYear.Value

With Sheets("CustomerDetails")
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1).Value = Int(Rnd * 100000000)
End With

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Unload Me

End Sub

Do While Not Loop

Is it possible to have a Do While Not loop that looks something like this:

Do While Not Activecell.value = "XXX" OR ""

Is this sort of thing possible?


Need Help With A Loop Please
Hello Guys,

I have been playing with changing the text in a certain number of cells on my worksheets to ensure that entries are always in upper case and if not then converted to upper case, unfortunately i have found a slight problem with the code i have been using .... (inside a worksheet change event)

Application.EnableEvents = False ' this just stops it getting
' stuck in a loop with other
' code in change event
Dim IsInRange1 As Range ' Week 1
Dim IsInRange2 As Range ' Week 2

Set IsInRange1 = Application.Intersect(Target, Range_
Set IsInRange2 = Application.Intersect(Target, Range_

If Not IsInRange1 Is Nothing Then
Target.Value = UCase(Target.Value)
End If

If Not IsInRange2 Is Nothing Then
Target.Value = UCase(Target.Value)
End If

Application.EnableEvents = True ' Reset event trapping
' + calc sheet

Now the problem comes in that some of my cells are merged ...... and i get a type mismatch error. Now i have been able to get around this by declaring new IsInRanges and setting each to one of the merged cells, then applying that convert code to them, but with an extra line to accomodate blank entries (ie when user deletes target cell contents ... like so .....

If Not IsInRange3 Is Nothing Then
If IsInRange3.Value = "" Then Goto Blank
Target.Value = UCase(Target.Value)
End If

where IsInRange3 is one of the merged cells ... say C25

Now, this works .... although to me it seems to be missing an end if (if i put one in it errors saying end if without if block) the problem is that i have about 42 of these merged cells to check ..... so i think i need to use a loop, but i keep failing misserably ... lol ... i also dont now if i need to dim and set each of the merged cells, as in order to make the second code work, you only refer to the merged Cells by their primart address ...

anyone help me with this ?



Help With A Loop
This could have gone in the game section, but its in Excel sooo.....

I'm making a blackjack program (to test my VB skills), and I have a question. Each of my 10 cards has 2 captions where the card number will go (i.e. A, 2-10, J, Q, K) and a picture where the suite will go. I need to be able to store those 3 things as variables so that I can call on each card depending on what the variable icard is set to.

Here are my objects:

If icard is 1 i want to be able to change the values for card1...if icard is 2 it changes the values for card2.....ect.... can somebody help?

Copyright 2005-08, All rights reserved