In A2 is the Stock price, and B2 the time of the update.Now every time it updates A2 and B2 get overwritten.I need some code that will save/ record the the stock price on the hour, every hour. I then need the highest and the lowest price within that hour, and lastly the price at the very end of that hour. (example)
I received what I think may work, but I dont know how to write this in VBA. If the time of the new value received > than B2 + 1 second, create new row where Open=High=Low=Close else if (last column=new value,if new value>high column then high column=new value,if new value<low coulmn then low column=new value.)
I need to link my code to data from a different Excel file. I feel it is smth simple, i just have never done this before. In a regular (=same file) setting, my code would be smth like that:
With Worksheets("Daily1") j = 1 Do Until IsEmpty Range("A6").Offset(j - 1, 0).Value) j = j + 1 Loop m = j End With
What would be the syntax if data instead comes from, say, Z:Brazildata.xls. In general, when I retrieve data from another file, do I always have to use "With - End With" structure?
I inherited a spreadsheet to manage that is linked to a SharePoint table.
It is trying to populate a date that a certain "Tier" is selected (1, 2, 3, or 4).
It works great if I manually type in the tiers, but does not run on existing data (about 400 records) or lines that are updated and new via the SharePoint list.
How can I have this run on all of the existing lines and anything added or changed in the future from the list?
VB: Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("AD2:AD10000")) Is Nothing Then Application.EnableEvents = False
The code contain using Autofilter to some columns, & then copy paste to another sheet to first blank row, & sorting it by asscending in Column A. Both sheets are in the same workbook.
But for some reason, the Macro will giving the result expected if i run it step by step (by using F8 button under window VB editor). And if i call the Macro name it stoped in the middle of the process.
I am working on multiple sheets that collate to an “averages” sheet. The Averages sheet would need to collate the information historically.
I need a code that can be assigned to a button so when it’s clicked, the figures for this week are added to the figures that were from last week. So for example.
Worksheet 10, Rows 12 – 20 have the data from the last 8 weeks.
Worksheet 09 has this weeks data and a button to push. When pushed it adds the data from this week to row 21 in Worksheet 10.
The following week the button would add the data to row 22 and so on.
Essentially each time the button is pressed it drops the information in a row one lower than the last time the button was pressed.
I want to record the amount of people attending a venue over a given period. The information presents names and did they attend. In column "D' from D5 to D45 the information will be yes or no. I want a formula that calculates the amount of YES and shows the answer in a numeric format
I have it all set up already with macros and formulas and such. My next step is to record the sales data ( selected cells ) from the invoice to a seperate sheet I call Cashup. The idea being to record all relevant sales data for the day/trading session on one sheet so the manager can cash up the till run by 2-4 different people during the day. Its targeting a small family run business so security isn't currently the main concern although for future proofing I'm trying to build that in as far as possible. Long story short: What would be a good method for recording each successive sale's data from the same page each time the Finish button is hit , to a seperate sheet in a log format? Currently I have these problems:
1:The operation should record into the next open row on the Cashup sheet and not overwrite the previous record.
2:The record must ignore all formulas and record only the values in the cells . Currently a normal copy and paste results in " N/A" recorded in the cells in the Cashup screen.....( probably since all the data that needs recording is formula generated to begin with )
I am trying to collect data from a server. The data comes through as .csv (seperated data), and I am able to get all the useless info/columns out of the way - but I would like to keep a record of how many times these "alarms" come in. form a spreadsheet, or tell me how to go abouts using a tool to simplify my process.
I have 3 worksheets, one worksheet is a summary worksheet that gives totals for what the user chooses... like Actual vs Budget or Actual vs Prior... etc. When we are in our meetings a common question is where did these numbers come from?
I would like to create a macro button that when you are on a cell you can click the button and the macro well take you to the destination cell. Is there a way you can write a formula in to the macro that would use the formula in the current cell and take you to where that number comes from?
I have linked two workbooks together but now need to be able to convert that linked data to straight text. I have a v-lookup in the report that needs this to be this way. I do not want to use paste special values because that would defeat the purpose of linking the two pages.
I am trying to set up a new assurance work book and worksheet (worksheet2 lets say) which will dynamically link to another workbook and worksheet (worksheet 1) owned by another part of my business. The purpose using the data set in worksheet 1 is that this is the source data and is the most accurate for the project information. So I am looking up columns A, B and C in that workbook in my new worksheet2 using ='[Spreadheet 1.xlsx]Sheet1'!$A$1:$A$1174 the same for column B and C, with the aim that any new data entries within columns A, B and C will be populated in worksheet2.
In columns D to Z of worksheet 2 (my worksheet) I am applying some assurance metrics to the project information that is specific each row of column A, B and C which are dynamically linked to woorksheet1. My problem and it is completely eluding me is this....
The owners of worksheet1 regularly sort the data into chronological order based on Column A. However the data is not normally presented in this way i.e. all new entries regardless of date are added to the list at the bottom of worksheet1. The problem I have is, is keeping my row data in Columns D-Z linked to the row data in columns A-C of worksheet2 no matter what kind of sorting occurs to Columns A-C in worksheet1.
I'm trying to see if there is a way to speed up the process of doing multiple Google searches to see if I can find a website attached to a text. I have a list of websites, and am trying to update the list to remove dead/inactive sites, and also find the URLs for the live sites.
The list looks like this (sample): Comstock, Inc. ConNuestroPeru Contacto Latino contentSutra Core States Crains Chicago Crains Chicago Business Crains Detroit Crains Detroit Business Credential Crescent-News.com Crowell Weedon
Thus there's no website attached, and I need to manually find the URL myself. There are over a thousand cells in the list.
How to speed up the process of finding a url for these websites?
I have two linked workbooks, one containing a large data table, and the other containing individual worksheets that extract the data from the large table via HLOOKUP equations in each cell.
Is there a way to have Excel hide the row of a worksheet if the resulting HLOOKUP result in the cell in that row is empty? Also, if there is data in the cell, can I get Excel to automatically adjust the row height based in the incoming data and not on the HLOOKUP equation?
I am using OFFSET to make dynamic named ranges- the only problem is that I'm having trouble using it with linked data-
The Situation:
-Cells A1:H500 are linked to another sheet, with each cell containing a formula like =IF(ISBLANK('C:...[workbook.xlsx]worksheet'!A1),"",'C:...[workbook.xlsx]worksheet'!A1)
-Currently only cells A1:H100 have data, but this is dynamic and will change
-I want a named range that will only select the cells with data in them...the problem is that COUNTA counts the link formula even if the cell is "blank" (because it isn't blank).
-I hate hard-coding ranges...the solution for the named range =OFFSET($A$1,0,0,500-countblank($A$1:$A$500),8) is ugly!
Any solution to making dynamic named ranges with linked data that may or may not be "" ?
I've have a spreadsheet where 4 cells are linked to another workbook via a vlookup. the problem i have is that a lot of users can update this external book, or it can be saved as a seperate spreadsheet somewhere else on the network. If it was up2 me i would have them only update the one sheet, but as it stands its not. So what i want to be able to do is put some code onto a button on the sheet, from here i want the: Application. GetOpenFileName
method to open....but from here i want them to be able to pick the cells where the data is situated. Any clues..... i can get as far as them selecting a workbook. Do you think i will need to create another userform?? Maybe RefEdit? I'm not sure.
On a weekly basis I update a suite of charts in one workbook that are created from several other linked workbooks. Once compiled I send this via email to a colleague who incorporates it into another report by printing the charts as a pdf. For some reason he often ends up with erroneous data (zero's where there shouldn't be on the charts), or often his system doesn't display certain elements like titles and axes etc
We think his system is spending resource looking for the original data and getting itself knotted up, but surely there should be a way of delinking the charts but maintaining their integrity as they are saved?
On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
I have the following code that should open all EXCEL workbooks in a specified path, and unprotoect any password-protected worksheets to allow for Link Updates, then close the workbook after password protecting it.
sub UpdateAllLinks() Dim vLinkSources Dim iLinkSource As Integer Dim AnySheet As Worksheet sPath = " C:Documents and SettingsShaneMy DocumentsHarcourt Assessmentspassword" sName = Dir(sPath & "*.xls") do while sName <> ""...............
Bar those who don't believe/celebrate in Christmas, Hope you all had a good Christmas and New Year celebrations.
I have two sets of data, lets call them 2013 and 2014. I have a Spreadsheet already set up in excel, but currently I just past this information into a tab in excel (which isn't ideal at all), and given the data is growing, is slowing down my excel spreadsheet a lot.
I have some experience with Access, SQL and queries.
What I have done thus far is to link my Access DB with Excel, run a query and paste the resulting table from the query into excel all using VBA (easy peasy).
The problem i have is that the table is still linked, and i do not want or need it to be linked. I know that there is a "Unlink" Option, which is not what i need, and also a "Convert to Range" option which is also not what i need, but closer. This got me thinking, is it possible to import the data into an array (all in VBA in excel from the query in the SQL) and then i can paste that information into my Excel spreadsheet, or is there a better method?
I am trying to chart data from a single cell that is changing over time. The cell is linked to another software program that provides its value. I would like to be able to chart the changes in its value either every time it changes or on some time interval (i.e. every 3 minutes).
I am trying to create a combo box whereby i need to populate a list of name into the box and when user click on the name, it will linked it to the corresponding letter in MS Word.
Have tried a number of codes available in this forum (to populate the combo box) but were not successful.
I am attempting to put a drop down list using "Data Validation". I can get it working when my list is on the same sheet (sheet 1) and the column of cells I want the drop down list to show up in....(you know..when the drop down list shows up in each individual cell)......BUT...when I put the list on another sheet (sheet 2) and try to do the "Data Validation" back on sheet 1, excel won't let me go highlight the list on sheet 2.
I even tried writing sheet2 and the range and that still doesnt work.
I have a function, that one of the members in this forum wrote. that changes the title of a chart according to the text that is written in a specific cell. I added this code to a module: ...
I have a spread sheet in excel that I enter a date(a1) the supplier(b1) product ordered (d1) and an order number(g1) when I record a macro to copy theses cells in to a sheet called a purchase order sheet (i.e. A1,B1,D1,G1) the macro runs and prints off the order sheet how do I get the macro to run when I fill in line (A2 etc) when I enter a new order.
cell A3 contains a drop down list of student names.
say, William is the 1st student listed on cell A1.
when William is selected his math grade shows up on cell B3 & his English grade on cell C3
I figured out how to make cells B3 & C3 change according to selected student from cell A3. All data comes from the Database Table, which is locked/unedittable.
Is there a way to copy/transfer a cell value to an adjacent cell when the value of the cell changes. So if A1= 5, and it changes to 3, have B2 = 5 when this happens.
