Posts

Showing posts from July, 2012

ElseIf Structure

Sub ElseIf_Structure()
Dim marks As Integer
marks = Application.InputBox("Give Marks")
If marks >= 33 And marks <= 50 Then
MsgBox "Third"
ElseIf marks > 50 And marks < 60 Then
MsgBox "Secend"
ElseIf marks >= 60 Then
MsgBox "First"
Else
MsgBox "Fail"
End If
End Sub

Multiple-line statements - Syntax

If condition Then
statements
ElseIf elseif_condition_1 Then
elseif_statements_1
ElseIf elseif_condition_n Then
elseif_statements_n
Else
else_statements
End If



If statement  ->  In case of a multiple-line syntax (as above), the first line should have only the  If statement. See below for single-line syntax.
condition  ->  an expression (could be numeric or string)  which evaluates to True or False (note: a Null condition is equated to False). It is necessary to specify a condition.
statements  -> one or more statements (block of code) get executed if the condition evaluates to True. If statements are not specified, then no code will be executed if the condition evaluates to True.
ElseIf  ->  this clause can be used (optionally) if you want to test for multiple conditions. It is necessary to specify elseif_condition if ElseIf is present. Any number of ElseIf and elseif_conditions can be present.
elseif_condition  [elseif_condition_1 … elseif_condition_n] ->  an expression (could be nu…

SpecialCells method to find Last Used Column in worksheet

Sub LastUsedColumn_SpecialCells_1()
Dim lastColumn As Integer

lastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

MsgBox lastColumn

End Sub



Sub LastUsedColumn_SpecialCells_2()
Dim lastColumn As Integer

lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column

MsgBox lastColumn

End Sub

UsedRange method to find number of used columns in a worksheet

Sub UsedColumns_UsedRange()
Dim usedColumns As Integer

usedColumns = ActiveSheet.UsedRange.Columns.Count

MsgBox usedColumns

End Sub

UsedRange method to find number of used rows in a worksheet

Sub UsedRows_UsedRange()
Dim usedRows As Long
usedRows = ActiveSheet.UsedRange.Rows.Count

MsgBox usedRows

End Sub

End(xlUp) method to determine Last Row with Data, in one column

Sub LastRowWithData_xlUp_1()
Dim lastRow As Long

lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

MsgBox lastRow

End Sub



Sub LastRowWithData_xlUp_2()
Dim lastRow As Long

lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

MsgBox lastRow

End Sub

largest value in range

Sub Largest()Dim rng As RangeDim maximum As DoubleSet rng  Sheet1.Range("A1:Z100")maximum Application.WorksheetFunction.Max(rng)MsgBox maximum End Sub

Smallest Value in Range

Sub Smallest()
Dim rng As Range
Dim Minimum As Double

Set rng = Sheet1.Range("A1:Z100")
Minimum = Application.WorksheetFunction.Min(rng) MsgBox Minimum End Sub

Remove Special Characters

Image
Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?"" {}[](),!`~\:;'._-=+&^%$<>|"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
    Next
    removeSpecial = sInput
End Function


Sub cleanAllText()
   Dim rngUsed As Range, rngCheck As Range
   Dim i As Long
'Change Column as per requirement like ("K:K")
   Set rngUsed = Range("J:J") 
   'Set rngUsed = Range(rngUsed, _
      ' rngUsed.SpecialCells(xlLastCell))
   Application.Calculation = xlCalculationManual
   For Each rngCheck In rngUsed.Cells
       If rngCheck.Formula <> "" Then
         If Left(rngCheck, 1) <> "=" Then
         rngCheck = removeSpecial(rngCheck.Value)
         End If
       End If
   Next rngCheck
End Sub

Rank If Passed

Image
Sub Marks_rank()
Dim i As Integer
Dim k As String
For i = 2 To Sheet1.Range("B1048576").End(xlUp).Row
k = Application.WorksheetFunction.Rank(Cells(i, "B"), Range("B:B"), 0)
If Cells(i, "C") <> "Failed" Then
Cells(i, "D") = k
End If
Next
End Sub

Lookup If Blank

Sub vlkP_blankfild()
Dim i As Long
Dim k As Integer
For i = 2 To Sheet2.Range("A1048576").End(xlUp).Row
k = Application.VLookup(Cells(i, "A"), Sheets("sheet1").Range("A1:C" & Sheet1.Range("A1048576").End(xlUp).Row), 3, 0)
If Cells(i, "D").Value = "" Then
Cells(i, "D") = k
End If
Next
End Sub

COUNTIFS

Sub contifs()
Dim i As Integer
For i = 11 To Sheet1.Range("F1048576").End(xlUp).Row
Cells(i, "G") = Application.CountIfs(Sheets("Sheet1").Range("A:A"), Sheets("Sheet1").Range("G9"), Sheets("Sheet1").Range("C:C"), Cells(i, "F"))
Cells(i, "H") = Application.CountIfs(Sheets("Sheet1").Range("B:B"), Sheets("Sheet1").Range("G9"), Sheets("Sheet1").Range("C:C"), Cells(i, "F"))
Next
End Sub

3) Table 1 and 2 in different books and different sheets.

Option Explicit
Sub VlookUpExampleDifferBooks()
'This example look up table in different book and sheet (TABLE 1 - ActiveSheet, TABLE 2 - Book1 and sheet1)
Dim rw As Long
For rw = 3 To 12
Cells(rw, 3) = Application.VLookup(Cells(rw, 2), Workbooks("Book2.xls").Sheets("Sheet1").Columns("B:C"), 2, False)
Next
End Sub

2) Table 1 and 2 in different sheets.

Option Explicit
Sub VlookUpExampleDifferSheets()
'This example look up table in different sheet (TABLE 1 - ActiveSheet, TABLE 2 - Sheet 2)
Dim rw As Long
For rw = 3 To 12
Cells(rw, 3) = Application.VLookup(Cells(rw, 2), Sheets("Sheet2").Columns("B:C"), 2, False)
Next
End Sub

1) Table 1 and 2 same sheet.

Option Explicit
Sub VlookUpExampleSameSheets()

'This example look up table inside same sheet as picture above (TABLE 1 & 2 SAME SHEET)
Dim rw As Long
For rw = 3 To 12
Cells(rw, 7) = Application.VLookup(Cells(rw, 6), ActiveSheet.Columns("B:C"), 2, False)
Next
End Sub

Unique Desgnation

Image
{=IFERROR(INDEX($C$2:$C$25,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$25),0)),"")}