I have one column of data (numbers). Within this column, are the values for two different categories...which are separated by a blank row. And then a total for each product given one column over; like this:
Product1 123
Product1 456
Product1 789 1368
Product2 456
Product2 123
Product2 456 1035
Either product can increase/decrease in rows, so I am unable to set a static range. The only solution I got on my own was to use SUMIF (sum the column of values if it matches the name "Product1" or "Product2"). This is fine until someone misspells the product name or leaves it off completely; which is why I prefer to go off the actual values.
My 1st preference is to go off the totals. Basically, return the 1st value (ie 1368) in the column, and then in another cell, return the 2nd value in the column (ie 1035).
My 2nd preference would be to sum the individual values in the column until a blank cell is encountered, and then return that value. Then continue on until another blank cell is encountered, and return that value as well.
I know very little of vba, so I haven't taken that route, but I bet this is easily done as a UDF (although I would prefer not to do it that way).
We have an excel worksheet with textfields, that are compiled (when a btn is clicked) and the results are assigned to a static range. (Meaning, the results always appears in the same cells)
Question is: How can i have the results appear in a different row each time, starting on row 14 ?
I've got a formula that takes a value from the last numerical entry in a column. Is there a way to code this so that I don't have to enter the cell location of that number manually every time I append the sheet?
Finding a link and deleting it. When I open a specific excel file I am working on I get an update links error
Unable to open [URL].... Cannot locate the Internet server or proxy server.
- I have done a search for "[" throughout the whole workbook and found nothing neighter within functions nor values - I have looked at the Names and found nothing of the sort - I have looked for hyperlinks within 2-3 images and found nothing - I have looked through the VBA code
This does not happen when I open other excel files....
I'd like to use the INDEX & MATCH functions to look in a table (in 'misc') and find a value contained within a specific cell in Sheet 1 but I am getting a #REF error and I'm not sure why.The formula is as follows:
I know how to delete duplicates using the function under the data tab, but how can i locate them. For instance, a spreadsheet with a list of PO numbers, how can i do a search to see if it is showing the same PO number multiple times?
In the attached sample workbook, (Excel 2007 format), there are hundreds, maybe thousands, of drawing objects and rectangles. They are mostly located near the top of column AC. You can't see any of them, because they are empty, or formatted to have no line and no fill, ubt if you move the cursor around in that area, it will eventually give you the option to "select" one of the objects. They appear to be stacked on top of each other.
This bloats the worksheet terribly. I have managed to shave the size of the attached sample down to 100K, bu deleting about 200 of the objects, but if I save this balnk file as an Excel 2003 file, it is 1.3 Mb.
I have discovered how to show what objects are on the worksheet, by selecting "Find and Select" and choosing to show the "Selection Pane". This pane shows hundreds of blank objects in the sheet. When this pane is open, however, if I try to select and delete an object, Excel locks up on me.
I have the below formula that pastes data into the worksheet entered in the " " it works great but only when the workbook was last saved on that sheet, how can it locate that worksheet sheet even if the the workbook was last saved and another worksheet. if the workbook was saved on a different sheet name the vba doesn't finish
I am having a problem with locating a certain date. What I am trying to do is scan down a list of dates and have returned the row is is in. I have been using the MATCH function and it was fine except when there is no MATCH. In the case where there is no match I would like it to select the next cell.
I am currently using this =MATCH(Search!G5,Data!F1:F10005,0) where Search!G5 is the date I want to find Data!F1:F10005 is the set of dates.
I have a workbook that requires a multiple of 3 to be entered in a cell. If a quantity other than a multiple of 3 is entered, a Msgbox pops up and requires the quantity to be changed to such value.
I am looking for where this is handled. I found part of it in a Private Sub:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub But I cannot locate where the other parts of the code is stored.... I would like to see how this is handled so I can incoporate this in my own worksheets.
This formula finds the last space from the right and returns the values less the last space and whatever is to the right- =TRIM(IF(LEFT(Y25,2)="~C",REPLACE(LEFT(Y25,FIND("`",SUBSTITUTE(Y25," ","`",LEN(Y25)-LEN(SUBSTITUTE(Y25," ",""))))),1,FIND(" ",Y25),""),RIGHT(Y25,LEN(Y25)-FIND(" ",Y25))))
Results of a item of a whole size: LAKAI KOSTON SLCT INDY SHOE BK
However- I forgot I had added a criteria to convert records that have a fractional value from decimal to a fractional value and an extra space shows up. I need to alter the above formula to handle this additional condition, returning the value w/ out the second space and the size 10 shown below.
Results of an item w/ a fractional size, ie. 10 1/2:
LAKAI KOSTON SLCT INDY SHOE BK 10
Linked to this post: new post started to help avoid confusion: but wanted to show prior material... http://www.mrexcel.com/board2/viewto...766e4507d1a402
I am trying to append about 15 files of CSVs. I have code that works on importing the data, placing it at the end of the previous data, but then it clears the previous data. Here is the code
Sub import_BCDV() Dim lastrow As String Selection.End(xlDown).Select Selection.End(xlUp).Select ' Range("A515").Select lr = FindLastrow1() lastrow = "A" & lr MsgBox lastrow Range(lastrow).Select ' "TEXT;J:QA ReportsQA ReportsWorkbenchBCVD 1-11-09 1-17-09.csv", _..........................
I have created a form that will be used my many people. The first person will enter their information and click a button to transfer their data to a second workbook, as well as send the workbook via email. At a later time, someone else will add more data to the emailed form and transfer this to the database in the second workbook. What I need is to be able to find the row in the second workbook containing the data transferred the first time and add the data that was put in later to the same row as the original.
take a value from an input box and use it locate a record?
On my "Menu" sheet, the user will click a button which opens the input box and asks them enter a record number (format = PCAR.yy.nnn.xxxx)
The records are stored on a "Log" sheet and the lookup value is located in column "F".
I found code on another post which I used to create the input box, but don't know how to take this input and use it to find the relevant record.
I have the following code, which I use to find the first empty row in which to add a new record. if there is an error (record not found) to return to the input box to reenter the number.
Sub GetPCAR() Dim Pcarnum Pcarnum = InputBox("Please enter the PCAR number") MsgBox ("Searching PCAR") & Pcarnum End Sub
Sub AddPCAR() Dim rNextCl As Range
Set rNextCl = Worksheets("PCAR Log").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Worksheets("PCAR Log").Activate rNextCl.Select ActiveWindow.DisplayHeadings = False End Sub
I have a Workbook (only 1 sheet) that is filled out each month and consists of sales and revenue data. I need to copy the total sales and total revenue and paste them to another sheet. The only problem is that it is never consistent as to which cell the totals will be located in. They will always be in the same column, but almost never in the same row.
For example, one month there will be 8 individual sales and the total will be in Cell F13, but the next month will be F20. THe same needs to be done with Total Revenue (which is always one cell right of the Sales Total) I need to copy and paste the cell with Total in it (in this case F13) and do the same for each month after. This is not difficult to do in Excel, but i need some code to include this process in a macro.
Is there a way to reference a column depending on its header? Is there a way of doing this without the need of surveying one-by-one all the column headers with a for loop? I need this cuz if I have a very dinamic program which moves columns arround and depending on the state of the program the position of a column can change.
I have a large worksheet (about 9,000 rows) with lots of vlookups to another sheet.
It is possible that some of the lookups will return '#N/A' but it is laborious to go through all the rows to check.
Is there a way to have a macro to look through that part of the worksheet, and to pop up a message box with the cell reference of the first error it finds? (I'm sure there is a way! I just dont know it)
Struggling a bit on this one! I have tried the index function, but doesn’t quite work how I need it. Consider the attachment as an example:
I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is ‘Index’ is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.
I have about 20k records with dealer codes and brands listed. I need to be able to see the duplicates from the dealer numbers and brands. Is there a formula that can be used to locate them and see them before removing them?
I have an array of data with both positive and negative numbers. I want to locate the largest number (farthest from zero) regardless of sign. But I will need to retain the sign because I will be dividing every number in the array by that number to normalize the array to values between 1 and -1?
I needed to convert PartNumber to a string using Cstr(PartNumber).
The following VB code searches an Excel spreadsheet for a Production Order and then looks for a Part Number in the corresponding row. The Production Order can be listed multiple times, but there is only 1 Production Order/Part Number combo. When searching for the Part Number, it will find letters, numbers with a dash and letter/number combos, but won't find integers.
For example, it will find ABC, 3500-01 and T1000, but not 200.
Is there a formula I can use to locate the last active cell in a column. I did use the count function, however this isn't always reliable if there are blank cells within the column
Other than using the search on a 1 by 1 basis, is there an easier way to locate Declared variables that are unused in the code and/or Public variables that are only used in 1 sub/function?
I need a formula that will tell me which 'BPS' a person should receive based off of the count of loans closed (between 'Criteria 2' & 'Criteria 2.1' and/or the volume (total of 'Loan Amount')(between 'Criteria 1' & 'Critera 1.1'), whichever gives the person a the higher tier.
I have provided an example below. In the example John Doe should receive .01750 'BPS' because he closed over 1 million in volume. If, for example, John Doe closed 5 loans for 400,000, then he would receive .01600, since he met the criteria for the 2nd tier based off of # of loans closed.
The formula needs to take into consideration which 'Comp Plan' the person has, as well as the other criteria mentioned above.
I have the simple code below to perform and filter and copy:
Code:
Sub Macro1() Sheets("Data Dump").Select Range("A1:F100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "K1:L73"), CopyToRange:=Range("P1:U1"), Unique:=False End Sub
However, the CriteriaRange can change depending on which OptionButton the user chooses on a different tab. My reference the range is in Cell N1. So how can I use Indirect to refer the range in Cell N1 instead of the current CriteriaRange of K1:L73?
