Close Window   
TeachExcel.com
Got an Excel Question?
TeachExcel.com

Wait/pause During Loop

Hi

I've looked about but I must be searching using the wrong words as I can't seem to find what I'm after.

I have a do while .... loop, all I want to do is slow it down by just by a second or two to see what's going on. A pause until a key is pressed isn't required.

In original basic I used years ago it would have gone something like:

for a=1 to 1000:next a

or

wait 1000

a simple one second pause but it doesn't like it.

Any suggestions are welcome.

Cheers


Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Stop a Macro from Running in Excel
How to stop a macro from running after it has started. This method uses the keyboard and has nothing to do with th ...
Pause a Macro or Make it Slow Down in Excel
How to make a macro stop or pause for a specified amount of time. This allows you to slow down the execution of a m ...
Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...

Helpful Excel Macros

Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Delete a VBA Module From Excel
- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f

Similar Topics







I automating a task at work that will save some time. Seems simple enough but for some reason the code I have to cause IE to wait unil ready doesnt seem to work in all cases.

Im using :

'wait until the page loads
Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE
DoEvents
Loop

This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready.

Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the .innerHTML to verify that a unique page element is present.


Thanks,

Scott


I would like to know how to make excel calculate which inventory is left in the FIFO method, quantities that are bought first are the first to be sold. Basically where the quantity and price is derived from.

For example:
APPLE
bought 100 on FEB 1 @$1
bought 500 on FEB 1 @$1
bought 1000 on FEB 10 @$1
sold 300 on FEB 12 @$1.5
bought 100 on FEB 13 @$1.1
sold 100 on FEB 13 @$1.2
bought 1000 on FEB 14 @$1
bought 400 on FEB 15 @$1
sold 2000 on FEB 19 @$1.5

For this, the formula/function would know that:
=>for the 300 sold on FEB 12, 100 bought on FEB 1 is depleted and 200 of the 500 bought on FEB 1 is depleted.
=>for the 100 sold on FEB 13, the 100 bought on FEB 13 is depleted
=>for the 2000 sold on FEB 19, it knows that:
==>the 100 bought on FEB 1, the 200 of the 500 bought on FEB 1, and the 100 bought on FEB 13 is depleted so it can't consider those quantities
==>it would consider the remaining 300 of the 500 bought on FEB 1, 1000 bought on FEB 10, and 700 of the 1000 bought on FEB 14

The remaining quantities that are left would be the remaining 300 of the 1000 bought on Feb 14 and the 400 bought on FEB 15.

I would like to know for each sold transaction, which quantities at which prices on which day were sold.

Thanks in advance!


Hi there,

I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.

What I want is for my code to execute as soon as the data connection refresh has finished.

When I use the statement

Code:

ActiveWorkbook.RefreshAll
Application.Run "Project1.xlsm!ConvertDates"


The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.

Is there some way I can tell excel to wait till the refresh has finished and then execute the code?

Any help would be hugely appreciated.


I have a long list of item descriptions that I need to filter down to descriptions containing words like "valve" or "ring" or "root" or "epic" (17 key words in all). The basic custom filter looks like it only lets me filter for 2 words.

I have some experience working with advanced filter for things like >100 but that's about it. Any help with this would be so appreciated.


I'm trying to use VBA to go to a website that requires a User Name, Password, and a Submit Button.

So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually "hit" the submit button on the webpage.

For posting, I removed my actual user name and password and and used the generic " User Name " and " Password " highlighted in blue.

I highlighted another section in green that I took from a previous post hoping it would solve my problem. The link is: http://www.mrexcel.com/forum/showthr...xplorer+submit


Here is the code I am using:

Sub GoToWebSiteAndPlayAroundNew()

Dim appIE As Object ' InternetExplorer.Application
Dim URL As String


Set appIE = CreateObject("InternetExplorer.Application")
URL = " https://efolio.morgankeegan.com/escripts/defaultLogon.asp?errCode=2 "


With appIE
.navigate URL
.Visible = True

Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop

.document.getelementbyid("fUserName").Value = " UserName "
.document.getelementbyid("fPassword").Value = " Password "

End With


On Error Resume Next
x = 0
For Each mitem In IE.document.all
mitem.Value = "x"
x = x + 1
Next

x = 0
For Each mitem In IE.document.all
If x = "Submit" Then
mitem.Click
Exit For
End If

Next

End Sub


I am using below code, but it only untill 19,999. Amount from 20,000 and above only show thousand? Please help!

For 19,999 it show
Ringgit Malaysia : Nineteen Thousand Nine Hundred NinetyNine Only

For 20,000 and above it show
Ringgit Malaysia : Thousand Only

Code as below:

Function SpellNumber(amt As Variant) As Variant
Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1) = "One"
WORDs(2) = "Two"
WORDs(3) = "Three"
WORDs(4) = "Four"
WORDs(5) = "Five"
WORDs(6) = "Six"
WORDs(7) = "Seven"
WORDs(8) = "Eight"
WORDs(9) = "Nine"
WORDs(10) = "Ten"
WORDs(11) = "Eleven"
WORDs(12) = "Twelve"
WORDs(13) = "Thirteen"
WORDs(14) = "Fourteen"
WORDs(15) = "Fifteen"
WORDs(16) = "Sixteen"
WORDs(17) = "Seventeen"
WORDs(18) = "Eighteen"
WORDs(19) = "Nineteen"

tens(2) = "Twenty"
tens(3) = "Thirty"
tens(4) = "Fourty"
tens(5) = "Fifty"
tens(6) = "Sixty"
tens(7) = "Seventy"
tens(8) = "Eighty"
tens(9) = "Ninety"

FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
FIGLEN = Len(FIGURE)

If FIGLEN < 12 Then
FIGURE = Space(12 - FIGLEN) & FIGURE
End If

If Val(Left(FIGURE, 9)) > 1 Then
SpellNumber = "Ringgit Malaysia : "
ElseIf Val(Left(FIGURE, 9)) = 1 Then
SpellNumber = "Ringgit Malaysia "
End If

For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Ntow = Ntow & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If

If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Hundred "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i

If Val(Left(FIGURE, 1)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + " Hundred "
End If

FIGURE = Mid(FIGURE, 2)

If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If


FIGURE = Mid(FIGURE, 4)


If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " And Cents "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " Only "
End If
End Function


Is there a way that will run the code from a button when the enter key is pressed. Currently the enter key moves to the next text box in the user form.


I have a list of 5 buildings each with different unit counts, and an average price sold for each building. How can I get the average price of all units sold?

A B
312 $1000
54 $1010
35 $1835
32 $1028
53 $1937


When using the copy and paste function it is extremely slow to paste the
result. Also the file size seems to be increasing to a much larger size for
no reason.
Any feedback would be appreciated.



Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?!

Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?

In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.

Thanks for taking the time to read this post. Any help would be appreciated! Thanks, doug


I have a sheet with a list of names on and I've noticed that when I use ctrl+F to use the find function, even when I know i have entered the correct name it still will not work. I have identifed times when I know the name is there on the sheet, but the find function says "Excel cannot find the data you are searching for"

Im unable to put any pics up as my work pc has java disable so photobucket ect won't work but has anyone else come across this problem and how did you rectify it? A search macro perhaps?


I have a spreadsheet set up with an employees information, including the start date. After each year that the employee is working, he gets bumped up on the pay scale. For this reason, I only need to know how many years the employee has been working, rounded down to the nearest year.

This is what I have so far (hire date is in column B):

=((TODAY()-B4)/365)&" YEARS"

This function gives me a number with many decimal places.

I tried:

=ROUNDDOWN((TODAY()-B4)/365),0)&" YEARS"

It tells me I have too few arguments. Please help!


I have a UserForm with several fields on it: 5 are 'required' and the other 6 are 'optional'. If a user doesn't enter a value for ALL of the 5 'required' fields, how do I get the code to return the user to the UserForm (with, I assume, the values they already entered, still showing)?

I have the AutoOpen code that starts things off, and then, in turn, loads the UserForm with the fields that need to be populated.

I can't figure out how to route the user back to the UserForm, to enter the 'missed' data.


Hi folks,

My problem is Tab no longer moves your active cell selection one to the right. It now moves to the last cell in your spreadsheet to the right (similar to CTRL+Arrow Key Right).

I seem to remember this being a simple setting you can change, but was unable to find it myself, or search the web for the way out. So my last result is asking the experts .

Do any of you know how to make it so Tab goes back to just moving one cell to the right again?

Thanks in advanced.


I have a simple address book spreadsheet. I would like to create a Search
form, where the user can enter the Last Name of the person they are searching
for.





I was wondering if there is a way of getting a macro to open the "save as" dialogue box and then have it fill in the "file name" field.

I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required.

Cheers.


I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.

Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:

using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.

The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.

Anyone have any idea?

Cheers
Jon

Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe


Hello Everybody,

I am hoping for some assistance. Here is what I have....

I am trying to get counts of certain values.

This is in the same workbook but different sheets.
On sheet 1 is where my formula lies. In a cell, I want it to look at sheet 2 column B and look for instances of "A", then I want it to look in the same row it found "A" and look at the value in column "N" and evaluate if there is an amount. If there isn't skip it and don't count it, if there is a value count it if it falls within a given range.

An example of the formula is below...what am I missing?

=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=0:500))
=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=501:1000))

Thank you in advance for your assistance.


Hi All,

Random question i have a large Excel Workbook (which is protected) and has over five sheets on it - however one sheet has randomly decided not to scroll... yes i know sounds random! If i use the cursor and down arrows the selection just disappears off the screen.
The page will scroll if i filter by one field, but not if i select (All) for all filters.
All other worksheets scroll fine.
Any suggestions?

Thanks in advance.
Cheers Kaite


I am trying to work out how to calculate the geometric mean of a series of values, some of which are negative, ie. investment returns over a series of years with some negatives.

The Geomean function doesn't work because it only uses positive numbers.

Any help would be greatly appreciated.


Good mornng - I am new to the forum - my name is Jena

My question is this - I have a large spreadsheet with multiple columns of information. I want to sort it by a certain column. I know how to do this. My question is, once I'm done with my work I want to sort it back to the original "sort" but I'm not sure how they have it sorted. I've tried to figure out how they have it sorted but can't. Is there some way to go back to the original sort?

Thank you for your anticipated help.

Jena

This formula should be very often to use, but i cant found it anywhere, so i ask this here




For example in a1 is :
mike is playing basketball, he is very great player

i want to make it in b1 to be uppercase only in first letter so :
Mike is playing basketball, he is very great player






i know about proper formula =proper(a1) , but proper formula is make uppercase to all first letter in all words, we just need first letter in first words to be uppercase (and to every first words after dot if it can)


I have created a simple macro in this excel spreadsheet. Everything works fine until I sent the attachment to my boss. It does not work and keep showing s pop up error msg.

What could be wrong?


Hi folks,

Hoping someone can help me quickly as I'm pulling my hair out and have a deadline getting too close.

Been trailing the net and this board but can't work out how to do a simple variance.

How do I work out the difference between two numbers - including negatives.

If the numbers are all positive, it's fine as it's simply a case of A - B = C which is your difference. However, that doesn't work if B is negative.

I need a formula that would give the following:

First Number / Second Number / Difference
1 / 5 / 4
1 / -5 / -6
-1 / 5 / 6
-1 / -5 / -4
-5 / -1 / 4

Hopefully there's a simple function I've been missing.

Thanks in advance, R


Hi all,

I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise.

I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.

Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.

It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly.

I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel.

I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.

I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!

Thanks very much for your time.