Dymanic Range Starting at a Cell Defined by a Count

[jap7675]

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!

[Debaser]

Which cell holds the value of the end row?

[jap7675]

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

[Debaser]

On which sheet should M4 and M5 be referenced?

[jap7675]

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

[Debaser]

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").Value, "E")), Type:=xlFillDefault
End With

should work.

[Graham Griggs]

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

[Debaser]

Originally Posted by Graham Griggs

you should never be using big complicated formulae like this in VBA

Why on earth not? If it's an appropriate formula, why shouldn't you use code to populate the worksheet with it?