Multiple formulas in one Cell with VBA

Hello,

I'm trying to put one big formula (one formula which contains others) in one cell using VBA but it does not seem to work.
It works when I put just one simple formula such as (that's just an exemple):

Range("O2").Formula = "=MAX(A1:A20)"

This works fine.
But now let's say I have 2 formulas:

Range("O2").formula = "=MAX(A1:A20)&" "&MIN(A1:A20)"

This does not work!
Althought if you use =MAX(A1:A20)&" "&MIN(A1:A20) in a regular EXCEL cell it works just fine.
It seems that VBA does not recognize the multiple forumlas that I'm aksing him to put in the cell.
However, it works just fine in Excel!
Does someone know how to go around this problem please?

Thank you!

P.S.: The actual formula i'm trying to use is much more complex. I'm just using MAX and MIN as an exemple to illustrate my problem.

Try this and report back.

Range("O2").Formula = "=MAX(A1:A20)&"" ""&MIN(A1:A20)"

Originally Posted by BigBas

Try this and report back.

Range("O2").Formula = "=MAX(A1:A20)&"" ""&MIN(A1:A20)"

Hello,

Thank you for your answer.
Great it works! but what did you do? Just add " and " ?

Now would you do the same if the actual formula is:

Range("O2").value= "=IF(LEN(D23)-LEN(SUBSTITUTE(D23,"[",""))>1,TRIM(MID(LEFT(D23,FIND("|",SUBSTITUTE(D23,"[","|",2))-1),FIND(">",D23)+1,255)),TRIM(MID(RIGHT(D23,FIND("|",SUBSTITUTE(D23,"<","|",1))+255),FIND(">",D23)+1,255)))"

Thanks again!

Edit: I used "," althought in my excel sheet I use ";" instead. I think in VBA "," should be used right?

The easiest way to get the correct syntax is to use the macro recorder to generate the code for the formula.

Yikes, that is a HUGE formula. Essentially, I used 2 quotation marks to indicate that it was not the end of the formula statement (consider that the formula is opened with a quotation mark, and the second one would usually close it formula.

Try this and report back:

Sub jdlkfsldk()
    Range("A1").Value = "=IF(LEN(D23)-LEN(SUBSTITUTE(D23,""["",""""))>1,TRIM(MID(LEFT(D23,FIND(""|"",SUBSTITUTE(D23,""["",""|"",2))-1),FIND("">"",D23)+1,255)),TRIM(MID(RIGHT(D23,FIND(""|"",SUBSTITUTE(D23,""<"",""|"",1))+255),FIND("">"",D23)+1,255)))"


End Sub

Keep in mind, this is a very long formula. Have you considered if there is a VBA alternative for whatever you are trying to extract?

Originally Posted by BigBas

Yikes, that is a HUGE formula. Essentially, I used 2 quotation marks to indicate that it was not the end of the formula statement (consider that the formula is opened with a quotation mark, and the second one would usually close it formula.

Try this and report back:

Sub jdlkfsldk()
    Range("A1").Value = "=IF(LEN(D23)-LEN(SUBSTITUTE(D23,""["",""""))>1,TRIM(MID(LEFT(D23,FIND(""|"",SUBSTITUTE(D23,""["",""|"",2))-1),FIND("">"",D23)+1,255)),TRIM(MID(RIGHT(D23,FIND(""|"",SUBSTITUTE(D23,""<"",""|"",1))+255),FIND("">"",D23)+1,255)))"


End Sub

Keep in mind, this is a very long formula. Have you considered if there is a VBA alternative for whatever you are trying to extract?

Oh my God!
It works!
But please tell me, how did you know where to put the "? I dont get it.
You said you just added two " ?
Only two?

I have not considered a VBA alternative as I created this formula on Excel as I found it easier (given the complexity of it).

thanks a lot!

Could you describe the basic purpose of the formula?

I was trying to step through it, but without some representative sample data it's hard to follow.

Originally Posted by jason.b75

Could you describe the basic purpose of the formula?

I was trying to step through it, but without some representative sample data it's hard to follow.

Hello,

Sure:

The goal is pretty simple:

I have data in cells that shows (each line is a cell):
[27/07/2012] <John> Waiting for news[15/06/2011] <john> In going
[20/06/2012] <John> no news[14/04/2011] <Mike> sent[27/07/2011]<Mike> waiting
[15/07/2012] <Mike> in going[27/07/2011] <Eddy> Draft in progress
[15/07/2012] <Mike> e-mail sent

I have many lines like this and what I want to do is keep only the last comment without the name or the date. So I must keep only the comment on the left (such as "waiting for news") without dates or names.
The IF() of the formula allows me to make the formula work when I have only one comment (as shown in the last lane: [15/07/2012] <Mike> e-mail sent).
Basiccaly, 2 formulas are created: one that take care of stripping down everything when I have 2 or more comments with dates and names; and the other one when there is only one comment with one name and one date.

Actually the correct formula is this one, there was one little typo in the one I put before:

=IF(LEN(D22)-LEN(SUBSTITUTE(D22;"[";""))>1;TRIM(MID(LEFT(D22;FIND("|";SUBSTITUTE(D22;"[";"|";2))-1);FIND("]";D22)+1;255));TRIM(MID(RIGHT(D22;FIND("|";SUBSTITUTE(D22;"<";"|";1))+255);FIND(">";D22)+1;255)))

So the =LEN(<range>)-LEN(SUBSTITUTE(<range>,"a","")) allowas me to count the number of occurence of the bracket"[". When it's above 1 it means I'm facing multiple comments. Then, the formula used is TRIM(MID(LEFT(D20;FIND("|";SUBSTITUTE(D20;"[";"|";2))-1);FIND("]";D20)+1;255))
which allows me to replace "[" by | and keep only what's on the left, the left comment.
Otherwise (if I have only 1 bracket. cf. IF statement), this formula is used (second part):TRIM(MID(RIGHT(D22;FIND("|";SUBSTITUTE(D22;"<";"|";1))+255);FIND(">";D22)+1;255)))
Here I use RIGHT instead of LEFT
the end result is this

Waiting for news
no news
in going
e-mail sent

I hope this helps! I think I forgot to explain a few points but I have to go now. I'll try to get back to you if some points are missing

How about a UDF to simplify things?

Public Function lastmessage(str As String) As String
    lastmessage = Trim(Split(Split(str, ">")(1), "[")(0))
End Function

Formula =lastmessage(D22)

Jason, it looks like he is trying to extract data between certain delimiters (<> and []).

Jeffy,

When you are adding a formula through VBA, " " should surround the ENTIRE formula as you've done. For example "=SUM(A1:A10)" . Whenever there are quotes that are meant to be WITHIN the formula, you have to replace a each instance of a quotation with two instances. I'll extract a portion of your formula to demonstrate.

"=IF(LEN(D23)-LEN(SUBSTITUTE(D23,"[",""))>

The first " opens the formula. The other " are all within the formula. This means that EACH instance of a single " must be replaced with 2.

"=IF(LEN(D23)-LEN(SUBSTITUTE(D23,""["",""""))>

Hello,

i'm trying to insert the formula in column E and to Copy paste it in each cell as long as the cell in front of it in COlumn D is not EMpty. The loop works but I dont understand how to make the formula work. Can someone help me please?

Sub RepeatFormula()
Dim Cell As Range
  Dim LastRow As Long
  Dim R As Long
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    LastRow = Wks.UsedRange.Rows.Count - Wks.UsedRange.Row + 1
    
      For R = Wks.UsedRange.Row To LastRow
        If Wks.Cells(R, "D") <> "" Then
           Wks.Cells(R, "E").Formula = "=lastmessage(WHAT SHOULD I PUT THERE?)"
        End If
      Next R

End Sub

How do I tell VBA to refer to the cell in Column D to activate the forumla please?

Thx a lot!

This is easier done with R1C1 references

Wks.Cells(R, "E").FormulaR1C1 = "=lastmessage(RC[-1])"

R with no number refers to the same row, C[-1] offsets 1 column to the left, using the cell that the formula is being entered into as a reference point.

Originally Posted by jason.b75

This is easier done with R1C1 references

Wks.Cells(R, "E").FormulaR1C1 = "=lastmessage(RC[-1])"

R with no number refers to the same row, C[-1] offsets 1 column to the left, using the cell that the formula is being entered into as a reference point.

Awesome. I knew I had to use something like that but I was not able to make it work...
Thanks again and thank you for your exlpanations

You're welcome.

You can do away with the For - Next loop as well, it's just as easy to write the formula to the whole range at once, if not easier.

You could even condense the whole procedure down to 1 line (not including Sub and End Sub).

Sub repeatformula()
Intersect(ActiveSheet.UsedRange.Offset(1), Range("D:D")).SpecialCells(2, 2).Offset(, 1).FormulaR1C1 = "=lastmessage(RC[-1])"
End Sub

Just a quick check on what you're doing there.

Offset(2) would start in row 3.

Because we're using offset with usedrange, it shifts the range down Offset(#) rows, it should ignore any empty rows and start from the first 'used' row.

If you want to play with the ranges to see how it works, try this code, it's the method I use to check that code is working with the correct range, without making any changes to the sheet.

Sub showrange()
MsgBox Intersect(ActiveSheet.UsedRange.Offset(1), Range("D:D")).SpecialCells(2, 2).Offset(, 1).Address(0, 0)
End Sub