• 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.

Dynamic Range in SUMIF

M

Manojkumar S

New Member
Hello Team,

I would like to know whether the range can be used dynamically in SUMIF using VBA?

DataFilterClmn & EffortCalColumn value mentioned in below code will be changed dynamically.

When i execute the code, nothing is happening. Can you suggest how to use dynamic range in SUMIF?

Code: 
Set DataSheet ="XYQ"
Set DataFilterClmn = "B"
Set EffortCalColumn = "D"
Set rValue = EffortCalColumn & ":" & EffortCalColumn
Set rName = DataFilterClmn & ":" & DataFilterClmn

Set wkbkMacros = ActiveWorkbook
Set wkbkSource = Workbooks.Open(SrcFilename)
   
Set WSNew = wkbkSource.Worksheets.Add(After:=Sheets(Worksheets.Count))
WSNew.Name = NewSheetNme

WSNew.Cells(LastColumnNo, 1) = Application.WorksheetFunction.SumIf(wkbkMacros.Sheets(SrcSheetName)!rName, DataSheet, rValue)
   
[\Code]
 
Hi ,

Please upload your workbook with the data and code in it.

If this is not possible , post all of the code within this procedure , instead of just a segment.

Narayan
 
Hello Narayank991,

Please find the complete procedure.

Code: 
Sub Effort_Summary_Run()
   
  Dim My_Range As Range
  Dim CalcMode As Long
  Dim ViewMode As Long
  Dim FilterCriteria As String
  Dim CCount As Long
  Dim WSNew As Worksheet
  Dim sheetName As String
  Dim rng As Range
  Dim DataFilter As String
  Dim DataFilterClmn As String
  Dim DataFilter_Start_End As Variant
  Dim lColumn As Long
  Dim EffortCalColumn As Range
  'Dim EffortCalNo As String
  Dim PrjEffortCalColumn As String
  Dim NonPrjEffortCalColumn As String
  Dim LastColumnNo As Integer
  Dim PrevSheetLastRowNo As Integer
  Dim LastRowNo As Integer
  Dim PlotSheet As String
  Dim NewSheetNme As String

  On Error Resume Next
  Workbooks(Filename).Close SaveChanges:=False
   
  Application.DisplayAlerts = False
   
  If (SrcFilename = "") Then
  MsgBox ("Source File no selected. Click on Select Source button for selecting the source file")
  Exit Sub
  End If
   
  Set wkbkMacros = ActiveWorkbook
  Set wkbkSource = Workbooks.Open(SrcFilename)
  'On Error Resume Next
   
  NewSheetNme = "Effort_Summ"
   
  DataFilter_Start_End = Split(Trim(Application.WorksheetFunction.VLookup("Data Filter", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False)), ",")

  EffortCalColumn = Trim(Application.WorksheetFunction.VLookup("Total Effort Capped Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False))
  PrjEffortCalColumn = Trim(Application.WorksheetFunction.VLookup("Project Effort Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False))
  NonPrjEffortCalColumn = Trim(Application.WorksheetFunction.VLookup("Non-Project Effort Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False))
  DataFilterClmn = ColNumToLetter(Trim(Application.WorksheetFunction.VLookup("Data Filter Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False)))

  EffortCalColumn = EffortCalColumn & ":" & EffortCalColumn
  PrjEffortCalColumn = PrjEffortCalColumn & ":" & PrjEffortCalColumn
  NonPrjEffortCalColumn = NonPrjEffortCalColumn & ":" & NonPrjEffortCalColumn
  DataFilterClmn = DataFilterClmn & ":" & DataFilterClmn

   
  'Add a new Worksheet
  Set WSNew = wkbkSource.Worksheets.Add(After:=Sheets(Worksheets.Count))
  WSNew.Name = NewSheetNme
   
  wkbkSource.Sheets(NewSheetNme).Activate
   
  wkbkSource.Sheets(NewSheetNme).Cells(1, 1) = "Section"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 1).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 1).WrapText = True
   
  wkbkSource.Sheets(NewSheetNme).Cells(1, 2) = "Sum of Project Effort capped"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 2).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 2).WrapText = True
   
  wkbkSource.Sheets(NewSheetNme).Cells(1, 3) = "Sum of NonProject Effort capped"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 3).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 3).WrapText = True
   
  wkbkSource.Sheets(NewSheetNme).Cells(1, 4) = "Effort Person month"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 4).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 4).WrapText = True
   
  wkbkSource.Sheets(NewSheetNme).Cells(1, 5) = "%"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 5).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 5).WrapText = True
   
  wkbkSource.SaveChanges = True
  LastColumnNo = 2
   
  For y = LBound(DataFilter_Start_End) To UBound(DataFilter_Start_End)
   
  'LastColumnNo = LastCol(wkbkSource.Sheets(NewSheetNme))
  'LastRowNo = LastRow(wkbkSource.Sheets(NewSheetNme))
   
  DataSheet = CStr(Trim(DataFilter_Start_End(y)))
   
  WSNew.Cells(LastColumnNo, 1) = DataSheet
  WSNew.Cells(LastColumnNo, 1).Formula = '"=SUMIF("&DataFilterClmn&","&DataSheet&","&NonPrjEffortCalColumn&")"'   
  'WSNew.Cells(LastColumnNo, 1 + 1) = Application.WorksheetFunction.SumIf(wkbkMacros.Sheets(SrcSheetName)!DataFilterClmn, DataSheet, PrjEffortCalColumn)
  'WSNew.Cells(LastColumnNo, 1 + 2) = Application.WorksheetFunction.SumIf(wkbkMacros.Sheets(SrcSheetName)!DataFilterClmn, DataSheet, NonPrjEffortCalColumn)
  'WSNew.Cells(LastColumnNo, 1 + 3) = Application.WorksheetFunction.SumIf(wkbkMacros.Sheets(SrcSheetName)!DataFilterClmn, DataSheet, EffortCalColumn)
  LastColumnNo = LastColumnNo + 1
  Next
   
  'wkbkSource.Close SaveChanges:=True
   
End Sub

[\Code]
 
Hi ,

I cannot understand how DataFilterClmn can be 10 ; when the SUMIF formula is constructed , it has to have two column references and one criterion reference.

Going by the data you have posted , the final SUMIF formula will be :

=SUMIF(10:10 , NewSheet , R:R)

This SUMIF formula will not return a correct result.

If you can upload your complete workbook , we can save a lot of time , going back and forth like this.

Narayan
 
Hello,

I will convert value 10 to its corresponding column in excel using ColNumToLetter function.

So final formula will be looks like below

=SUMIF(J:J , NewSheet , R:R)

Reference value will be keep changing based on worksheet. For eg. first time it will J & R (case 1) and next time it will be B & T (case 2). I would like to know whether the range can be passed dynamically to the SUMIF function.

(case 1) =SUMIF(J:J , NewSheet , R:R)
(case 2) =SUMIF(B:B , NewSheet , T:T)
 
Hi ,

Isn't that what you are doing in the code as it is at present ?

By varying the contents of the variables , the SUMIF formula is changing ; what else are you looking for ?

Narayan
 
Hi ,

Is the text NewSheet a named range or a VBA variable or just text ? If it is just text then it should be enclosed in double quotes , so that the SUMIF formula should be :

=SUMIF(J:J , "NewSheet" , R:R)

Narayan
 
Hi,

Yes i had done the same way. When i run the code nothing is updated in the cells. While browsing in internet they had requested to use INDIRECT but still it's not working.

Latest code:

Code: 
Sub Effort_Summary_Run()
   
  Dim WSNew As Worksheet
  Dim sheetName As String
  Dim DataFilter As String
  Dim DataFilterClmn As String
  Dim DataFilter_Start_End As Variant
  Dim EffortCalColumn As String
  Dim EffortCalNo As String
  Dim PrjEffortCalColumn As String
  Dim NonPrjEffortCalColumn As String
  Dim LastColumnNo As Integer
  Dim PrevSheetLastRowNo As Integer
  Dim LastRowNo As Integer
  Dim PlotSheet As String
  Dim NewSheetNme As String
    
  Dim wkbkSource As Workbook
  Dim wkbkMacros As Workbook
  Dim SrcSheetRowCnt As Integer
  Dim wkbhSheet As Sheets
   
  Dim nStart As Integer
  Dim nEnd As Integer
   
  On Error Resume Next
  wkbkSource.Close SaveChanges:=False
   
  If (SrcFilename = "") Then
  MsgBox ("Source File no selected. Click on Select Source button for selecting the source file")
  Exit Sub
  End If
   
  Set wkbkMacros = ActiveWorkbook
   
  If (IsWorkBookOpen(SrcFilename) = False) Then
  Set wkbkSource = Workbooks.Open(SrcFilename)
  End If
   
  NewSheetNme = "Effort_Summ"
   
  If (WorksheetExists(NewSheetNme, wkbkSource)) Then
  Call DeleteSheet(NewSheetNme, wkbkSource)
  End If
   
  'Add a new Worksheet
  Set WSNew = wkbkSource.Worksheets.Add(After:=Sheets(Worksheets.Count))
  WSNew.Name = NewSheetNme
  wkbkSource.Sheets(NewSheetNme).Activate
   
  DataFilter_Start_End = Split(Trim(Application.WorksheetFunction.VLookup("Data Filter", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False)), ",")  
  EffortCalColumn = Trim(Application.WorksheetFunction.VLookup("Total Effort Capped Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False))
  PrjEffortCalColumn = Trim(Application.WorksheetFunction.VLookup("Project Effort Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False))
  NonPrjEffortCalColumn = Trim(Application.WorksheetFunction.VLookup("Non-Project Effort Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False))
  DataFilterClmn = ColNumToLetter(Trim(Application.WorksheetFunction.VLookup("Data Filter Column", wkbkMacros.Worksheets("Lookup").Range("B:C"), 2, False)))
   
  SrcSheetRowCnt = wkbkSource.Sheets(SrcSheetName).Range("A" & Rows.Count).End(xlUp).Row
   
  wkbkSource.Sheets(NewSheetNme).Cells(1, 1) = "Section"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 1).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 1).WrapText = True

  wkbkSource.Sheets(NewSheetNme).Cells(1, 2) = "Sum of Project Effort capped"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 2).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 2).WrapText = True

  wkbkSource.Sheets(NewSheetNme).Cells(1, 3) = "Sum of NonProject Effort capped"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 3).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 3).WrapText = True

  wkbkSource.Sheets(NewSheetNme).Cells(1, 4) = "Effort Person month"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 4).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 4).WrapText = True

  wkbkSource.Sheets(NewSheetNme).Cells(1, 5) = "%"
  wkbkSource.Sheets(NewSheetNme).Cells(1, 5).ShrinkToFit = True
  wkbkSource.Sheets(NewSheetNme).Cells(1, 5).WrapText = True

  wkbkSource.SaveChanges = True
  LastColumnNo = 2

  nStart = LBound(DataFilter_Start_End)
  nEnd = UBound(DataFilter_Start_End)
   
  Set wkbhSheet = wkbkMacros.Sheets(SrcSheetName)
   
  For y = nStart To nEnd

     DataSheet = CStr(Trim(DataFilter_Start_End(y)))

  WSNew.Cells(LastColumnNo, 1) = DataSheet   
   
  Set DataFlt = INDIRECT("'" & wkbhSheet & "'!" & DataFilterClmn & ":" & DataFilterClmn)
  Set PrjEffort = INDIRECT("'" & wkbhSheet & "'!" & PrjEffortCalColumn & ":" & PrjEffortCalColumn)
   
  WSNew.Cells(LastColumnNo, 1 + 1) = Application.WorksheetFunction.SumIf(DataFlt, DataSheet, PrjEffort)
   
  LastColumnNo = LastColumnNo + 1
  Next
   
  wkbkSource.Close SaveChanges:=True
End Sub
 
Hi ,

Sorry , but I cannot spend any more time on this back and forth testing ; if you can upload your workbook , it may help.

Narayan
 
Hi ,

You need to upload the source file too , without which the macro in question cannot be executed.

The uploaded file does not have anything except the macro code ; in what way is this an addition to the code which you have already posted here earlier ?

Narayan
 
Hi ,

It is still not clear. The cell C4 in the workbook Report.xlsm has the text :

ECG, ECG3

The variable DataSheet contains the text ECG.

This text is not to be found in the workbook Input_Sheet.xlsx ; what is to be done ?

Can you manually post the worksheet formula which you want to implement in the code ?

Narayan
 
Hi,

I want below formula to be used in VBA dynamically. Here J and P range columns will vary based on input in LookUp sheet.

=SUMIF(EmployeeSummary!J:J, "LOP", EmployeeSummary!P: P)

Any text can be inputed, if the text is not there then count should be zero (0).
 
Hi,

Thanks a lot for your help. It works fine :-)

When i try to use COUNTIF in similar way its not working. Nothing is printed int he cell and even formula is not displayed in the worksheet.

Code: 
WSNew.Cells(LastColumnNo, 1 + 5).Formula = "=CountIf(" & "'" & SrcSheetName & "'" & "!" & DataFilterClmn & "," & """" & DataSheet & ")"

Do i need to change anything here?
 
Hi ,

While the SUMIF function takes in 3 parameters , the COUNTIF function has only 2 parameters ; you need to cut down the code accordingly , probably on the lines of :

WSNew.Cells(LastColumnNo, 1 + 1).Formula = "=CountIf(" & "'" & SrcSheetName & "'" & "!" & DataFilterClmn & "," & """" & DataSheet & """" & "," & "'" & SrcSheetName & "'" & "!" & PrjEffortCalColumn & ")"

The highlighted portion needs to be removed.

Narayan
 
You must log in or register to reply here.
Back
Top