range dynamic formula

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...

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)...

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...

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...

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...

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...

=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...

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...

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...

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...

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...

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"...

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...

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...

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...

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...