Use of Countif function with multiple criteria

Finding unique values from the list of values in column

Syntax finding unique values from the list:
SourceRange.AdvancedFilter Action:=xlFilterCopy, CopytoRange:=TargetRange, Unique:=True

Method:
Sub finduniquevalues()
Range("A2:A11").AdvancedFilter Action:=xlFilterCopy, CopytoRange:=Range("B2"), Unique:=True
End Sub

INDIRECT ---- Cell Value to Row Number

• Wanted to use the cell value as row number in one of the excel formulas.
• 3/7/17
• Use the INDIRECT function.
  
  If A1 holds 128, then
  
  =INDIRECT("C"&A1) will reference cell C128.
  
  Other ways...
  INDEX/MATCH
  OFFSET (e.g. =OFFSET(C1,A1-1,0) will work)
• Source: https://www.mrexcel.com/forum/excel-questions/56452-cell-value-row-number.html
• 
  

** When i tried to create dynamic formula i,e use variables in the function, i have come across INDIRECT function..

http://spreadsheets.about.com/od/tipsandfaqs/ss/2013-08-07-excel-sum-indirect-dynamic-range-formula_3.htm

OFFSET

** Moving positions from the existing cell, if you use activecell concept inorder to move next to activecell a row or a column use activecell.offset(-1,0) --this will move top row and same column.

ActiveCell.offset(1,0) ----this will make you move to next row and same column.

How to use formula in excel using vbscript

'Assigning formula to the cells

objExcel.Cells(6, 1).Formula = "=COUNTIF(A1:A4,'NK*')"  'this is how to use Countif function in the vbscript, here this will look for NK and count the number of cells that has NK.


i,e
strFormula = "=COUNTIF(A1:A4," & Chr(34) & "NK*" & Chr(34) & ")"
objExcel.Cells(6, 1).Formula = strFormula  'use .Formula, to assign formula to the cells 

Ref: http://blogs.technet.com/b/heyscriptingguy/archive/2006/07/13/how-can-i-add-a-countif-formula-to-an-excel-spreadsheet.aspx


'Assigning formula to the Range

Function1 = "=Countif(A1:A5,"hi")"    'here 'hi' is counted in each instance b/w A1 to A5

Sheets(1).Range("A1").FormulaLocal = Function1 'use .FormulaLocal, to assign formula to the Range of cells.



' Now make the formulas dynamic

One can use INDIRECT function along with main function.

Opening a file from Share point

'*****************************************************************************
'set filepath to sharepoint location
strRawFilePath = "\\teams.aexp.com\davwwwroot\sites\fcct\B2TMO\Shared Documents\QC Defect Extract Reports - Raw Extract\"
'set IPR filename
strIPRFileName = strRawFilePath & "IPR Defects " & myStr & ".xlsx"
MsgBox (strIPRFileName)
'open workbooks
Workbooks.Open (strIPRFileName)
   ' copy all data
    'Cells.Select
    'Selection.Copy
       

Display Calender

Step 1
Method:

Sub OpenCalendar()
    frmCalendar.Show  'here frmCalender - is the Userform name where in we have calender object created  in that userform(has some code too)
 

   ' MsgBox (frmCalendar.curDay)
 
End Sub


Step2
' This is frmCalender userform code

Sub UserForm_Initialize()
    If IsDate(ActiveCell.Value) Then
        Me.MonthView1.Value = ActiveCell.Value
    End If
End Sub
Sub MonthView1_DateClick(ByVal DateClicked As Date)
'Public cell, curDay
    On Error Resume Next
    Dim cell As Object
    For Each cell In Selection.Cells
        cell.Value = DateClicked
        MsgBox (cell.Value)
        curDay = cell.Value
       
        'curDay = Date - 2  ''here -1 or -2 will make you to pick ydays or day before ydays date.
'format currentday as filename is formatted
myStr = Format(curDay, "yyyymmdd")
'*****************************************************************************
'set filepath to sharepoint location
strRawFilePath = "\\teams.aexp.com\davwwwroot\sites\fcct\B2TMO\Shared Documents\QC Defect Extract Reports - Raw Extract\"
'set IPR filename
strIPRFileName = strRawFilePath & "IPR Defects " & myStr & ".xlsx"
MsgBox (strIPRFileName)
'open workbooks
Workbooks.Open (strIPRFileName)
   ' copy all data
    'Cells.Select
    'Selection.Copy
       
    Next cell
    Unload Me
End Sub