Index/Match function

=INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))

finding circular references

1. If you can't find the error, click the Formulas tab, click the arrow next to Error Checking, point to Circular References, and then click the first cell listed in the submenu.
2. Review the formula in the cell.

how to work with the visible rows after applying autofilter

    ActiveSheet.Range("$A$1:$J$5").AutoFilter Field:=2, Criteria1:="3"
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Sheets("Email").Range("A30")

vlookup not working on cell with formula

even if a cell (the referencing cell) is formatted as general, if the formula within refers to another cell that is formatted as text, vlookup treats the referencing cell as text.

How to use the FORMAT Function with Dates (VBA)

How to use the FORMAT Function with Dates (VBA)

• Example
  • Dim LValue As String
  • LValue = Format(Date, "yyyy/mm/dd")
• Format ( expression, [ format, [ firstdayofweek, [firstweekofyear] ] ] )
• Format(#17/04/2004#, "Short Date")
  • Result: '17/04/2004'
• Format(#17/04/2004#, "Long Date")
  • Result: 'April 17, 2004'
• Format(#17/04/2004#, "yyyy/mm/dd")
  • Result: '2004/04/17'

get path of current workbook

get path of current workbook

• for just the path itself (without the workbook name)
  • Application.ActiveWorkbook.Path
• for the path with the workbook name
  • Application.ActiveWorkbook.FullName

CountIf

CountIf

• COUNTIF( range, criteria )
• =COUNTIF(A2:A7, D2)
• =COUNTIF(A:A, D2)
• =COUNTIF(A2:A7, ">=2001")
• You can also use a named range in the COUNTIF function
• =COUNTIF(family, D2)
• =COUNTIF(family, ">=2001")
• To use a cell reference in the criteria
  • =COUNTIF(C4:C19,">="&A1)

Loop through all workbooks

Loop through all workbooks

• Dim WS_Count as integer
• Dim I as integer
• WS_Count = Activeworkbook.Worksheets.Count
• For I = 1 to WS_Count
• Msgbox Activeworkbook.Worksheets(I).Name
• Next I