Posts by Kieran

Sub InputMsgOn()
    Dim ws As Worksheet
    Dim rng As Range
    Dim c As Range
    For Each ws In ActiveWorkbook.Worksheets
    On Error GoTo nocells
        Set rng = ws.Cells.SpecialCells(xlCellTypeAllValidation)
        For Each c In rng
            On Error Resume Next
            c.Validation.ShowInput = True
        Next c
nocells:
        On Error Resume Next
    Next ws
End Sub
 
 
Sub InputMsgOff()
    Dim ws As Worksheet
    Dim rng As Range
    Dim c As Range
    For Each ws In ActiveWorkbook.Worksheets
    On Error GoTo nocells
        Set rng = ws.Cells.SpecialCells(xlCellTypeAllValidation)
            For Each c In rng
                On Error Resume Next
                c.Validation.ShowInput = False
            Next c
nocells:
        On Error Resume Next
    Next ws
End Sub

Dim sh As Worksheet
Dim iLoop As Integer
For iLoop = 7 To 1719
     
     ' this is where the invoices are in an excel sheet
     
    iloopoffset = iLoop - 6
     
     ' as you see above, the list of invoices starts at line 7, so I used this to offset
     
    If Range("K6").Offset(iloopoffset).Value = "No" Then
         
         ' Column K is the one saying if the invoice was found or not in the branches file
         
        Set searchedvalue = Range("B6").Offset(iloopoffset, 0)
         
         ' I used this so i could use the value in the .find formula
         
        MsgBox (searchedvalue.Value)
        Workbooks.Open ("C:\Users\xxxxxx\Documents\xxxxxx\XML " + Range("D6").Offset(iloopoffset).Value)
        For Each sh In Worksheets
        'changed activesheet.name to sh.name
            If sh.Name = "062015" Or "052015" Or "042015" Or "032015" Or "022015" Or "012015" Or "122014" Or "112014" Then
                 
                 ' I needed to do this because on the sheets with the names above, the searched value will be in another column. sheets before 112014 are different.
                 
                'Set NFE = Worksheets(sh.Name).Range("B:B").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart)
                ' was using the worksheet find method - not the range find method.
                With sh.Name.Range("B:B")
                    Set NFE = .Find(Range("B6").Offset(iloopoffset, 0).Value, LookIn:=xlValues, lookat:=xlPart)
                        If Not NFE Is Nothing Then
                            firstAddress = c.Address
                            Do
                                Set NFE = .FindNext(c)
                            Loop While Not NFE Is Nothing And c.Address <> firstAddress
                        End If
                End With
                
            Else
                'Set NFE = Worksheets(sh.Name).Range("A:A").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart)
                 ' was using the worksheet find method - not the range find method.
                With sh.Name.Range("A:A")
                    Set NFE = .Find(Range("B6").Offset(iloopoffset, 0).Value, LookIn:=xlValues, lookat:=xlPart)
                        If Not NFE Is Nothing Then
                            firstAddress = c.Address
                            Do
                                Set NFE = .FindNext(c)
                            Loop While Not NFE Is Nothing And c.Address <> firstAddress
                        End If
                End With
            End If
             
                     
            If Not NFE Is Nothing Then
                'changed activesheet.name to sh.name
                MsgBox ("Found on sheet " + sh.Name + " " + NFE.Address)
                Range(NFE.Address).Offset(, 12).Value = "YES"
                 
                 ' yes for found
                 
                ActiveWorkbook.Save  ' this is done outside the "For Each sh In Worksheets" loop - do you really want it here?
                ActiveWindow.Close   ' this is done outside the "For Each sh In Worksheets" loop - do you really want it here?
            End If
        Next sh
        ActiveWorkbook.Save
        ActiveWindow.Close
    End If
Next iLoop
End Sub

Function AutoFilter_Criteria(Header As Range) As String
     'On  Error GoTo em
    Dim strCri1 As String, strCri2 As String
     
    Application.Volatile
     
    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)
            If Not .On Then Exit Function
            strCri1 = .Criteria1
            If .Operator = xlAnd Then
                strCri2 = " AND " & .Criteria2
            ElseIf .Operator = xlOr Then
                strCri2 = " OR " & .Criteria2
            End If
        End With
    End With
     
     
    AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
    Call rowcountnoblanks
End Function
 
Function rowcountnoblanks()
     'Application. ScreenUpdating = False
     
     'Range("A3").Select
    Range("A3").Activate
     
    Dim x As Object
    Dim r As Integer, c As Integer
     
    Set x = ActiveCell
     
    r = x.Row
    c = x.Column
    r = 3
    c = 1
     
    Dim i As Integer
    Dim totalrows As Integer
    Dim flag As Boolean
    flag = False
    i = 1
    Do Until flag = True
        If Cells(r + i, c).Value = "" And Rows(r + i).EntireRow.Visible Then
            flag = True
            totalrows = totalrows + 1
        Else
            i = i + 1
        End If
     Loop
     
     
     'Application.ScreenUpdating = True
     MsgBox totalrows ' result for purchasing manager is 30 however it actually should be 13 -- it appears to be counting the results that are not present when the filters applied
     
     
End Function