Replacing row values

[catlair]

Hi,

I have a macro with the following included:

iDataRows = Application.CountA(Range("A:A"))

ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(--(LEFT(R2C1:R170C1,2)=""AP""),--(R2C7:R170C7=""Reactive""),
--(R2C11:R170C11=""BD""))"

I wish to substitute all the R170 with the value from iDataRows. I
tried something like this:

"=SUMPRODUCT(--(LEFT("A2:A" & iDataRows,2)=""AP""),--("G2:G" &
iDataRows=""Reactive""), --("A2:A" & iDataRows=""BD""))"

Didn't work... Any suggestions?

 

[Ken Johnson]

Hi,

I have a macro with the following included:

iDataRows = Application.CountA(Range("A:A"))

ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(--(LEFT(R2C1:R170C1,2)=""AP""),--(R2C7:R170C7=""Reactive""),
--(R2C11:R170C11=""BD""))"

I wish to substitute all the R170 with the value from iDataRows. I
tried something like this:

"=SUMPRODUCT(--(LEFT("A2:A" & iDataRows,2)=""AP""),--("G2:G" &
iDataRows=""Reactive""), --("A2:A" & iDataRows=""BD""))"

Didn't work... Any suggestions?

Hi Catlair,

The following resulted in the formula in the active cell being...

=SUMPRODUCT(--(LEFT(A2:A19,2)="AP"),--(G2:G19="Reactive"),
--(A2:A19="BD"))

when the COUNTA(A:A) was 19...

Dim iDataRows As Long
iDataRows = WorksheetFunction.CountA(Range("A:A"))

ActiveCell.Formula = "=SUMPRODUCT(--(LEFT(" & "A2:A" & iDataRows &
",2)=""AP""),--(" & "G2:G" & iDataRows & "=""Reactive""), --(" & "A2:A"
& iDataRows & "=""BD""))"

I have not included any code line breaks, so just change
ActiveCell.Formula = "=SUMPRODUCT etc...to one continuous line after
pasting into VB Editor.

Ken Johnson

 

[catlair]

Hi Ken,

I ended up with the following:

=SUMPRODUCT(--(LEFT('A2':'A170',2)="AP"),--('G2':'G170'="Reactive"),
--('K2':'K170'="BD"))

The apostrophes causes the results to reflect as #NAME?.

 

[Ken Johnson]

Hi Ken,

I ended up with the following:

=SUMPRODUCT(--(LEFT('A2':'A170',2)="AP"),--('G2':'G170'="Reactive"),
--('K2':'K170'="BD"))

The apostrophes causes the results to reflect as #NAME?.

Hi Catlair,

So, have you got it working?

Ken Johnson

 

[Ken Johnson]

Hi Catlair,

What I mean is...

Where did the apostrophes come from?
Are they part of another solution to your problem, or are you saying
that they appeared in the cell formula when you ran my suggestion?
I don't get them.

Ken Johnson

 

[catlair]

Hi Ken,

Yes, they appear in the cell formula. You didn't get them? Let me check
again.

 

[Ken Johnson]

Hi Catlair,

That's right, I don't get them.

Ken Johnson

 

[catlair]

Hi Ken,

The apostrophes are still there. Can't figure out why. Any other
suggestions?

catliar

 

[Ken Johnson]

Hi Ken,

The apostrophes are still there. Can't figure out why. Any other
suggestions?

Hi Catlair,

I'll email a worksheet with the code in place, then we'll see how that
goes.

Ken Johnson