• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FormulaArray & Replace Assistance

P

Paul Dodson

New Member
Hello -

I'm fairly new to VBA, and attempting to get my array formula consolidated to within the 255 character limit has taken me hours, only to end up still unsuccessful. Below is the code I currently have:

Code: 
Dim FormulaPart1 As String
Dim FormulaPart2 As String
Dim FormulaPart3 As String
Dim lastrowdata As Long

lastrowdata = Cells(Cells.Rows.Count, "B").End(xlUp).Row

FormulaPart1 = "=SUM(IF((RC1=Test!R2C11:R" & lastrowdata & "C11)*(Test!R2C22:R" & lastrowdata & "C22<DATE(2016,2,1))*(Test!R2C22:R" & lastrowdata & "C22>DATE(2015,12,31)),""1+YYYY"",""""))"
FormulaPart2 = "1/COUNTIFS(Test!R2C11:R" & lastrowdata & "C11,RC1,Test!R2C2:R" & lastrowdata & "C2,Test!R2C2:R" & lastrowdata & "C2,+ZZZZ)"
FormulaPart3 = "Test!R2C22:R" & lastrowdata & "C22,""<""&DATE(2016,2,1),Test!R2C22:R" & lastrowdata & "C22,"">""&DATE(2015,12,31)),0))"
   
    With ActiveSheet.Range("B4")
        .FormulaArray = FormulaPart1
        .Replace """1+YYYY"",""""))", FormulaPart2, lookat:=xlPart
        .Replace "+ZZZZ)", FormulaPart3, lookat:=xlPart
    End With

The normal array formula I am trying to break up is below (with the exception of row 2000 being replaced by lastrowdata):
Code: 
{=SUM(IF(($A4=Test!$K$2:$K$2000)*(Test!$V$2:$V$2000<DATE(2016,2,1))*(Test!$V$2:$V$2000>DATE(2015,12,31)),1/COUNTIFS(Test!$K$2:$K$2000,$A4,Test!$B$2:$B$2000,Test!$B$2:$B$2000,Test!$V$2:$V$2000,"<"&DATE(2016,2,1),Test!$V$2:$V$2000,">"&DATE(2015,12,31)),0))}

Can anybody help me write this correctly? I've been researching for a while, and I can't seem to get solve the issue.
 
Try:
Code: 
Range("B4").FormulaArray = "=COUNT(1/FREQUENCY(IF(($A4=Test!$K$2:$K$" & lastrowdata & ")*(Test!$V$2:$V$" & lastrowdata & "<DATE(2016,2,1))*(Test!$V$2:$V$" & lastrowdata & ">DATE(2015,12,31)),MATCH(Test!$B$2:$B$" & lastrowdata & ",Test!$B$2:$B$" & lastrowdata & ",0),0),MATCH(Test!$B$2:$B$" & lastrowdata & ",Test!$B$2:$B$" & lastrowdata & ",0)))"
 
Hi ,

Try this :
Code: 
Public Sub ArrayFormula()
          Dim FormulaPart1 As String, FormulaPart2 As String
          Dim lastrowdata As Long

          lastrowdata = Cells(Cells.Rows.Count, "B").End(xlUp).Row

          FormulaPart1 = "=SUM(IF((RC1=Test!R2C11:R" & lastrowdata & "C11)*(Test!R2C22:R" & lastrowdata & "C22<DATE(2016,2,1))*(Test!R2C22:R" & lastrowdata & "C22>DATE(2015,12,31)),1/COUNTIFS(Test!R2C11:R" & lastrowdata & "C11,RC1,Test!R2C2:R" & lastrowdata & "C2,Test!R2C2:R" & lastrowdata & "C2,Test!R2C22:R" & lastrowdata & "C22,""<""&DATE(2016,2,1),Test!R2C22:R" & lastrowdata & "C22,YYYY),0))"
          FormulaPart2 = """>""&DATE(2015,12,31)"
         
          With Sheet1.Range("B4")
                .FormulaArray = FormulaPart1
                .Replace "YYYY", FormulaPart2, xlPart
          End With
End Sub
Narayan
 
NARAYANK991 said:
Hi ,

Try this :
Code: 
Public Sub ArrayFormula()
          Dim FormulaPart1 As String, FormulaPart2 As String
          Dim lastrowdata As Long

          lastrowdata = Cells(Cells.Rows.Count, "B").End(xlUp).Row

          FormulaPart1 = "=SUM(IF((RC1=Test!R2C11:R" & lastrowdata & "C11)*(Test!R2C22:R" & lastrowdata & "C22<DATE(2016,2,1))*(Test!R2C22:R" & lastrowdata & "C22>DATE(2015,12,31)),1/COUNTIFS(Test!R2C11:R" & lastrowdata & "C11,RC1,Test!R2C2:R" & lastrowdata & "C2,Test!R2C2:R" & lastrowdata & "C2,Test!R2C22:R" & lastrowdata & "C22,""<""&DATE(2016,2,1),Test!R2C22:R" & lastrowdata & "C22,YYYY),0))"
          FormulaPart2 = """>""&DATE(2015,12,31)"
        
          With Sheet1.Range("B4")
                .FormulaArray = FormulaPart1
                .Replace "YYYY", FormulaPart2, xlPart
          End With
End Sub
Narayan
Click to expand...

Narayan - This seems to be the closest to helping me, but I didn't give you the correct code. I replaced the true sheet name with "Test", when really the sheet name is "Employee Data". Below is the TRUE code from my macro.
Code: 
Dim FormulaPart1 As String
Dim FormulaPart2 As String
Dim FormulaPart3 As String
Dim lastrowdata As Long

lastrowdata = Cells(Cells.Rows.Count, "B").End(xlUp).Row

FormulaPart1 = "=SUM(IF((RC1='Employee Data'!R2C11:R" & lastrowdata & "C11)*('Employee Data'!R2C22:R" & lastrowdata & "C22<DATE(2016,2,1))*('Employee Data'!R2C22:R" & lastrowdata & "C22>DATE(2015,12,31)),""1+YYYY"",""""))"
FormulaPart2 = "1/COUNTIFS('Employee Data'!R2C11:R" & lastrowdata & "C11,RC1,'Employee Data'!R2C2:R" & lastrowdata & "C2,'Employee Data'!R2C2:R" & lastrowdata & "C2,+ZZZZ)"
FormulaPart3 = "'Employee Data'!R2C22:R" & lastrowdata & "C22,""<""&DATE(2016,2,1),'Employee Data'!R2C22:R" & lastrowdata & "C22,"">""&DATE(2015,12,31)),0))"
   
    With ActiveSheet.Range("B4")
        .FormulaArray = FormulaPart1
        .Replace """1+YYYY"",""""))", FormulaPart2, lookat:=xlPart
        .Replace "+ZZZZ)", FormulaPart3, lookat:=xlPart
    End With

I apologize for providing the incorrect code. I took the code you provided and replaced "Test" with "Employee Data", and it did not work for me. Is it critical to have the Private Sub? I have a lot more code prior to this array formula so I only pulled the code that is causing the error. Again, I'm very new at VBA and I am trying to learn as much as I can, but this array formula has stumped me. Thank you so much for your assistance!
 
Debaser said:
Try:
Code: 
Range("B4").FormulaArray = "=COUNT(1/FREQUENCY(IF(($A4=Test!$K$2:$K$" & lastrowdata & ")*(Test!$V$2:$V$" & lastrowdata & "<DATE(2016,2,1))*(Test!$V$2:$V$" & lastrowdata & ">DATE(2015,12,31)),MATCH(Test!$B$2:$B$" & lastrowdata & ",Test!$B$2:$B$" & lastrowdata & ",0),0),MATCH(Test!$B$2:$B$" & lastrowdata & ",Test!$B$2:$B$" & lastrowdata & ",0)))"
Click to expand...

Debaser - Thank you for the attempt to assist. I mistakenly provided the incorrect sheet name in my code. The correct sheet name is "Employee Data" and not "Test". Also, it seems that the code you provided is past the 255 character limit for array formulas. That is the error I have been encountering. To my knowledge, the code will be past 255 characters so I have been trying to research different ways of replacing pieces of the array formula to allow >255 characters.
 
It was some way under 255 with the information provided. ;)

With the new sheet name you could do this:
Code: 
Dim FormulaPart1 As String
Dim FormulaPart2 As String
Dim lastrowdata As Long

lastrowdata = Sheets("Employee data").Cells(Rows.Count, "B").End(xlUp).Row

FormulaPart1 = "=COUNT(1/FREQUENCY(IF(TRUE,MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0),0),MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0)))"

FormulaPart2 = "($A4='Employee data'!$K$2:$K$" & lastrowdata & ")*('Employee data'!$V$2:$V$" & lastrowdata & "<DATE(2016,2,1))*('Employee data'!$V$2:$V$" & lastrowdata & ">DATE(2015,12,31))"
   
  With ActiveSheet.Range("B4")
  .FormulaArray = FormulaPart1
  .Replace what:="TRUE", replacement:=FormulaPart2, lookat:=xlPart
  End With
 
Debaser said:
It was some way under 255 with the information provided. ;)

With the new sheet name you could do this:
Code: 
Dim FormulaPart1 As String
Dim FormulaPart2 As String
Dim lastrowdata As Long

lastrowdata = Sheets("Employee data").Cells(Rows.Count, "B").End(xlUp).Row

FormulaPart1 = "=COUNT(1/FREQUENCY(IF(TRUE,MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0),0),MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0)))"

FormulaPart2 = "($A4='Employee data'!$K$2:$K$" & lastrowdata & ")*('Employee data'!$V$2:$V$" & lastrowdata & "<DATE(2016,2,1))*('Employee data'!$V$2:$V$" & lastrowdata & ">DATE(2015,12,31))"
  
  With ActiveSheet.Range("B4")
  .FormulaArray = FormulaPart1
  .Replace what:="TRUE", replacement:=FormulaPart2, lookat:=xlPart
  End With
Click to expand...

Thank you, Debaser. It seems we are almost there! This formula actually works, and replaces properly which is MUCH improvement. The formula doesn't quite function the way I need it to though. I am trying to refer to "Employee Data" sheet, and capture all employees who were hired after 12/31/15 and before 2/1/16 (Column "V"). Once identified, I then need to only count an employee once because an employee has multiple records within the sheet. To do this, I have been referring to the unique identifier in column "B". I need this formula to run for each department, which is column "A" of my active sheet. I hope I have helped provide some more insight into how I'm using it. Thank you for tackling this!
 
In what way is it incorrect? Can you post a sample workbook and indicate how and where it is calculating wrongly?
 
Debaser said:
In what way is it incorrect? Can you post a sample workbook and indicate how and where it is calculating wrongly?
Click to expand...


Attached is an example file.

On the "Monthly Analysis" sheet in B4, I am trying to calculate the employees who started after 12/31/14 and before 2/1/15 (the dates have changed for this example) per Department, but you will notice in the "Employee Data" sheet that employees have multiple records due to this report generated each month with new "Month Active" and "Month Term" columns added each time the report is ran (I'm hoping this makes sense).

When I run the code you provided (with the dates reflecting year 2015), it still calculates "1" even for departments that did not have a new hire for that month.

Again, thank you so much for helping. If I need to provide more clarity with any piece, please let me know.
 

Attachments

  • Example.xlsx
    159.3 KB · Views: 7
Sorry, there was an error in the formula. Please use:
Code: 
FormulaPart1 = "=COUNT(1/FREQUENCY(IF(TRUE,MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0),FALSE),MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0)))"
 
Hi ,

See if this works :
Code: 
Public Sub FormulaArray()
          Dim FormulaPart1 As String, FormulaPart2 As String
          Dim lastrowdata As Long

          lastrowdata = Cells(Cells.Rows.Count, "B").End(xlUp).Row

          Application.ReferenceStyle = xlR1C1

          FormulaPart1 = "=SUM(IF((RC1='Employee Data'!R2C11:R" & lastrowdata & "C11)*('Employee Data'!R2C22:R" & lastrowdata & "C22<DATE(2016,2,1))*('Employee Data'!R2C22:R" & lastrowdata & "C22>DATE(2015,12,31)),TRUE))"
          FormulaPart2 = "1/COUNTIFS('Employee Data'!R2C11:R" & lastrowdata & "C11,RC1,'Employee Data'!R2C2:R" & lastrowdata & "C2,'Employee Data'!R2C2:R" & lastrowdata & "C2,'Employee Data'!R2C22:R" & lastrowdata & "C22,""<""&DATE(2016,2,1),'Employee Data'!R2C22:R" & lastrowdata & "C22,"">""&DATE(2015,12,31))"

          With ActiveSheet.Range("B4")
                .FormulaArray = FormulaPart1
                .Replace "TRUE", FormulaPart2, lookat:=xlPart
          End With
        
          Application.ReferenceStyle = xlA1
End Sub
This will insert the following formula :

=SUM(IF(($A4='Employee Data'!$K$1:$K$2)*('Employee Data'!$V$1:$V$2<DATE(2016,2,1))*('Employee Data'!$V$1:$V$2>DATE(2015,12,31)),1/COUNTIFS('Employee Data'!$K$1:$K$2,$A4,'Employee Data'!$B$1:$B$2,'Employee Data'!$B$1:$B$2,'Employee Data'!$V$1:$V$2,"<"&DATE(2016,2,1),'Employee Data'!$V$1:$V$2,">"&DATE(2015,12,31))))

Narayan
 
Debaser said:
Sorry, there was an error in the formula. Please use:
Code: 
FormulaPart1 = "=COUNT(1/FREQUENCY(IF(TRUE,MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0),FALSE),MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0)))"
Click to expand...

This works perfectly! Just what I was needing! Thank you so much!
 
NARAYANK991 said:
Hi ,

See if this works :
Code: 
Public Sub FormulaArray()
          Dim FormulaPart1 As String, FormulaPart2 As String
          Dim lastrowdata As Long

          lastrowdata = Cells(Cells.Rows.Count, "B").End(xlUp).Row

          Application.ReferenceStyle = xlR1C1

          FormulaPart1 = "=SUM(IF((RC1='Employee Data'!R2C11:R" & lastrowdata & "C11)*('Employee Data'!R2C22:R" & lastrowdata & "C22<DATE(2016,2,1))*('Employee Data'!R2C22:R" & lastrowdata & "C22>DATE(2015,12,31)),TRUE))"
          FormulaPart2 = "1/COUNTIFS('Employee Data'!R2C11:R" & lastrowdata & "C11,RC1,'Employee Data'!R2C2:R" & lastrowdata & "C2,'Employee Data'!R2C2:R" & lastrowdata & "C2,'Employee Data'!R2C22:R" & lastrowdata & "C22,""<""&DATE(2016,2,1),'Employee Data'!R2C22:R" & lastrowdata & "C22,"">""&DATE(2015,12,31))"

          With ActiveSheet.Range("B4")
                .FormulaArray = FormulaPart1
                .Replace "TRUE", FormulaPart2, lookat:=xlPart
          End With
       
          Application.ReferenceStyle = xlA1
End Sub
This will insert the following formula :

=SUM(IF(($A4='Employee Data'!$K$1:$K$2)*('Employee Data'!$V$1:$V$2<DATE(2016,2,1))*('Employee Data'!$V$1:$V$2>DATE(2015,12,31)),1/COUNTIFS('Employee Data'!$K$1:$K$2,$A4,'Employee Data'!$B$1:$B$2,'Employee Data'!$B$1:$B$2,'Employee Data'!$V$1:$V$2,"<"&DATE(2016,2,1),'Employee Data'!$V$1:$V$2,">"&DATE(2015,12,31))))

Narayan
Click to expand...

Thank you, Narayan! This formula works just how I was hoping mine originally would. Thanks for the assistance.
 
Debaser said:
Sorry, there was an error in the formula. Please use:
Code: 
FormulaPart1 = "=COUNT(1/FREQUENCY(IF(TRUE,MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0),FALSE),MATCH('Employee data'!$B$2:$B$" & lastrowdata & ",'Employee data'!$B$2:$B$" & lastrowdata & ",0)))"
Click to expand...

Again, I have hit another road block trying to use this formula for another column. For the "Total Employee Count" column, I'm trying to write the code where where I remove the >12/31/14 requirement and somehow include a requirement that MATCHes "*Jan*" and "*Active*" within the header row to find the appropriate column of the Employee Data sheet, and require that there be a "1" to show that the respective employee was active within that month. I've tried using OFFSET with MATCH, but I've been unsuccessful. Do you know how to structure the formula to check for these conditions?
 
I think you mean:
Code: 
FormulaPart2 = "($A4='Employee Data'!$K$2:$K$" & lastrowdata & ")*(INDEX('Employee Data'!$2:$" & lastrowdata & ",0,MATCH(""Jan Active"",'Employee Data'!$1:$1,0))=1)"
 
Debaser said:
I think you mean:
Code: 
FormulaPart2 = "($A4='Employee Data'!$K$2:$K$" & lastrowdata & ")*(INDEX('Employee Data'!$2:$" & lastrowdata & ",0,MATCH(""Jan Active"",'Employee Data'!$1:$1,0))=1)"
Click to expand...

You are an Excel wizard! You actually taught me more applications with using INDEX and MATCH. The only thing I had to change was to write MATCH(""*Jan""&""*Active*""... because the reports are not always ran the same way everytime, but the column headings always include a 3-letter abbreviation for month and either Active or Term. Thank you SO MUCH!
 
You must log in or register to reply here.
Back
Top