VB - Excel formula problem 1

[btrini]

Hi all, I am having a problem with the following Excel formula:WS_2.Range("BJ" & CStr(Temp_Cell)).FormulaArray = "=SUM((DATE(YEAR('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & " ),MONTH('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & " ),1)<=DATEVALUE(""1/""&LEFT(L" & CStr(Temp_Cell) & ",5)&""/2010""))*('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & " <> """")*('SOURCE TM DATA'!$Q$2:$Q$" & LastRow & "=""OPS - Investment Distribution & Operations Service"")*1)"

If I reduce the length of the string "OPS - Investment Distribution & Operations Service" it works. Also if I simply type the formula into excel without reducing the length of the string, it also works. So I do not think it is the length of the formula in excel. I am thinking I may have to split the formula into two parts but I am not sure how I will do that either.
Any help would be appreciated.

 

[SkipVought]

Hi,

First, this is the MS Office forum, not the forum707. which is where you will get focused advice.

I always wonder what the reason is for codeing a worksheet formula in VBA. There may be a very good reason, but I'd like to know the justification.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

You are limited to 255 characters when setting the formula using VBA. If LastRow is four digits or more, then you exceed that limit. You can eliminate a few characters from your formula to squeak under that limit.

I like to put formulas in a string variable when debugging so I can make sure they are correctly formulated.

Dim frmla As String
Dim LastRow As Long, Temp_Cell As Long
frmla = "=SUMPRODUCT((DATE(YEAR('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & "),MONTH('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & "),1)<=DATEVALUE(""1/""&LEFT(L" & CStr(Temp_Cell) & ",5)&""/2010""))*('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & "<>"""")*('SOURCE TM DATA'!$Q$2:$Q$" & LastRow & "=""OPS - Investment Distribution & Operations Service""))"
WS_2.Range("BJ" & CStr(Temp_Cell)).Value = frmla

Another trick you might consider is putting the formula (using SUMPRODUCT) in the cell as text, as shown in the snippet above. The formula should evaluate (I am testing in Excel 2003, using a 269 character formula).
Brad

 

[GlennUK]

Can you use Analysis Toolpak functions? If so, using EOMONTH and changing the logic will save about 40 characters:

WS_2.Range("BJ" & CStr(Temp_Cell)).FormulaArray = "=SUM(('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & "<=EOMONTH(DATEVALUE(""1/""&LEFT(L" & CStr(Temp_Cell) & ",5)&""/2010""),0))*('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & " <> """")*('SOURCE TM DATA'!$Q$2:$Q$" & _
LastRow & "=""OPS - Investment Distribution & Operations Service"")*1)"

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

Easiest option is to put the text "OPS - Investment Distribution & Operations Service" in a cell somewhere (I often use lookup sheets for stuff like this) and then reference that cell:

WS_2.Range("BJ" & CStr(Temp_Cell)).FormulaArray = "=SUM((DATE(YEAR('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & " ),MONTH('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & " ),1)<=DATEVALUE(""1/""&LEFT(L" & CStr(Temp_Cell) & ",5)&""/2010""))*('SOURCE TM DATA'!$AJ$2:$AJ$" & LastRow & " <> """")*('SOURCE TM DATA'!$Q$2:$Q$" & LastRow & "=" & Sht_Lkup.Range("strFrmLkup").text & ")*1)"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Also, what's in column L:

DATEVALUE(""1/""&LEFT(L" & CStr(Temp_Cell) & ",5)&""/2010"")

that you have to convert to the correct date?

You could either have something more useful there, or convert the date in another cell on that row.

Cheers, Glenn.

Beauty is in the eye of the beerholder.