Adding Worksheets -- full techniques

Adding worksheets to all extent..

http://www.ozgrid.com/VBA/add-worksheet.htm
 

Material

- http://www.cpearson.com/Excel/Duplicates.aspx

Found it helpful, it is very good website,as it has explanation and live examples.

- https://www.techonthenet.com/excel/formulas/if_nested.php
Good for handson, has examples.

Macro to Compare columns in two excel files and paste the few columns in 2nd sheet for selected records.

'Macro to search for new project ids and change in current phase of a existing project - mark the 'changes in new sheet. Paste this macro in the new sheet.

 ' Copy this macro in the sheet "FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm" and run the macro, make sure two files are open while running.

Sub newprojectsearchandcurrentphase()

Dim i, j, L, N, LN, flag, i1, j1

 ' A1 is to store the values of column A(Project id) from the old sheet

Dim A1

' E is to store the value of column E(Master Project id) from the old sheet

Dim E

 ' Merging A1 and E values

Dim A1E

' A2 is to store the values of column A(Project id) from the new sheet

Dim A2

' F is to store the value of column F(Master Project id) from the new sheet

Dim F

 ' Merging A2 and F values

Dim A2F

 

 

 'new sheet

file1 = "FCCT Project List_10-24.xlsx"

Workbooks(file1).Sheets(1).Activate

ActiveWorkbook.ResetColors

 

For i = 2 To 571

 A1 = Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 1).Value

 F = Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 6).Value

 flag = 0

A1F = A1 & F

 'old sheet

Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Activate

 

 For j = 12 To 579

 A2 = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Cells(j, 1).Value

 E = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Cells(j, 5).Value

 A2E = A2 & E

  'Check if project id & Master id matches with the records.

 If (A2E = A1F) Then

 flag = 1

  ' Current phase of the project in the new sheet is saved in i1

 i1 = Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 9).Value

  ' Current phase of the project in the old sheet is saved in j1

 j1 = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Cells(j, 10).Value

 

  ' Copy the content of Release, Req, Test Plan, Test Lab and Defects from old sheet and place in new sheet

 Set shtToCopy = Workbooks("FCCT Project List 10-9_QC Compliance and QC Project status updates_10-22.xlsm").Sheets(1).Range("R" & j & ":Y" & j)

 shtToCopy.Copy Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Range("R" & i & ":Y" & i)

 

  'Mark the change in current phase for the projects that match

 If (i1 <> j1) Then

  Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Cells(i, 9).Interior.ColorIndex = 10

 End If

 Exit For

 Else

 flag = 2

 End If

 Next

 

 'Coloring the cells that does not have matching in the old report.

If flag = 2 Then

Workbooks("FCCT Project List_10-24.xlsx").Sheets(1).Activate

'Sheets(1).Cells(i, 1).ColorIndex = 3

Sheets(1).Cells(i, 1).Interior.ColorIndex = 10

End If

Next

 

End Sub

 

 

Copy Excel sheet or Excels columns from one sheet and paste in another sheet --- Open Status

'Copy excel sheets

Sub copy_sheets()
Dim eapp As Excel.Application    
Dim wkbk_from As Workbook    
Dim wkbk_to As Workbook    
Dim wksh As Worksheet    
Set eapp = CreateObject("Excel.Application")    

Set wkbk_from = eapp.Workbooks.Open("C:\Documents\Miscellaneous-DT\Excel\a.xlsx")    
Set wkbk_to = eapp.Workbooks.Open("C:\Documents\Miscellaneous-DT\Excel\b.xlsx") eapp.Visible = True    
For Each wksh In wkbk_from.Worksheets wksh.Copy After:=wkbk_to.Worksheets(Worksheets.Count)    
Next wksh
End Sub

'copy excel data in a sheet and place it in same sheet



Set shtToCopy = wkbSource.Sheets("qryQuickLookToExcel").UsedRangeshtToCopy.Copy wkbDest.Sheets(1).Range("A1")

Sort the Excel rows based on the values in a cell

- 9/30/2014


There are two ways to do this -

Option 1:

Sort rows by two or three criteria (columns)

For best results, the range you sort should have column labels, or headers.

1. Click a cell in the range you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by and Then by boxes, click the columns you want to sort, starting with the most important.
4. Select any other sort options you want, and then click OK.

Option 2:

a. Record macro by performing the activity or write your own code.

Below is the used to perform the sorting operation.

 'Sorting the rows
Sub Macro1()
'
' Macro1 Macro
'
'    'Select the range of cells
    Range("A2:K10").Select
    
     'clearing the sheet for any sorting that already exist
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
     '
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H2:H19") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:K19")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=-3
End Sub

Deatils of syntax of sort method are here:

https://msdn.microsoft.com/en-us/library/office/aa213557(v=office.11).aspx

Example

This example sorts the range A1:C20 on Sheet1, using cell A1 as the first sort key and cell B1 as the second sort key. The sort is done in ascending order by row, and there are no headers. This example assumes there is data in the range A1:C20.

Sub SortRange1()
   Worksheets("Sheet1").Range("A1:C20").Sort _
        Key1:=Worksheets("Sheet1").Range("A1"), _
        Key2:=Worksheets("Sheet1").Range("B1")
End Sub
  

This example sorts the region that contains cell A1 (the active region) on Sheet1, sorting by the data in the first column and automatically using a header row if one exists. This example assumes there is data in the active region, which includes cell A1. TheSort method determines the active region automatically.

Sub SortRange2()
   Worksheets("Sheet1").Range("A1").Sort _
        Key1:=Worksheets("Sheet1").Columns("A"), _
        Header:=xlGuess
End Sub

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

Understand: Split and UBOUND functions

 'Split - Returns a zero-based, one-dimensional array containing a specified number of substrings.
strString = Split(strString, "\")

 'UBound - Returns the highest available subscript for the indicated dimension of an array.
For i = 0 To UBound(strString) - 1
     strNewPath = strNewPath & strString(i) & "\"
Next


4/23/2014..
today I have found that there are lbound and Ubound functions, that will give you lower bound and upper bound values.

Understand Regex: "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}"

"(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}"

Here
( ) - is first priority
[ ] - is like range of values you give to the character for which you have this [ ] tagged
|    - OR Symbol
{ } - number of times you wanted to see that charater for which you have { } tagged to
[-/_] - here the range of values that you give to the character are -, /, _.
so here
00/01/1900  --taking first valid option from given set
--
--
--
12_31_2099  -taking last valid option from given set

VBscript to remove the string from a given text(ex: Path) and form a new path.

Sub split1()
 'Declaring the variables
Dim strString, strNewPath
strString = "c:\january\february\test.exe"

 'This helps to capture any error if gets created.
On Error Resume Next
strString = split(strString, "\")
 'Capture the Error message
If (Err.Number) Then
'MsgBox (Err.Description)
End If
 'UBound - Returns the highest available subscript for the indicated dimension of an array.
 'Navigating to each substring
For i = 0 To UBound(strString) - 1
  'Print the each substring
 MsgBox (strString(i))

strNewPath = strNewPath & strString(i) & "\"
  'MsgBox ("While breaking the string:" & strNewPath)
Next
 'MsgBox ("Without removing the string:" & strNewPath)
 'Since we have appended "\", trying to take Left part of the string with the length Len(strNewPath) -1
strNewPath = Left(strNewPath, Len(strNewPath) - 1)
'MsgBox ("After removing the string:" & strNewPath)
End Sub

Understanding formula '=SUM(('Defects Past SLA'!D3:D12="1 - Severe")*('Defects Past SLA'!P3:P12="One"))'

Understanding this formula: This formula is trying to count the number of cells with "1- Severe' and
"One" values.

=SUM(('Defects Past SLA'!D3:D12="1 - Severe")*('Defects Past SLA'!P3:P12="One"))

The above formula does not work if it is directly directly into a cell.

 

so here the formula needs to be entered with: CTRL+SHIFT+ENTER. This will allow to accept the range as an array for SUM Function.

 

As per the MSDN: http://support.microsoft.com/kb/214153

 

How to Count the Occurrences of a Number

Method 1

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

When you invoke the SUM function in this way, you have to "array enter" it (since it is using arrays and is by nature, not an array function). To array enter it, hold Ctrl+Shift and then press Enter after typing in the formula.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range, Number)

 

I posted this question in Stackoverflow.com, here is link for the responses i got. http://stackoverflow.com/questions/22653362/could-you-please-help-me-understand-the-excel-formula

Err: Activex component cannot create object

Hi, when i am trying to create object that is used to connect QC using vbscript.

I am getting err: Activex component cannot create object.

Extracting Release module structure from QC and displaying it

Extracting Requirements folder structure from QC and displaying it in similar fashion

Creating tree structure in excel userform using vbscript

Comparing text using Regular expression using vbscript

http://msdn.microsoft.com/en-us/library/ee236359%28v=vs.84%29.aspx

http://stackoverflow.com/questions/21800742/regular-expression-using-vbscript-for-starting-characters/21801104?noredirect=1#21801104

Symbols:
! =
^   = this is carat operator to identify beginning of sting. Ex: "^a" , Ex: "^(AET)". Only match the beginning of a string 'AET' here.
*   = Matches one or more times. Ex: ".*" ..matches any character any number of times.
.    = Matches any characters
()  = this is useful to combine operations
""  = matches any character between " ". Any characters b/w "", will check if that chars are present in string.
|  =This is OR operator
? =  Matches the previous element one or more time
[] = You can create a list of matching characters by enclosing one or more individual characters in brackets [ ]. Match any one character enclosed in the character set.
{} = Match exactly x occurrences of a regular expression.
\  = matches the above character in the string, to make them not do there operations. Ex: string= "AET*" , to match this "*" in string, pattern is ".*(\*)"
$ = Only match the ending of a string. "t$". Ex: "Tight"
\b = Matches any word boundary. "ly\b" matches "ly" in "possibly tomorrow."
\B = Matches any non-word boundary.
[^xyz]   - Matches any one character not in the set. Both opening and closing brackets are required.
Example "[^abc]" matches the "p" in "plain
[^a-z]  - Matches any character not in the specified range.
Example "[^m-z]" matches any character not in the range of "m" through "z".

Quantifier	Explicit quantifier	Meaning
*	{0,}	Matches the previous element zero or more times.
+	{1,}	Matches the previous element one or more times.
?	{0,1}	Matches the previous element zero or one time.

Method:
-Execute()
-Matches()


' Code for regular expression
Sub validatingtext()
Set RegularExpressionObject = CreateObject("vbscript.regexp")

src = "AET_P1s_ dfd"
RegularExpressionObject.IgnoreCase = True
RegularExpressionObject.Global = True
RegularExpressionObject.Pattern = "^(AET|BUS)_.*_.*"
'RegularExpressionObject.Execute (src)
Set Matches = RegularExpressionObject.Execute(src)
If (Matches.Count <> 0) Then
MsgBox ("True")
Else
MsgBox ("False")
End If

End Sub

Working on two or more Excel files using macro

Application.Workbooks("<Excel_Name1>").Worksheets(1).Range("A1").Value = "Hello"


http://www.excel-easy.com/vba/create-a-macro.html

Open a workbook(excel) file on your desktop using vbscript

Sub Button4_Click()
 file_Name = Application.GetOpenFilename() '-- to show the pop up window to find the file  and then it captures the filename of selected file when clicked ok.

' or file_Name= Application.GetOpenFilename(, , Title:="Select the Release module report") '-- to 'show the pop up window(with the text on it) to find the file and open it
set owb=Application.Workbooks.Open(file_Name) '-- it will really open the file. GetOpenFileanme - does not open the file
 MsgBox (file_Name) ' file_Name has full file path

End Sub

Row Count and Column Count from Excel

Finding the last Row or Coumn:
Solution 1:
Process 1: (2/9/2014)

lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
MsgBox (lastrow) 'Total number of rows(which has data) in Excel.
lastcolumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
MsgBox(lastcolumn) ' gives total number of columns(which has data) in Excel.


4/7/15:

lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row ' this gives the row number

' Lets suppose you applied filter, i found 2 records(row 2 and row 36) after applying filter, this code is giving me lastrow values as 36. so this formula cannot be used for counting rows.


5/7/15:
Solution 2:
'Get the Number of Rows used in the Excel sheetRowCount = ExcelObject.ActiveWorkbook.Sheets(1).UsedRange.Rows.count
msgbox "Number of rows used in the excel sheet "& RowCount

'Get the Number of Columns used in the Excel sheet
ColumnCount = ExcelObject.ActiveWorkbook.Sheets(1).UsedRange.Columns.count
msgbox "Number of columns used in the excel sheet "&ColumnCount

source: http://mahimavbscript.blogspot.com/2010/09/working-with-excel-object.html

Note: UsedRange sometimes gives disguising results, need to find out why.

Solution 3:

Selection method plays a major role here.


Ex:
        Workbooks(Filename).Sheets(Director).Range("B25").Select 'Dont know why not select
     
        Workbooks(Filename).Sheets(Director).Range(Selection, Selection.End(xlDown)).Select
        Workbooks(Filename).Sheets(Director).Range(Selection, Selection.End(xlToRight)).Select

Creating Table from Excel Data

Sub Table()
Dim r, a, rfr, d, c, o, otr, Row, i
Rowscount = Sheet1.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Row

Columnscount = Sheet1.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
r = 0
a = 0
d = 0
rfr = 0
c = 0
o = 0
otr = otr + 1


For Row = 2 To Rowscount

'If ((Sheet1.Range("E" & Row).Value = "Assigned" Or "Ready For Retest" Or "Deferred" Or "Open")) Then

i = Sheet1.Range("E" & Row).Value

Select Case i

          Case "Resolved"

              r = r + 1

          Case "Open"
              o = o + 1
          Case "Assigned"
              a = a + 1
          Case "Ready For Retest"
              rfr = rfr + 1
          Case "Cancelled"
              c = c + 1
          Case "Deferred"
              d = d + 1
          Case Else
               otr = otr + 1

End Select

Next
MsgBox (a)
 Sheet2.Range("K17").Value = a
Sheet2.Range("k18").Value = d
Sheet2.Range("k19").Value = o
Sheet2.Range("k20").Value = rfr
Sheet2.Range("k21").Value = r
Sheet2.Range("k22").Value = otr
End Sub