Slowing Down And Waiting For Data Results Using VBA?
Aug 21, 2013
I have 2 sheets in a workbook
My VBA code gets data from a cell in sheet1 Then it paste that into a cell on sheet2 sheet 2 processes some data and produces a single cell result based on the pasted data Then that single cell result is pasted back into sheet1
Here is the problem. Sheet 2 takes a up to one second to produce the final result because it has to fetch some data from the web My code is pasting data back to sheet1 before sheet2 has had time to produce a final result
How can I make sure the data is finished on sheet2 before my code paste the result back to sheet1?
The variable MacdBlack is the being pasted back to sheet1 before sheet2 has had time to update it This is because sheet2 has to get data from the web which is slow
VB:
Sub SheetTest()
Dim x As Integer
Dim StartCell1 As String
I am trying to rank a series of data. I have most of this figured out. However, the data that is being ranked is based on a sum of a range of cells. I have lines that are waiting for new information to be put in related to the values to be ranked and the value is reference a sum of blank cells currently since there is no data there and it is showing up as zero and therefore being ranked number 1 in my list. I basically want the ranking to rank the values 1-11 while putting all values that are zero to be put at the bottom of the ranking. Any way to do this without macros. I have been using the VLOOKUP and RANK functions for my ranking purposes.
I have tried using an if statement that made the cells #N/A if they were zero, however they remained at the top of my ranking.
I have a macro with about 20 Subs and before each one I wanted a msgbox saying "macro is now running sub 1." which then changes to "Macro is now running sub 2", all while the user is sat there with his feet on the desk twiddling his thumbs.
I am trying to put together a macro that waits for the user to input data and then continue running the macro.
All user input is for one cell and I am looking at more of an entire sheet worth of data.
Here is the scenerio.
I have one tab that pulls data out of a sharepoint list. I then have another tab that I dump data from another database into and then use vlookups to compare the data.
The problem is I need it update the sharepoint list. Then wait for the user to dump in the new data from the external database before continuing the macro and working with the vlookups.
Currently I am working this by running a few different macros to do it all, but I would like to condense it down to running one macro.
If the below formula is activated - e.g. something is put into cell f8 or g8, the I get the word calculate in the bottom left hand size of the s/sheet.
I have a spreadsheet that has 1800 sumproduct formulas in it. Foe each day of the year it counts or sums 5 things. Each of these things has 2-3 criteria that is why I used sumproduct. The database it counts from is on the same sheet. It takes to long for the sheet to calculate. Is there a better way. I am using Excel 2003. The sheet itself is not huge 913 kb.
I would like to make a file that can calculate the quantity that need for the bakery shop, it's just my example. The customers can order the number of layers of the cake and that is related to the quantity from the table. I would like to looking for the 3 ingredients and the quantity in grams, and afterwards create a pivot table to sum by each type of the ingredients. The orders is about 3000 rows and ingredients are about 20 columns, If I use IF() and Vlookup() or put in Array formula, my program will be very very slow. Can I use others function to get the ingredients and the quantities? I have attached the file belows.
Please also make some suggestion, or something to read more, about the way I use the funtion like I use IF()+IF() or IF()&IF() instead of IF(,,IF). Does it make any different?
My spreadsheet has a sheet1 with 1000 rows and needs four columns with "sumproduct" formulas referencing sheet2 with 20000 rows. An example of the formula is:
Once I add them, the recalculation slows it to a crawl. In trying to understand the problem, I do a Manual/Automatic between running a sort. The recalculation still takes too long to be practical.
I am doing a report which analyzes a specific person's transaction per country (3 criterias). The workbook has 3 sheets. First sheet is the database, 2nd is the report itself and 3rd is a reference table. I am using a sumproduct formula to count per person's transaction per country. It is giving me slow calculations.
I cannot possibly use a pivot table since there will still be other calculations involved after determining the transactions. I tried using database functions but i can't seem to understand how to go about it when there are multiple criterias involved.
I have had a workbook that I have been using to track inventory. I use a userform to enter the data for inventory in and out of the system. I recently added two more worksheets to the workbook, each of which has several SUMPRODUCT formulas on them that reference the inventory data.
Since adding these two sheets, when entering data via the userform, it takes a long time (relatively speaking) for the sheet to update with the relevant new data. I have attached a copy of the file, in hopes that someone can show me exactly what is causing the slow-down, and if there is a way that I can get the same results using different programming or formulas that will not be so slow.
The part that is slow is on the "Data" sheet. Click on the green or orange buttons to open the userform. Make a selection in each section, and enter an amount. Clicking on "Enter" will transfer the appropriate information to the worksheet. This is the part that is slow.
I would like to add a "subroutine" to my macro to show the numbers it is picking. But I am having a problem figuring out how to do this.. Here is my macro...
Sub generatelottery2()
Const l& = 1 'lower value Const u& = 49 'upper value Const n& = 6 'number of numbers per draw
=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9 When the workbook with this formula is open it slows down other workbooks quite a bit.
If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.
How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.
How to sum numbers from multiple columns that match certain criteria.
My spreadsheet is a funeral team tracker and it's only the 1st day of the month, Each corresponding day will eventually have it's own tab once I nail down this first one.
Basically the page is laid out into 6 different details a team member can do. A team member gets their hours from the "Total Detail Time," that gets calculated from the show time til the return time. The team who worked the detail gets added to the bottom of the Detail they worked. I am just trying to total the "total detail time" each time a member works it. So, a member could work say Detail 1 and Detail 3, so I would just want to sum the total detail time from those two days where the member is shown.
I was trying to use =SUM(IF((Detail1,N13,B10)+(Detail2,N13,D10)+...
Note: I have range names for each bottom portion of the details to shorten the formula (i.e., Detail1, Detail2, etc.)
The following is an example of the layout of the data that I have in a pivot table. What I need to do is find a vb code that will grab each store and its data and put them into either an individual pages or tabs. For example, if I have region 1 selected from something like a data validation list, the code will put stores 1 and 2 and their data from below into individual pages or tabs. Then if I select 2 from the list it will no longer show the results from region 1 and do the same for region 2.
region store apples oranges 1 1 3 4 1 2 3 3 2 3 4 4 2 4 5 3
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
I have two sets of data, and one colum in each set is titled ID numbers. I would like to search both those colums and combine the matching ID numbers in one row?
I'm trying to find a way to take a data set and write an excel equation/s to find out how many times in that column of data a certain result (number or letter) occurs consecutively for more than 5 (hoping that this is also customizable) times. For example....
DATE USER A 1/1/2013 NO
[Code]....
Above are two columns, one with the date and another with the data I'd like to search through. So I'm hoping that I can write an equation/s that tells me how many times a certain value, in this case I'm looking for "No" occurs more than 5 times consecutively in the line of data. For example, for this particular data set, the final answer would be 2. There are only two instances where 5 or more cells with a "No" value follow each other.
I have a column with lots of tube station names. I have a look up table with the tube station name, and the possible lines that pass by that tube station.
One tube station may be covered by more than one tube line.
I am looking for a formula that will output every tube line that caters that one tube station.
first question, what is the best way to organise the data in my look up table. Second, is a vlookup the best formula to achieve the output I want? i.e. Reflect how many tube lines cover each station
see table below: If the station name has only one tube line, Vlookup works fine. When the station, such as Canno Street, has two tube lines, Vlookup only picks up one tube line. If I duplicate Cannot street under the station name, the Vlookup picks only one tube line.
LOOK UP TABLE A1 - station name B1 Tube line name Cannon Street District
[Code].....
Is there a formula that will output the data of the second tube line name in column D1?
On my attached example (ProjectDBTrial.xls) I have a number of sheets. Data containing all the relevant project data, Search which functions similarly to a MS Access Query (this is where the coding is) and the other relevant sheet is Portfolio_Dash which will end up being dashboard.
My aim is to allow the Search function to be controlled from the Portfolio_Dash Sheet. For example - if the user were on the Dashboard and he chose the project "Magnus". This would then select Magnus in the Search/Query, populate all the Magnus data in the search and then the Dashboard would take the information straight from the Seach Sheet.
At the moment the code (seen below) requires me to select the project on the search sheet drop down. Also a bit of a bug is that as soon as I click off the Search sheet and back on it resets.
Any way to control the search function from the Portfolio_Dash sheet. The reason I cannot Make the Porfolio dash sheet the query is it has to have a very specific graphical template applied that will not work well with the function built in.
CODE: Option Explicit Private Sub Worksheet_Activate() [c3] = "Type your search here." [c3].Select End Sub Private Sub Worksheet_Change(ByVal Target As Range)
1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.
Company Name, Year, Manufacturing, Non-Manufacturing
This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.
So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.
I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
I have a dropdown box to choose a category. When the category is chosen it displays the relevant course names which corresponds to that category (this could be 1 course up to 10 different courses). Along with that course name it gives the relevant course overview.
This works exactly how i want it to. (thanks to Zbor from a previous thread)
I now want to expand further and give the corresponding course objectives for each course that shows up in the results.
I have attached a workbook as its much easier to see the dropdown box working.
Sheet 2 is the working sheet and sheet 1 will contain all the source data. I have highlighted the columns affected in Blue Blue column on sheet 1 will have the course objectives on the same line as the course they belong to Blue column on sheet 2 is where they will be displayed when the correct category selection is made.
The course objectives are unique to the course title and course overview.
I have a worksheet named "List" with static values in columns A & B. I want to search all the other worksheets in the same workbook for the one that matches the value I entered in column B on the "List" sheet. The cell on the other worksheets with the possible match is B5. When it finds a match, I want it to take the data from cell B3 on that worksheet and paste it in Column C of the "List" sheet on the corresponding row (all rows in the "List" sheet have a different value in Column B).
In other words, when "List"b7 = worksheetb5 , then worksheetb3 is pasted to Listc7.
using a command button to input data back into the spreadsheet at a specific location.
Background: I am building a time clock spreadsheet, of sorts. I have a user form that provides a list of volunteer names in a combobox and then a list of activities they can perform in another combobox. Some activities have Details. (So, VolunteerName Todd can choose Activity Maintenance, which has no details; Volunteer Joe can choose Activity Teaching and then can select Details Intermediate 2.) The volunteer then clicks a SignInCommandButton.
The SignInCommendButton populates a worksheet (VolunteerLog) with the following information:
Column A: Volunteer Name Column B: Today's Date Column H: Exact Time In Column E: Activity Column F: Details, if populated
The SignInCommandButton also copies a formula from cell L2 into the appropriate row of column C (Time In) that rounds the Exact Time In to the nearest 15 minutes. The SignInCommandButton then does a Copy/Paste Special Values to remove the formula from column C and then resets the Userform.
What I need to do:
1) When the person first selects their name from VolunteerNameComboBox, I want to check 3 things:
a) whether the person's name exists in Column A of the VolunteerLog worksheet;
b) if it does, if the Date associated with that entry = Today; and
c) if it does, if the Time Out column is Blank.
If all three conditions are met, I want the ActivityComboBox to populate with the values in Column E and F, if necessary. The user will then click the SignOutCommandButton. (details in a moment)
I already have the code for if the conditions aren't met (Activity box populates and, depending on the selection in the Activity box, the Details box may appear for a selection to be made, or may remain hidden.)
2) The SignOut Button needs to enter the Time into Column I of the appropriate row found in (1) above. It will also need to copy the rounding formula I mentioned earlier, and do the Paste Special, but I have that already.
For (1), I think I have a start. My thinking is to first check if the Name selected in VolunteerNameComboBox is in the VolunteerLog. Each time it shows up, I'd like to add it to a list (range?) called rngSignedInDate. Then I'd loop through all the entries in that range checking if the Date = Today. If it does, then I'd add it to a list called rngSignedOut and loop through those results to see if Column C (Time Out) is blank.
I think I have the first loop, but am stuck on how to populate the results into rngSignedInDate. Here is the code:
Code: Private Sub VolunteerNameComboBox_Change() 'Check if the Volunteer has signed in already Dim strVol As String Dim rngSignedInVol As Range Dim rngSignedInDate As Range Dim rngSignedOut As Range Dim rngSignedInActivity As Range Dim rngSignedInMatch As Range
[code]....
(2) I haven't started working on the SignOutCommandButton. The challenge for me with that is directly tied to the challenge in (1). If someone does pass all 3 tests, I need to have that specific instance defined in a way that I can have the SignOutCommandButton put the time in the appropriate row.
I would like to compare some keyword lists if possible.
I have a large list of Unique keywords in Col A (From A3),
This Column is called Keyword List A - Large"
I then have a keyword list in Column C (From Cell C3),,
This Column is called "Keyword List B - Small.
I then have a column called "Unique Keywords Found",, This is Col E,, with hopefully returned results being entered from cell E3 downwards.
What I would like to be able to do if possible is run a Macro that would compare all the unique words in ColA and C and return only the difference, (The Unique words not found in ColC as Col A is the "Master List")
If possible could a pop up box appear saying =================== List A No Rows:xyz List B No Rows: xyz No of Uniques Found : xyz Time Elapsed (sec): xyz ===================
I want to locate the corresponding acct number between worksheets “primary_data” and “qty_movement” and once a match is found (from acct worksheet) then copy over the acct numbers and the share data to the results page. If there is no match I don’t want anything copied to the results page, just ignore that data on either worksheet “primary_data” or “qty_movement”.
•Both ranges on “primary_data” and “qty_movement” worksheet are variable, as accts are left off or kept on depending on the daily activity so the ranges are never set. •I need to concatenate on “primary_data” worksheet cells A&B&C to get acct number •concatenate on “qty_movment” worksheet cells B&C (need to keep zeros in front of actual numbers for acct number reasons) •these accts need to be cross referenced as the accounts on the “acct” worksheet (pre-populated with corresponding acct numbers that never change and will always be on that worksheet) •If a match is found on the “acct” sheet then I would like it to either replace the acct so they match on both worksheets or just to recognize those accounts correspond with each other and do the below. •then take the 2 concatenated acct numbers off of “primary_data” and “qty_movement” in the I cells and copy them over to the “results” worksheet as well as the share data from “primary_data (column E)” and “qty_movement(Column D)” and then compare the 2 share amounts on “results(column E)”
i need to elaborate on anything, the attached sample sheet shows what i want to do, but without any formulas or code.
