none
Excel 2010 VBA - Countifs formula error with dynamic workbook name RRS feed

  • Question

  • Hello, I'm creating a macro for users who will run the macro to identify if an invoice has been offset or not. They will run the macro on a file they had sent to the client x number of days earlier and now want to know the status of the invoices on that file, including if an invoice has been offset and re-billed. I'm using a countif formula to do this with the expected result of yes if the criteria in the formula has been met or no if it hasn't.

    When I run the macro I keep getting #Value! as the result. In the formula is a reference to a daily file, since it is a daily file I set the path of the file with a dynamic date range earlier in the macro (see below)

    Dim Today As Date
    Today = Date
    Dim PrevBusDay As Date
    PrevBusDay = Date - 1
    Select Case Weekday(PrevBusDay)
        Case vbSunday
            PrevBusDay = PrevBusDay - 2
        Case vbSaturday
            PrevBusDay = PrevBusDay - 1
    End Select

    Set APODaily = Workbooks.Open("S:\3rd Party Co-broker\Invc_Status_Files\Third_Party_APO_" & Format(PrevBusDay, "yyyy-mm-dd") & ".xlsx")

    Here is the code for the countif formula:

    Range("M2").Formula = "=IF(COUNTIFS('APODaily'!$P:$P,K2,'APODaily'!$E:$E,D2,'APODaily'!$X:$X,Q2,'APODaily'!$AD:$AD,-S2)>0,""Yes"",""No"")"

    What am I doing wrong? I've verified the macro is looking at the correct columns. Am I entering too many apostrohe's?


    Lorac1969

    Friday, July 8, 2016 12:40 PM

All replies

  • What am I doing wrong?

    Am I entering too many apostrohe's?

    b) you can answer that question by yourself, make a new file, name a sheet APODaily and run just your line from above to write the formula. As you see no issue.

    a) Maybe it depends on the other code or the contents of the file.

    Andreas.

    Friday, July 8, 2016 12:53 PM
  • Hi Andreas,

    I appreciate the vote of confidence, but unfortunately it may be mis-placed. As you've probably guessed I'm a newbie at VBA code, teaching myself, I took a second look at the code and can't figure out the issue. I've attached the entire code. Would you please take a look at it and let me know if you find the issue?

    Sub THPInvcStatus()
    
    Dim MainWkbk As Workbook
    Dim APODaily As Workbook
    Dim Detail As Worksheet
    Dim rngC As Range
    
    Set MainWkbk = ActiveWorkbook
    Set Detail = MainWkbk.ActiveSheet
    
    Dim Today As Date
    Today = Date
    Dim PrevBusDay As Date
    PrevBusDay = Date - 1
    Select Case Weekday(PrevBusDay)
        Case vbSunday
            PrevBusDay = PrevBusDay - 2
        Case vbSaturday
            PrevBusDay = PrevBusDay - 1
    End Select
    
    Set APODaily = Workbooks.Open("S:\3rd Party Co-broker\Invc_Status_Files\Third_Party_APO_" & Format(PrevBusDay, "yyyy-mm-dd") & ".xlsx")
    
    Application.DisplayAlerts = False
    MainWkbk.Activate
    ActiveSheet.Name = "Detail"
    Sheets(1).Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    
    Set rngC = Cells.Find(what:="Payable", After:=ActiveCell, LookIn:=xlFormulas, _
        lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, searchformat:=False)
    ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=rngC.EntireColumn
    'NameRange
    Columns("M:M").Select
    Selection.EntireColumn.Insert
    Selection.EntireColumn.Insert
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Current AR Remaining"
    Range("N1").FormulaR1C1 = "Current AP Remaining"
    Columns("M:N").NumberFormat = "General"
    'Application.DisplayAlerts = True
    
    MainWkbk.Worksheets.Add(After:=Worksheets(1)).Name = "Lookup"
    Range("A1").Select
    
    APODaily.Activate
    ActiveSheet.Select
    If ActiveSheet.AutoFilterMode = True _
        Then ActiveSheet.AutoFilterMode = False
    Columns("AD:BC").Select
    Selection.Copy
    
    MainWkbk.Activate
    Range("A1").Select
    ActiveSheet.Paste
    Columns("B:U").Delete
    Columns("A:A").Copy
    Columns("G:G").Select
    ActiveSheet.Paste
    Detail.Activate
    Columns("M:M").EntireColumn.Insert
    Range("M1").FormulaR1C1 = "Offset Y/N"
    'Range("M2").FormulaR1C1 = "=IF(COUNTIFS('APODaily!$P:$P,K2,'APODaily!$E:$E,D2,'APODaily!$X:$X,Q2,'APODaily!$AD:$AD,-S2)>0,""Yes"",""No"")"
    Range("M2").Formula = "=IF(COUNTIFS('APODaily'!$P:$P,K2,'APODaily'!$E:$E,D2,'APODaily'!$X:$X,Q2,'APODaily'!$AD:$AD,-S2)>0,""Yes"",""No"")"
    'APODaily.Close
    'Application.DisplayAlerts = False
    
    MainWkbk.Activate
    Sheets(1).Select
    Run_SumIf
    
    Sheets("Lookup").Visible = Excel.XlSheetVisibility.xlSheetHidden
    
    MsgBox ("Done")
    
    
    Application.DisplayAlerts = True
    
    End Sub
    
    
    Public Function Run_SumIf()
    ActiveSheet.Select
    'AR Status
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Range1,Lookup!C2:C7,3,FALSE)"
    Selection.Copy
    Range("N2").Activate
    Range("N2:N" & ActiveSheet.UsedRange.Rows.Count).PasteSpecial (xlAll)
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    'ValOut
    'Columns("M:M").Select
        'Cells.Replace what:="#N/A", replacement:="Closed", lookat:=xlPart, _
            'searchorder:=xlByColumns, MatchCase:=False, searchformat:=False, _
            'ReplaceFormat:=False
    'AP Status
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Range1,Lookup!C2:C7,6,FALSE)"
    Selection.Copy
    Range("O2").Activate
    Range("O2:O" & ActiveSheet.UsedRange.Rows.Count).PasteSpecial (xlAll)
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    'ValOut
    Range("N2").Select
    'Columns("N:N").Select
        'Cells.Replace what:="#N/A", replacement:="Closed", lookat:=xlPart, _
            'searchorder:=xlByColumns, MatchCase:=False, searchformat:=False, _
            'ReplaceFormat:=False
    Range("A1").Select
    
    'Application display alert was part of original working code
    'Application.DisplayAlerts = False
    
    End Function
    Sub ValOut()
        Dim x, y As Integer
    ' Keyboard Shortcut: Ctrl+Shift+V
        
    On Error GoTo ErrHandler
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.SpecialCells(xlCellTypeVisible).Select
        Exit Sub
    ErrHandler:
        Application.CutCopyMode = False
        Selection.SpecialCells(xlCellTypeVisible).Select
        If Selection.Count = 1 Then
            ActiveCell.Value = ActiveCell.Value
        Else
            For Each Ecell In Selection
                Ecell.Value = Ecell.Value
            Next
        End If
    End Sub
    
    
    
    
    


    Lorac1969

    Friday, July 8, 2016 3:15 PM
  • Hint: Instead of posting a long-winding code:

    Some clever programmer once said something like
    "An effective way of debugging is to minimize your (failing) application as much as you can. In this process you will often find the reason for the error."

    So, make a copy of your program and with the copy, take off all code not needed and step through the few lines of code remaining until you can isolate the error.
    This is also useful if you want to post your code. Small and easy to overview.


    Best regards, George



    Friday, July 8, 2016 3:43 PM
  •  I'm a newbie at VBA code, teaching myself, I took a second look at the code and can't figure out the issue. I've attached the entire code.

    I can not run that code, because I don't have the files that this code opens.

    And I said also: Maybe it's in the contents in the file, means not in the code.

    I agree with George, do as he said and debug the code. If you don't know how to debug have a look here:
    http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training.htm

    BTW, I recommend not to use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

    Andreas.

    Friday, July 8, 2016 5:06 PM
  • Hi Lorac1969,

    you had mentioned that ,"In the formula is a reference to a daily file, since it is a daily file I set the path of the file with a dynamic date range earlier in the macro (see below)"

    there is no error in path.

    as other members suggested you did you try to debug this code and find the step that create this issue?

    if not please try to do it first. because we don't have your data and file. we are not able to debug it.

    if we try to do it using our demo data then also it will not produce the situation like you because we don't know which type of data contains by the file.

    so it is better if you try to debug and let us know with which step you are having an issue so that based on that we will again try to give you suggestion.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 11, 2016 4:59 AM
    Moderator