Excel Functions Library

Excel Functions Library

Good Website to understand basics of Excel Macro - 2

http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/

Autofilter error - Autofilter Method of Range class failed

How to handle error in AutoFilter Method while using Excel Macros -

Error - "autofilter method of range class failed"

Reasons -

because the rows might be blank

It's caused because your data is not laid out for AutoFilter. To use AutoFilter the data has to be in a table layout with a Header, with no empty rows or columns.

Solution-

select the complete range of values and then do selection.autofilter

How to Subtract the given no.of days from today

Sub Sub_dates()

x = InputBox("Enter the no.of days to be subtracted from today's date")
y = DateAdd("d", -x, Now)
z = Mid(y, 1, InStr(1, y, " ") - 1)

MsgBox z
End Sub

How to rename file in a directory

Sub rename_file()

Dim OldName, NewName

clearfilepath

SearchFile

Sheets("FilePath").Select

Range("A65536").Select

ro1 = Selection.End(xlUp).Row

For i = 2 To ro1

   

    OldName = Cells(i, 1).Value '"D:\Documents and Settings\XXXXXX\Devlopment\2010-3-30\chr0101.591"

    NewName = OldName & ".csv" ' Define file names.

    Name OldName As NewName ' Rename file.

Next i

MsgBox "Its done !!"

End Sub

Function SearchFile()

Set fs = Application.FileSearch

With fs

'‘this code automatically searches for files in desktop – Source File folder

    .LookIn = Environ("userprofile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "Source File" & Application.PathSeparator

    .FileType = msoFileTypeAllFiles

    If .Execute > 0 Then

        Sheets("FilePath").Select

        For i = 1 To .FoundFiles.Count

             Range("A" & (i + 1)).Value = .FoundFiles(i)

        Next i

    Else

        'MsgBox "There were no Binder files found."

    End If

End With

End Function

Function clearfilepath()

Sheets("FilePath").Select

Range("A2:A65536").Select

Selection.ClearContents

End Function

Good Website to understand basics of Excel Macro

I just came across this website and seems to be really good to understand macro from basics.

http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

Draw border lines around cells with common values

How to draw borders around cells with common values

Sub Draw_border_for_common_items_in_row()

Sheets("Top CTDs").Select

LC1 = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column

LR1 = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row

For i = 1 To LC1

temp = Cells(2, i).Value

ro = 2

    For j = 2 To LR1

        If Cells(j, i).Value <> temp Then

            Range(Cells(ro, i), Cells(j - 1, i)).Select

            Selection.BorderAround Weight:=xlThin

            ro = j

            temp = Cells(j, i).Value

           

        End If

       

    Next j

Next i

End Sub

Collating the CMS Avaya Raw Dump into consolidated raw data

Sub test()
Dim myDir As String, fn As String
myDir = "W:\XXXXXXXXXX\2010\Raw dump - avaya script\"
fn = Dir(myDir & "*.xls")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        With Workbooks.Open(myDir & fn)
            With .Sheets(1).Range("A1:IV200")
                ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp)(4) _
                .Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With
            .Close False
        End With
    End If
    fn = Dir
Loop
Rows("1:3").Select
Selection.Delete shift:=xlUp
Range("a1").Select
End Sub

How to edit CMS Avaya Script

Open the required CMS Avaya Script and Save the Script and then do the below modifications -

comment the line

b=Rep.Run

and add the below line

b=Rep.Exportdata("\\path\Raw dump - avaya script\255.xls",9,0,false,true,true) 

also if you wanna make the script more dynamic then add the below lines 

Date1 = Inputbox ("Please enter the Start date of the reports you wish to see in the format mm/dd/yyyy") 

Time1 = Inputbox ("Please enter the Start Time of the reports you wish to see in the format hh:mm") 

Date2 = Inputbox ("Please enter the END date of the reports you wish to see in the format mm/dd/yyyy") 

Time2 = Inputbox ("Please enter the END Time of the reports you wish to see in the format hh:mm") 

and now modify the Rep property section as shown below -

Rep.SetProperty "Splits/Skills","255" 'Skill number

Rep.SetProperty "Start Date",Date1 '-1 - Yday

Rep.SetProperty "Start Time",Time1 '"14:30"  - start time

Rep.SetProperty "Stop Date",Date2 '0 -today

Rep.SetProperty "Stop Time",Time2 '04:00 - end time

Finding the last row or column

How to find the last row or last column in any excel sheet

LR = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row

LC = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column

or 

LR= range("A65536").end(xlup).row

Lc=range("IV1").end(xltoleft).column