Dynamic Formula

[jhumphrey]

I'm using excel 2003 / VBA Editor

I'm trying to have the following formula entered into a cell, but can't get it passed the debugger.

Please Login or Register  to view this content.

I'd like the formula to, as an example, to be evaluated like the following:

=SUMIF($D$3:$D$197,"<"&DATE(YEAR($D$3),MONTH($D$3),16),H$3:H$197)

Any thoughts on where I'm going wrong?

[royUK]

The VBA Formula mixes VBA with Excel Functions which isn't going to work, for example you use End(xlUp).

You will need to creat Dynamic Named Ranges for the formula instead of tryin to set the range using End(xlUp)

[jhumphrey]

Please remember to wrap code

I've been able to use mixed (VBA and Excel Formula) coding when inserting a formula into excel via VBA.

I think the issue is with the component of the formula that requires quotation marks to encapsulate the formula (Criteria section of the SUMIF formula).

Here is an example of a formula that mixes VBA and Excel Formulas, which isn't far off from what I'm trying to do.

Please Login or Register  to view this content.

[rylo]

Hi

If you really only want to limit the range of the data to process, then something like

Please Login or Register  to view this content.

I've just taken the current formula and put in the code to find the last row in column D (wasn't sure if column A would produce the right result) and adapted to allow the " to stand in the formula.

HTH

rylo

[jhumphrey]

Thanks, that did the trick! I did want to use column "A" to determine the row number, but that was any wasy swap.

Much appreciated.