range dynamic formula

  1. A

    Macro that sorts data with blanks at the bottom

    I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows: I have a template with formulas that cannot be changed (I cannot paste special values, or clear the...
  2. B

    For loop with offset function

    How to I increment the x and y data sets by one? ------------------- Sub main_ols() Dim x, y, z, Dim strMsg As String For x = 2 To 500 x = RangeToArray(Sheet4.Range("B2:B51")) y = RangeToArray(Sheet4.Range("C2:C51")) z = regress_orthols(x, y)...
  3. B

    Dynamic Selection of Dynamic Ranges reporting sets of negative values

    I have a large range of automatically generated columns that report daily predicted inventory levels going off into the future about a year so that's 360+ columns of data. These numbers can be positive, negative, or 0. Given a user supplied integer "N" I need to find the StartDate of the Nth...
  4. H

    Help needed - check the size of a range and create a new array

    Hi Guys, I'm a total newb at VBA so please excuse any possible nonsense. I've searched the web and this site for possible solutions to no vail. I have a data extract in excel that changes is size everyday today the data range is A1:R5232 with a header. It's always saved to a new workbook...
  5. R

    Application-defined or object-defined error on sort command

    Hi! I have the following line of code that keeps giving me a "Run-time error '1004': Application-defined or object-defined error," and I can't seem to figure out why: .Range(.Cells(7, 1), .Cells(x, lastcol)).Sort Key:=Columns(mycol), Order:=xlAscending, Header:=xlYes My table extends from...
  6. S

    Last non-blank row in a column part 2

    Hi, Apologies if this has already been asked, but I'm after a reasonably concise (and non-VBA if possible) way of identifying the row number of the last non-blank row in a set of rows from a given position (like the line number of the last word in any given paragraph of text on page with 20...
  7. N

    Display minimum of numbers or text if range criteria is met

    =IF(AND(A4:A6>=0,A4:A6<=5,COUNTIF(A4:A6,"NS")),MIN(A4:A6),"NS") This equation doesn't seem to capture the full logic of what I'm trying to accomplish. In column A, we have a validated data cell where the only allowed values are: 1,3,5, NS. In the column where the above equation fits, I would...
  8. S

    Dynamic ranges

    Hello, I have a sheet called “Calculation” on which column E that contains (from row 4 below) approx. 50 numbers (results from a formula) and somewhere in these 50 numbers one row that contains the text “maximum” (also as a result from a formula). This formula is for row 4...
  9. D

    Need To Cycle Through Range ("F2-F800")

    The below code snippet only handles one value for one cell: StrURI = Range("F2") In the same column ("F") I have 800 values that I need to cycle through. Any help would be appreicated...
  10. S

    Runtime error 1004

    Hi all Can anybody tell me why this bit of code would result in an 'Application-defined or Object-defined' 1004 error message? Range("AI2").Formula = "=TRIM(IF(ISERROR(FIND(""["",L2)),"",MID(L2,FIND(""]"",L2)+1,4)))" This works well on the Excel workbook but when I put it into VB I get...
  11. G

    dynamic range -1 row

    I am trying to dynamically select the range on a worksheet based on the cell not being blank but the last row of my data is a totals row. I can figure out how to get my data but I can not figure out how to reduce the selection by 1 row so that the totals row is not included. My data starts in...
  12. brettr87

    For Next Loop Trouble

    I'm sure this could be an easy fix for someone, but somehow this is escaping me at the moment... Dim AC Range("L6") = AC For AC = 0 To ActiveCell.Offset(RowOffset:=1) Next Sheets("15 mn bucket").Names.Add Name:="LEAD", RefersTo:=ActiveCell Sheets("15 mn bucket").Names.Add Name:="ChtVal"...
  13. J

    VBA Code for Sorting by Subset

    I have reports that are formatted like so: A:A Vendor: [Vendor A] Item 1 Item 2 Item 3 Vendor: [Vendor B] Item 1 Item 2 Vendor: [Vendor C] Item 1 ... 1) the number of items per vendor varies 2) the vendor lines are always in bold 3) the item lines always begin with "??-" (as it would be...
  14. B

    Working with a variable Range

    My MACRO is used daily to copy data from several sheets resulting in a new sheet with a differing number of rows with every run. My problem is that I need to define this range of rows in order to do an AUTOFILL function. I know I can use a cell to hold the row count using COUNTA. However, I...
  15. F

    VBA Copy/Paste Range Macro HELP!!

    Hi Excel Experts!! I am attempting to write a macro to do the following..... 1) Select all the data on the current sheet (HIST). Data spans from column A:H. I will call this Selection 1. 2) Paste Selection 1 Underneath the last row of selected data. (Creating Selection 2.) 3) Change the...
  16. A

    Dynamic Range?

    I want to create a formula in column C that refers to the maximum value in column B, up to that row. I.e. I would like a formula that equates to MAX(B8:*current_row*) where *current_row* is the whatever row the current formula in column C is in. This is what I am *trying* to do...

Some videos you may like

This Week's Hot Topics

Top