What I have is a database of measurements taken, where X-axis is distance across a surface and Y-axis is measuring deformation to that surface. Each measurement is actually a group of data points from one sweep across the surface on a certain day, resulting in a line plot for that group. What I'm trying to do is create a "checksheet" so that you can overlay plots from multiple dates on the same grid to compare how the surface has changed with each measurement. I've got the checksheet part working properly, and I can toggle things on/off as I like as long as my data doesn't change.
The rub is that this data is gathered via query, and each measurement doesn't have the same # of data points. If I update the query to look at a different date range, the plots are now off because the old data range doesn't match the new data.
Is there any way of changing the data range when a query gets updated, or a way to tie it to a function?
I'm trying to automate a spreadsheet that downloads a set of tickers from Bloomberg and then fills across a range with a series of data. I have most of the VBA code I need, but my problem is that on opening the spreadsheet, the code moves on to fill the range before all the tickers have downloaded correctly.
I've tried using application.wait and setting up loops until the time reaches a certain value, but these seem to stop Excel updating the list of tickers (presumably the loops stop excel gathering the data?). I suspect that what I need to use is something like the querytable. afterrefresh, but am not sure I'm using it correctly since my knowledge of VBA is very rudimentary.
(i) is there a way of getting the sheet to open, and then just wait for a period of time while still allowing the tickers to download, and/or
(ii) any way to detect when the tickers have finished downloading before the next step of code can commence?
I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.
I pass in the query name and the sheet name into qryName and sheetName name.
Code: Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sQRY As String Dim strFilePath As String
It is my general understanding that the change event system within Excel vba is fairly particular as to what will fit the mold of a qualifying change event.
For example, changes that the user imparts to the worksheet and other written code are legitament candidates for change events. However copying down data and cells changing their values indirectly rather than directly may not be considered in the Microsoft change event design.
I would like to know 2 things:
1) Does a cell updated by a market data feed mechanism qualify for a change event?
2) Any list of qualifying change event types. It seems that Microsoft does not have this information.
A worksheet has a column named "Grade". There are may entries into this column, and most are used multiple times. I'd like a list in another location (to use in a list box on a user form) that contains all of the unique entries in the "Grade" column.
I know how to do the Advanced filter for unique records, but when I add different grades to the column, the filtered list does not update to reflect the addition. Do I need to run a macro to run the filter after every new entry?
i want to link my stock portfolio to a spreadsheet to make various calculations on historic performance. my problem is that it is the same cell thats get updated whenever a change occurs, so that there is no history.
let's say i put the DDE-link in cell A1, i would like B1 to be filled with that value and then when the value changes i would like B2 to be filled with the new value and the next time B3, and so on.
I have had a TREMENDOUS amount of assistance from a true gentleman, Jaslake; aka John. In an attempt to quite monopolizing his time I wanted to throw this at the board. I have a number of sub sheets with exact ranges of (B4,d4:b44, D44) update an individual cell (H6:H86) in the” summary” page. My attachment explains it in detail but currently I will show you the formula that resides in (H6) of "Summary". (H6) = Sheet "1". This formula only covers (B4,D4:B10,D10) for example purposes but I need it to run all the way through (B4,d4:b44, D44) for all 80 sheets.
March 01, 2009-Today looks good | March 02, 2009-Today looks bad|March 03, 2009 - Today we had issues in Boston|March 04, 2009-Issues in Dallas | March 05, 2009-Issues In New York|March 06, 2009 - New York is complete|March 07, 2009 - Dallas is Complete
NOW, with this said what I really would like to happen, if it is possible, is to have the "Summary" (H6) only have the last range that has been updated showing. So if currently B4,D4 is showing in (H6) once the user types into B5,D5 then it replaces B4:D4. I would like this action to repeat itself until the user has completed their event. The reason I can not stick with a formula in (H6:H86) is because I have a macro running a copy.paste, then clear.contents once a selection is made in Column 4. The information must remain on the sub-sheets but it can be replaced in “Summary “. “Summary” is only a quick view of the current status while the sub sheets are a log of all the work done. So, the following
I am using the worksheet change routine for conditional formating of more than three items.
The problem is that it only changes if I manually type within the page and does not update if the cells are updated via formulas.
To explain a little more, I have three sheets that I combine into one to help find the best rate from three companies using the following formula:
=IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" All",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4),FIXED(PMI!C4,2)&" PMI/UG",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" PMI/GE",IF(AND(MIN(PMI!C4,UG!C4,UG!C4)=GE!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(UG!C4,2)&" GE/UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,FIXED(PMI!C4,2)&" PMI",IF(MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,FIXED(UG!C4,2)&" UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=GE!C4,FIXED(GE!C4,2)&" GE","ERROR"))))))) Please feel free to condense this if you like.
So when I update a reference on one of the first three sheets, the cells update on this sheet but the cell colors never change. This is the code for the worksheet change
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel1 As Range Dim Rng1 As Range
On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error GoTo 0
When someone clicks on a specific cell, I want a different cell to then fill with data from another worksheet, can this be done?
For example, I have cells on a speadsheet box 1, box 2 and box 3. What I want, is when someone clicks on say box 2 to selects that option, then I want box 4 to auto-fill with the price I have from another spreadsheet
Not sure if this is possible or if I would need to run a VB macro. image multiple columns with text headers.
'A'=Project Number 'B'= Date 'C'=Description etc etc etc 'S'=Cancelled (i actually use up to 'Z' on the columns for various other data)
I have added a Conditional Format on the cells in column 'S' that if the cell data = Y then colour the cell RED. This at first glance will show that the project has been cancelled. What I would like it to do is to colour the complete row (shaded grey for example) this would then show up more easily with the amount of data that is on my sheet.
I currently have a tab that is contains a macro driven query pulling data from an access database. However, I have selected the option to add new rows for new data which in turn causes my sumtotal at the bottom of the sheet to be over ridden by this new data. My question is, could there be a way to have the total row move down as new rows are added from the query (formatting and all)?
One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones). Unfortunately excel always converts this field into a date and a time but this is not what I want. Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run. I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
I am trying to add some functionality to a spreadsheet that I manage. Basically i have a small team, and each week I ask them to update a spreadsheet to show what they have been working on in that week. In order to monitor who is going in and updating their entry I would like to add a column that shows me when the last update was for each team member. I have seen this example but it only returns a date based on one cell as opposed to the whole row (or range of cells) Auto populate the date and time a cell was updated. I have attached an example.
I have a two sheet, in Sheet1 i have a data, and in sheet 2 i have a count of location wise data in it, which is from sheet1.. And beside it u will see a button "View Detail". When you click on that button it will pop up a userform which have listbox and 2 button.. Now in listbox i get a filtered value of column C of sheet1 (For eg. Mumbai, Pune, Chennai).. When i click on the value (Mumbai) in Listbox and then click on View Location Details. It should give me a details of mumbai based candidate, like name, pod no, location in the range of (H5:J100) of sheet2..
I wrote the code for it but its not giving me the correct data..
I am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...
I use 2010 and I will try and I need to complete a relatively simple project that I am working on.
I have a range of cells, "D7:Q30". This range has numerical values that are updated automatically (every cell within the range has a numerical value) from an "old value" to a "new value". The refresh rate is not constant, that is the values may change at any time depending on when a value changes I think it is called "pushed" data. Once a value is updated ("new value"), it stays at that value until one of the updates changes its value (and then it becomes the "old value").
I would like a message box to display the following EACH TIME A CELL CHANGES ITS VALUE when the NEW updated value is LESS THAN the OLD value:
"B51, B52, **5, has a changed value from "old value" to "new value".
The ** above in the message box represents the following: If for example cell F23 changes, ** in the message box should be the value that is in cell F5. If Q10 changes, the ** value in the message box should be Q5. It will always be the value in Row5 but have a different column depending on where the change is detected.
B51 is the text value in B51..same for B52 (text) etc...these values change depending on what spreadsheet I have open, but will always be in those cell positions. But the values within the range D7:Q30 are always numeric.
The spreadsheet I have designed retrieves stats and data from individual player pages and stores them in an excel workbook.
I have used 20 seperate web queries, to import the data for each of my players (from an online cricket simulation game). This works fine, but my problem is that I want to make it usable for other players.
Player pages are titled playerinfo.pl?playerid=1020, through to playerinfo.pl?playerid=1039.
Ideally Id like the user to enter his lowest playerid into a cell, and then create the web queries for that manager's players.
I'm not sure if this is a possible scenario, or if I need to use a repetition loop in VBA.
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
I have a Excel database query which of which i import into Sheet 1. On a daily basis I need to edit this query and change a critea field to yersterdays date. Is there a way in which I could run a macro to change this query for yesterdays date without having to manually go into the query?
I have tried to run the macro, however I can only run this if I have a specific date in my code e.g. 11/03/2007 and not a formula to show yesterdays date.
getting data externally from web and also keeping the previous data prior to the refresh.
My external table/data has 10 rows reporting values per day (10 days data table).
I can link the table to my sheet, however what I can not do is to create an offlinek, year to date table in the sheet which captures the rolling 10 days (everytime it is refreshed) without losing the prior days.
Above table changes on a daily basis, only reporting the last ten days.
How can I capture this data on a rolling basis, i.e. in a year to date format, so that everytime I refresh, the internal table gets updated with the new data.
I am trying to get a macro to update information from attached sheet GTS807 to the Stock sheet. The code in Module 26 works only for the first line and will be very long if I repeat it for the 20 lines of the input sheet! Basically we need to copy the new stock balance from GTS807 column AA13 to AA32 to the individual corresponding product name on the stock sheet (Column E)!
Is there a way to have Excel retrieve updates from a Web Site? Specifically what I am interested in getting is flight schedule information from sites like USAir, JetBlue, American or Other carriers. I have a list of Dates and FLight numbers in a spreadsheet, I would like to get updates on Arrival, Departure or status ( Delayed / Cancelled etc ). The Excel sheet would provide Date, Carrier, Flight Number, Departure Point (if Needed ), Destination Point ( if needed ) and flight Departure and Arrival Times. I would want to pass this data to the appropriuate Web site.
Convert data to proper for columns- E, Z and ADConvert data to upper for columns- A, I, X and ACHighlight Special characters with cell colour as Red for (!@#$%^&*:”<>+_’;\/?`~-)Find & replace % in the current sheet with blank.ran.
I want whenever i select any dept in the B column say ID or Design... the value into the D column should change automatically...
say if i select in B5 as ID then D5 value should show me India
******** ******************** ************************************************************************>Microsoft Excel - resource_sheet_11_Dec_07_V2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Project(Client - Name- Length)Dept.Resource 1-Oct2 3Template 4 5New Course Sample Template ID 6New Course Sample Template ID 7New Course Sample Template DesignDesign Pool 8New Course Sample Template BuildBuild Pool Project Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?
My normal Job is it: 1) I Work all the time in Excel 2) At the end of the work in Excel, I need to open Access 3) Open a query (Called "MyQuery") 4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month) 5) Run the query
I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet
Sub Change_Criteria_And_Run_Query()
Dim mydbase As Object Set mydbase = CreateObject("Access.Application") mydbase.OpenCurrentDatabase ("C:My doucmentsDB1.mdb")
mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)
>>>>>The above line of code I have problems to get right!
