I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Dim sNm As String, sRT As String
If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->
My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.
How I can show the longest string in a column, I've tried to find a formula that does this but it only shows the number of characters, not the actual string contents.
I have a list of dates in column A (sequential from low to high). I have corresponding rainfall data in column S.
I am trying to find the longest run of 0's in the rainfall data and return the start and end dates. It would be great if i could tell it (in cell C8) to find the longest run below this number. ie: i say i want to find the longest run of numbers below 5.
When i press the corner of a cell and drag it (example =D2) it will only go higher in the number (drag it 3 rows down it just goes D2, D3, D4, D5, D6).
Can I drag it down but make it stick to the 2 and instead go E2, F2, G2 etc?
I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.
My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.
So I found a great piece of code which calculates relative strength index. However, I've been trying to edit it to make it a bit more dynamic and I'm really struggling to get it to work.
Right now the initial code is:
Code: Function RSI(MyCells As Range) Dim up_day, down_day Dim average_up, average_down Dim RS, cellcount As Long Dim cll As Rangeups = 0up_day = 0downs = 0down_day = 0cellcount = 0For Each cll In MyCells
[Code] ..........
Basically I tried to amend this as follows:
Code: Function RSE(MyCells As Range, Length As Double)Dim up_day, down_day, ups, downs Dim average_up, average_down Dim rs, cellcount, rangecount As Long Dim cll As Range ups = 0
[Code] ..........
Basically i wanted it to look at the numbers in the range and then error check to see if it was a number... if it was a number great. If not then the length of numbers it should calculate across should increase by 1 as it has to include another number to calc across (hope this makes sense). however, i just cant get it to work at all. My code doesn't seem to make any difference. And it also raises the issue of the fact that the intial value for length also needs to be stored to do the final calc.
I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.
I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.
Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.
1st post so hope that title isn't too vague. Using VBA, I have a macro that will find a column based on a week number and add in a new column. It will then offset the ActiveCell down one. I now need the macro to SUM all values in that row to the left of the ActiveCell. My original thought was to use:
Unfortunately, there are gaps in the data field, blank cells that should count as zero value. How can I highlight all cells to the left, from whichever column the active cell is in, through to column B?
I am using office 2007 and here is the problem I am facing. I am using a formula and it is based on two columns data. The formula result is at C20:C2400, while the two data columns are at A20:A2400 and B20:B2400. I add one more row of data at A2401 and B2401, I expect the formula result would auto extend to C2401 but it doesn't, nothing happen.
I check that I need to turn on the auto extend check box in option, I check and it is already on.
I am trying to figure out how to create a formula or VBA to count how many contiguous 0's there are in a specific row...and then drag this formula down many rows..for example - if a row of data contains 1,3,0,0,0,4,5,0,0,0,0,0,0,0,5,3,1,0 I want the result to be 7, because 7 is the longest streak of contiguous 0's.
I'm looking for a formula which returns the longest consecutive series of occurrences of 2 chars in a text string.
In the case I'm trying to count the longest consecutive number of nine-spares (9/) in a bowling game.
For example:
|7/|X|9/|9/|9-|9/|9/|9/|9/|X8/|longest series of 9/ would be 4. |9/|9-|9/|9/|7/|9-|(8)/|X|7/|9-|longest series of 9/ would be 2. |9/|(8)1|X|X|72|63|7/|8/|8/|9/9|longest series of 9/ would be 1.
I have a database application which appends an "A" to the sequence number each time an entry is amended and then posts it as a new row in the database. This means there can be multiple entries with the same number but with different amount of "A"'s afterwards.
e.g. Cell A6 contains seq no 1 Cell A7 contains seq no 2 - Seq no 1 is subsequently amended so... Cell A8 contains seq no 1A - 1A is subsequently amended so... Cell A9 contains seq no 1AA - and so on and so forth..........
What i am trying to achieve is a formula which will look at the record number which is required (i.e. 1) and will search for record 1 with the most number of "A"'s appended. This way the user will only be amending the most up to date record.
I am editing a fairly sizeable name, address list and I would like the row highlight bar to extend across the page. For instance, when I click on the row number the highlight bar extends across the row, but when I hit return the highlight disappears and I have to click row by row. I would like the highlight bar to extend across a row and then move up or down with the arrow keys.
Right now I have a spreadsheet where I have a person enter in 4 differant values in 4 adjacent cells of a row, then in another 4 adjacent cells of the same row calculations are done with respect to the 4 rows of entered information then displayed. Is there a more effective way in placing and copying these formulas in the last 4 adjacent cells rather then just copying the formula into the first 3000 or so rows of cells?
I say 3000 because each project is differant and will require a differant amount of rows, but by copying the formula, even little IF statements, slows down the program and makes printing a hassle.
I have a worksheet that was originally saved as a xls (2003) file. I have resaved it as a xlsx (2007) file, but the max rows are still at 65,536. I am trying to combine over 100,000 records into one sheet. The only way I have been able to create a excel document with more than 65,536 rows is to export my records from an Access file.
I am using Excel 2003 SP3. How does one extend the the number of values in a list box and retain the same name to be used in the Data / Validation / Settings / Source entry? The only method I see is to extend the list of allowable values and give the longer list a new Name.
I was trying to set yp a formula that will automaticaly extend a series. I mean will add a sequential number to the one above. If I have in cell A1: CD001. I want to place a formula that will show me: CD002 in cell A2. I tried n function but it only converts it to the number and comes uout as 0. I don't know which one to use if there is any. Answer to the first question I will probably be asked: No I cannot drag it down with a mouse as I will place it in the conditional function afterall.
If my cursor is in A1 and I want to select all non-empty cells in that column, what is the VBA code that will do that?
(I'm a beginner and have tried recording a macro and inspecting the code, but that always gives me a specific number of cells to select, while I want it to vary by the number of cells that have values. )
I get 40~100 page PDFs of purchase orders every week. Each page has one part number and its open order and forecast information. Some pages have a few line of information, some have many lines.
I convert this file into a txt file.
I then create a macro to import it as space delimited and format it.
What I need to do it get rid of the header/footer information on EVERY page.
When the txt file is created it doesn't have any page marks.
The header has "ABC Widgets" as the customer name and then 10 rows of unnecessary information. If I do a Find ALL for "ABC Widgets" and select all, it will delete that header line at every instance, but the leftover selected cells then start not-lining up (meaning I can't just hit "delete row" 10 times and have it pick the right row--the rows don't line up any more and each instance is -1 row offset.)
Is there any way to extend the selection of non-contiguous rows (starting with ABC Widgets) down by 10 so I can delete them?
I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).
I have a column ( Total Hours) that I use to total the new numbers in the row.
If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula (=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))
How do I (Can I) get the formula to cascade into the new row.
For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.
It works great except fot the first row (A9) where it copies the header row (B8:E8).
How can I get it to not copy when data is entered into A9?
Private Sub Worksheet_Change(ByVal Target As Excel. Range) Dim c As Range, i As Long On Error Resume Next Set c = Intersect(Target, Columns(1)) If c Is Nothing Then Exit Sub If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub i = c.Row Application.EnableEvents = False Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i) Application.EnableEvents = True On Error Goto 0 End Sub
I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.
I found the following formula which, when used as a named range, automatically extends a list for data validation:
[Code] ....
I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?
