#1
|
|||
|
|||
Dymanic Range Starting at a Cell Defined by a Count
Hello,
Im trying to run a dynamic range starting at a cell defined by another cell. The first part of my dynamic range works, ending at a cell defined by another cell. If CheckBox1.Value = True And CheckBox2.Value = True Then With Sheets("Summary").Range("E22") .FormulaArray = "=IFERROR(INDEX('1600 Daily'!G$3:G$300, SMALL(IF('1600 Daily'!$F$2:$F$300=1, ROW('1600 Daily'!$F$2:$F$300)-2),ROWS(G$2:G2))),"""")" .AutoFill Destination:=Range("E22", Cells(Range("M4"), "E")), Type:=xlFillDefault End With End If I just need this formula performed across the range of cells. It seems to be this small section that doesnt work 'Range(Cells(Range("M4"), "E")))' Thanks in advance for any help anyone can provide! |
#2
|
||||
|
||||
Which cell holds the value of the end row?
|
#3
|
|||
|
|||
Hello,
There are 2 Cases, in one E22 is the start point and ("E" & M4) is the end. In the second case ("E" & (M5)) is the start point and E300 is the end point. Where M5 = M4+2 |
#4
|
||||
|
||||
On which sheet should M4 and M5 be referenced?
|
#5
|
|||
|
|||
Hi,
They're both referenced on the sheet "Summary", however the data in the formula that needs to be filled down is referenced from another sheet "1600 plant". It is not necessary to copy that exact formula for the time being - a simpler one will only be necessary for me to understand what to do. :-) |
#6
|
||||
|
||||
Then:
Code:
With Sheets("Summary").Range("E22") .FormulaArray = "=IFERROR(INDEX('1600 Daily'!G$3:G$300, SMALL(IF('1600 Daily'!$F$2:$F$300=1, ROW('1600 Daily'!$F$2:$F$300)-2),ROWS(G$2:G2))),"""")" .AutoFill Destination:=.Range("E22", .Cells(.Range("M4").Value, "E")), Type:=xlFillDefault End With |
#7
|
|||
|
|||
you give no clue what you are trying to achieve so not sure if it helps but:
you should never be using big complicated formulae like this in VBA créate a new tab in your spread sheet set cell a2 to your formula with cell a1 as input to it and cell a3 as the output you want test until it works under all circumstances use vba to put the input value into a1 and read the answer from a3 it looks from what you are trying to do, that not using VBA at all is probably the best option - you are not trying to rewrite Excel from scratch |
#8
|
||||
|
||||
Why on earth not? If it's an appropriate formula, why shouldn't you use code to populate the worksheet with it?
|
Tags |
array, autofill, dynamic range |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count how many have completed each Training Course within a Date Range | dmcmillion | Excel | 3 | 10-20-2014 09:59 AM |
Worksheet defined cell sums | flextera | Excel | 3 | 06-17-2014 11:12 AM |
Data validation list filter with range defined by OFFSET | Mango123 | Excel | 4 | 03-18-2014 02:52 PM |
Range.Information(wdStartOfRangeRowNumber): Application-defined or Object-defined err | tinfanide | Excel Programming | 2 | 06-09-2012 10:19 AM |
How can I fill cell color starting from Cell D5 using Conditional formatting instead | Learner7 | Excel | 0 | 07-08-2010 05:50 AM |