I need to find a term "aa" and upon finding this term, the term on the right must be "bb" before we capture the number. Output will be 15 for this case.
Note that we do not know which cell "aa" will occur in, but the term "bb" will always be on the right of "aa". Can anybody help me with this? I have attached an example for your reference. Hopfully by modifying the below code, i can get the value i desired.
Sub test2()
Dim xx As Variant
Set rng = Cells.Find(What:="aa" , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
If Not rng Is Nothing Then xx = rng.Offset(0, 3).Value
MsgBox xx
If rng Is Nothing Then xx = 0
MsgBox xx
I have streaming data coming into my excel spreadsheet. The data is in 1 cell and its a number that changes almost every second. How can I capture and store this data.
I have a moving average spreadsheet that I am having trouble with. In column F of 'Data for Pivot' Tab, I have a set of data that doesn't begin populating until 101 data points (cell C8 in 'Mov_Avg_Chart' is the input; in this cell F138 of 'Data for Pivot' tab) after data begins populating in Column E of 'Data for Pivot' Tab (in this case data begins populating in Column E in row 38).
What I would like to do is have the data in cells F38 to F137 = the value in cell F138. From there on forward (cells F138 and onwards), I would like the formula to calculate data in the same way as it is currently calculating it. That is, I would like the values in cells F38 to F137 be 0.00110 (in the current example). However, the data will be constantly changing. Thus, sometimes the data in column F will begin at a different cell than cell F138. In essence, I want the formula: when column E begins populating data but column F has not, I want those column F values to equal the value of the first populated value in column F (which is usually numerous cells below this value).
I just can't seem to get this one right after spending considerable time on it. I would appreciate any help you can give on this.
I have an excel file with column stating month and rows are phases ... for each Phases we add the revenue numbers by month and it does keep changes as and when scope changes. However, i am finding it difficult to capture the changes ... is there a way i can set the guidelines to ensure all historical data changes as and when any updates and it should store the historic inputs for consolidation ... i would like to be called as Baseline, Re-baseline1, Re-beseline2 like wise.
how data capturing works, using controls. I try to explain what I mean. I have a userform with a combobox that allows to select data from a worksheet in another workbook (database). Using VBA, I'm asked to define the "source" for the combobox. I don't understand the difference between controlsource and row source because when I close the userform, I find only the first field filled in (column "A"), while I would like the entire row to be copied.I also would like to have an "ok" button to allow me for multiple selection. In other words:
I select the desired data from the combobox I click on the "ok" button ahd this event should copy the data (entire row) selected via the combobox on my worksheet without closing the userform in order to allow me to select other data on the same session. The row to copy data into is selected using the firstemptyrow method..
Trying to capture data from specific fields (which are populated with live data collected elsewhere) into new fields based on date. Ex:
A1 - A4 have "totals" derived from an external source, and the fluctuate daily.
I want to take today's totals and drop them into D1 - D4. Tomorrow, I want totals dumped into E1 - E4, the next day they go into F 1- F4, and so on... in other words, I'm tracking daily totals over time.
I've started with a macro that I would attach to BeforeSave as follows--
find the attached file. Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.
I'm trying to do is definetly simple to a lot of you, but I'm struggling on how to get started. I attached a workbook that clearly shows my work! I'm almost there but not quiet. Basically the user will be able to make a slection that will be used to lookup values from a different sheet. Each selection triggers a ranking number at the bottom (red pattern) in the worksheet. Now I would like to capture all scenarios and display the ranking.
In the Data tab under Connection Properties, I have "Refresh every x minutes" checked. Question is how do I capture in a cell within a sheet the last time it was refreshed?
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days) wk1 - Day1 to Day8 (8 colums) wk2 - Day9 to Day16 (8 colums) wk3 - Day17 to Day24 (8) wk4 - Day25 to day 31 (7) columns
(30 days) wk1 - Day1 to Day8 (8 colums) wk2 - Day9 to Day16 (8 colums) wk3 - Day17 to Day23 (7) wk4 - Day23 to day 30 (7) columns
I have a program running on my computer called Orbits.
What Orbits does is monitor a race track, and via a transponder in each race car, when a race car crosses the start/finish line Orbits will record that infomation.
The orbits program continually feeds data out the port 50000.
I can start hyper-terminal (yes the free one that comes with windows) and create a link the the computer running orbits. I use IP and port (Ex. 10.0.0.1 and port 50000).
As the race is going on I can see the data in hyperterminal: Below is a sample.
Here A and B value is keep on changing from external source, when A1=9.30am, the B1 value 120, then C1 should capture the B1 value and stored in it. like wise it captured and stored for every 5 minit or as per requirement.
I am using the following code to browse to a CSV file and import it into my current workbook. How can I “capture” the name of the text file (myFile) for future use?
myFile = Application.GetOpenFilename("All Files,*.csv") If myFile = False Then Exit Sub End If
' Open file With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("A1")) .Name = "DATA" .FieldNames = True...............
for example we have AS1 cell in which appear first number 15, then it change to 5, then it change to 15, then it change to 11 etc. What i need is to capture and lock just the first shown number (in this ex. it has to be 15) and put it to another cell (for ex. cell AT1) Maybe there is any formula ?
I have a lot of workbooks with a date as a name (eg. 2004-08.xls or 1999-03.xls). I would like to capture the date portion of this name with a formula in sheet 'Date' cell 'A4'.
I am preparing a template on the con-call done with various states. What I want to do is to capture their log in time to the call.
1) As soon as participants log in, we need to input the time of that moment. Click or double click with the cell should give me the current time.
Then; 2) Need to calculate how many participants, logged in to the call On-time by considering the log in time captured. 3) Similarly, we need to count the participants joined within 10 minutes of call and who are late comers.
I want to extract just the last name from a cell that contains the full name and put just the last into a seperate cell.
WHAT I AM DOING: i copy the contents of a internal screen onto a spreadsheet that i use to check various peices of info and calculations. when i save I like to save my sheet with the last name and then acct number. The internal system screen combines the customers names into a single cell. Right now i have to type the last name into a new cell and have a simple save macro that concatenates that last name with the acct number and saves it into the appropriate folder.
The field i am pulling from is always formatted with FIRST NAME then MI (IF PROVIDED) and then LAST NAME. So when i dump the screen contents into excel A20 may be MIKE SMITH, or MIKE T SMITH. so i need something that looks backwards in the cell and stops at the first space and dumps SMITH into another cell of my choosing (B1 in this case)
In my sheet, i have a list of machines in cells A2 to A5 & list of Plants in cells B2 to B5 as below
Col A..........Col B Mach.........Plnt A...............1 B...............2 C...............3 D...............4
What i want to capture is the last machine selected by clicking and also the value of the last plant selected by clicking I want the machine last selected (clicked) to be in captured in cell D2 & the Plant last selected (clicked) to be in captured in cell D3.
i.e if i click Machine C in column A then go the column B and click Plant 4
I would like an input box that asks users a select a range of cells....After this certain range of cells is selected....I would than like that range to be copied to a different sheet.
I have a cell (for example, "A1") which is inserted with a WINROS formula to retrieve some data into my spreadsheet. And it is running live at all time.
Actually, I can't think of any formulas to capture the value from cell "A1" to "B1". Because I do not want the value that I captured into cell "B1" running live. I just want the value "B1" fixed after captured.
I have a number result in a cell (d1) from a calcuting formula elswhere on the spreadsheet.
As the worksheet is constantly calculating the result in d1 keeps increasing and decreasing
I would like a formula in cell f3 that will give the result 1 if the d1 has reached the number 45. If cell d1 has not had the number 45 appear f3 will be 0
How to modify this code so that I select only one column triggers the time stamp update? For e.g. if i make any changes in column A, the date stamp is updated in the corresponding cell in column B. Basically, I am trying to narrow down to only one cell in the row, but it should work for any row in the sheet.
Refer to the below post: [URL] ....
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row > 1 Then Cells(Target.Row, "B") = Now() End Sub
I have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10.
I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO).
I tried using a SUMPRODUCT but my formula didn't work.
Is there another formula that will give me this calculation?
I am looking for a formula to capture the "date" of my first sale to new customers.
A B C D E F G H Date of first sale 1 Co. Name 2/2/2012 6/5/2012 7/6/2012 10/16/2012 1/22/2013 4/17/2013 2 ABC Co. $10 $11 $5 $12 $12 2/2/2012 3 Smith Co. $19 1/22/2013 4 Brown Co. $11 $15 6/5/2012 5 AAA Co. $5 $10 7/6/2012
I'm trying to capture a set of forecast dates in a spreadsheet. We have column 1 = forecast date, column 2 = actual date.
I also have at the top of the sheet (which I will hide when I get the forumula to work), today's date, today's date less 13 days and today's date - 27 days.
In column 3, I want a traffic light system whereby if the date in column 2 is equal to or less than (before) each of the 3 hidden dates it the cell colour will turn green, amber then red..
