I have a worksheet with a column that lists the city where an item is located. If the city is say Austin I need to find the last cell in the range that has Austin in it. I can find lots of examples where you can find all instances within the range and to find the last cell in the range that has any data in but nothing that tells me how to find the last cell in the range that has a specific value.
View 9 RepliesI am in the process of designing an inventory like spreadsheet with prices. If possible, I'm trying to find a well to look at a column for every instance an item shows up (lets call them apples) & then find the LAST instance of apples showing up to pull the pricing of that row only. Is this possible?
View 9 Replies View RelatedI need a Macro that will find every instance of 0 within a workbook and then replace that value with an average of the values in the two cells above it and the two cells below it?
I want to use VBA code so that if cells within a column for instance, column I, is selected it warns the user 'Check you have used the correct column'.
View 7 Replies View RelatedI have a spreadsheet which I am trying to put in a macro to add a new page (below the current page in the same worksheet) so that users who are unfamilair with Excel can simply do this.
I have got the macro to copy the cells I want and can get it to paste but the issue is I don't always want it to paste in the same place. The current document is 1 page long but it may need to be up to 10 pages long. I am trying to get it to find the last instance of the word "COMMENTS" and then paste the copied info 3 rows below that.
In col A, I have the dates. I would like to create a formula in all the other columns in row 1 that will look fror the first instance of a value in each column and return the date that that threshold value is reached.
The value I am looking for is 3, but the values in the columns might look like 0 1 1 1 3 2 1 3 4 2 0 etc. I want to return the date of the FIRST instance of 3. How would I do this?
I have a workbook in instance1 named Workbook1 that can successfully refer to and open a file named data.txt in C:
I have a workbook in instance2 named Workbook2 that can't find the same data.txt file in C:
Why can one instance find the text file but the other can't? How do I work around this issue?
Note: Workbook1 and workbook2 are identical except for their name
Note: Both workbooks are confirmed to be in (and are isolated in) their respective instances
I would like to be able to add vb code to my developing macro that searches within 1 column for the last instance of a particular word, then inserts a blank row under that word. All the data is sorted, so the words will be used multiple times, but I need the row to go under the last instance of each word in the column.
I have this piece of code
Sub do()
Dim i As Integer
Dim x As integer
x = 5000
i = 1
Do Until i = x
If Cells(1, i).Value = "C" Then
ActiveCell.Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRigh
i = i + 1
End If
Loop
End Sub
I want it to search down the first column and find any instance of the letter C and shift it (along with the entire row) over two clumns to the right.
the code always opens a new instance of Outlook even though one may already be open. If left unchecked I may have like 30 instances open if I leave the computer for a few hours. So I was wondering is there some snippet of code I can add in here to check if excel is open first and if so do not open a new instance?
Code:
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = "xxxx@live.com"
[code].....
I need a formula in Column E to find the results for Column E or subtract A2 from C3, C3 from D3, A4 from C3, B5 from D5 and A6 from D6 .
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
[Code]....
I am working on a data entry sheet time and I am running into a wall. I am trying to code intelligence into sheet so that the user doesn't have to enter as much data.
I am using Excel for Mac, 2011, 14.2.3, But I would like to be able to have others use it on PC and in Excel 97 - 2004
I am developing a time sheet for work. I am trying to have excel generate the work order # for subsequent jobs.
'Daily Entry Sheet'!$B$8:$B$138 = Date
'Daily Entry Sheet'!$F$8:$F$138 = Property #
'Daily Entry Sheet'!$G$8:$G$138 = Property Location
'Daily Entry Sheet'!$H$8:$H$138 = Work Order Number
Following is what I presently have coded.
The following Works but is not completely as I need.
=IF(OR($F8=0,$F8="",ISNA(G8),$H7="Work Order #"),"",IF(ISERROR(INDEX($H7:H$8,MATCH($F8,Prop.,0))),"",INDEX($H7:H$8,MATCH($F8,Prop.,0))))
This works fine in that the match finds the first instance of the work order #. The problem is there may be a later worker order # for the same property which supersedes the old work order. I need to find that last work order number for property # 919 for example, not the first instance, as the index/match returns.
I looked for a function similar to the sumif or countif
=SUMIF(Prop.,$F4,WorkOrdNum)
=SUMIF(Date,"="&$V6,Total_____Hours)
But I don't need to add the work order numbers.
Countif can give me how many instances property # 919 is used,
But I haven't figured out how to use that to It would be ok if the function would return the largest number like the MAX function, best would be to select based upon the latest date.
I have a list of text cells in column A, the list is a dynamic one that changes depending on the property selected in a cell of another sheet.
Example data... Column A is Block Name, Column B is Room Number.
---------A----------------B-------
1--Main Building-------001
2--Block A--------------001
3--Block A--------------002
4--Main Building-------002
5--Block B--------------001
6--Block A--------------003
7--Main Building-------002a
8--BSS House----------BSS
9--Main Building-------003
What I need is for a formula that lists one example of each instance included in Column A.
As per the example data above, the expected results when dragging down from C1 through to C9 would be:
---------A----------------B--------------C--------
1--Main Building-------001-------Main Building
2--Block A--------------001-------Block A
3--Block A--------------002-------Block B
4--Main Building-------002-------BSS House
5--Block B--------------001-------
6--Block A--------------003-------
7--Main Building-------002a-------
8--BSS House----------BSS-------
9--Main Building-------003-------
I know I could use a pivot table to do this but as I need this to be dynamic, and also to be used with Excel on an iPad Air, I can't as it doesn't support pivot tables or VB to automatically refresh it.
As a part of a much larger routine, I need a code to find five consequetive cells in a column with identical value "XYZ" and select the first one of them. Say,
I have a column:
apples,
pears,
apples,
oranges,
xyz,
oranges,
xyz,
xyz,
xyz,
apples,
apples,
apples,
xyz,
xyz,
xyz,
xyz,
xyz,
xyz,
xyz,
etc.
I need to select the twelfth cell in this column, highlighted red.
I guess there could be some complicated if-then loop to go through the whole column, checking each cell and comparing it to the next four, but it would take forever to excecute... Is there some other way around? To find the first occurence of five xyz's one directly under another?
I am looking for a formula that will output the highest and lowest value in a range. Column A contains a range of numbers, some of which repeat and in column B i'd like to extract ONE instance of the highest and lowest value in Column A, preferable the last instance.
See example below and in column B the desired output.
Column A
160
90
120
90
120
160
Column B
blank
blank
blank
90
blank
160
I just used the excellent formula =row(index(range,match(1,index,--(range)>0),0),0))) to give me the first instance of a number, but i am wondering if there is a formula to give me the last instance of the number as well? Maybe? This would save me much time if possible.
View 11 Replies View RelatedI have 26 ranges defined on my worksheet sheet3. Each range is 10 rows high and 4 columns wide. On my sheet1, I'd like to be able to go automatically to a specific range on sheet3 (I need to provide the choice to go to any of the 26 ranges), selected perhaps by a combobox. The ranges are building occupancy groups like A1, A2, B, E, R1, R2, etc. (26 of them). Then I'd like the user to look at each row in that particular range, select one, and then have the four values in that row placed in corresponding cells on sheet1.
I have this set up now using a four column combobox, and it works just like I want, except that only the first column of data is displayed after a row is selected. If I could get those four cells of data as they appear in the combobox inserted on sheet1, I'd be fine. But I can't. So I'm looking for a work-around. Basically, my goal is to get a specific row of data (4 cells wide) from my named ranges copied into cells on sheet1. But I need to be able to get to the correct range automatically.
I m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?
View 2 Replies View RelatedI am receiving the error "unable to get the find property of the range class" in my code. I have attached my code and highlighted red where my error is occuring.
On Error Goto ProductionRptEngineListExport_Error
Dim objWrkTmp As Excel.Workbook
Dim ObjWrkshtActive As Excel.Worksheet
Dim objWrkshtTmp As Excel.Worksheet
Dim RngStart As Excel.Range
Dim CurrentRowNum As Long, RowCnt As Long
Dim GroupRowNum As Long, ColCnt As Long
Dim chtChart As Excel. chart
Dim intwrktmp As Integer, intwrktmpTot As Integer, intLastRow As Long
Dim sngwrk As Single
ctlStatus = "Opening Production Report..."
DoEvents........................................
I'm using the below VBA
Dim c, DataRow
With Data
Set c = . Range("A5:A350"). Find(KPI, LookIn:=xlValues)
If Not c Is Nothing Then
DataRow = c.Row
End If
End With
Now,If KPI is for example = "Favourite Hotel" and if i have data that looks like :
Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham
Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).
How do i make it search for an exact match?
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I have the following
Private Sub test2()
Dim c As Range, t As Long
With Worksheets(3)
.Unprotect
Set c = Range("AO10", cells(Rows.Count, "AO").End(xlUp))
t = Application.WorksheetFunction.Max((Application.WorksheetFunction. CountIf(c, ">=.01") * 2), 0)
Sheets("Template").Range("D3").value = t
'Range("D3") = t
End With
End Sub
The code is suppose to get the value of >= 1 percent from AO10 then go down the column until it finds the second instance and then add that number to the tile.
Thanks for any help in advance.
Have a great Friday all! : D
Kurt
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View 9 Replies View RelatedI am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.
View 2 Replies View RelatedI need a simple formula which does the following: I have a column of numbers, and I want to find the first instance which is equal or greater than a specific number. For example,
Column A
23
41
67
89
51
21
If the specific value is 55 the formula needs to return: 67
if the specific value is 10 the formula needs to return: 23
Using Excel 2003 SP2. The formula =vlookup("apple",table,2,false) would return the data in the second column of the array named "table" for the FIRST INSTANCE it comes across for the word "apple." Is there a formula to have it return data for the LAST INSTANCE? This is one where I say to myself "Self, there has got to be a way to do this," but I just can't figure out what it might be.
View 4 Replies View RelatedI have a cell..say A1...which contains a value...now I have a row say A2:A15 which contains 14 values and I want to highlight the first instance of cell A1.
View 4 Replies View Relatedhow to close the instance of excel created by my VBS script.
I run the script to open a workbook run a macro within the workbook and save it. I'd like to also close the workbook and the instance of excel.
If I close the workbook within the end of my macro I get an unknown run time error from my script. If I elect to just save the file and close it within the end of script, the workbook closes but the instance of EXCEL.EXE is still running in task manager.