Friday, 23 September 2016

VBA Macro to Find All exact Matches of a String in a Specific Sheet in Excel

Excel VBA Macro to Search or FindAll exact Matches of String  in a Specific Sheet and get the Address of Cell,Column and Row details of each Match
Note: Please note that the Macro will Search/Find in the Used Range of the source data.

Sub FindStrAll()
Dim Srch_Result As Range
Dim LastCell As Range
Dim MyRng As Range
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim Srch_Str As String

Set Sht = ThisWorkbook.Sheets("Results")
Set Src_Sht = ThisWorkbook.Sheets("Data")
    
    Src_Sht.Activate
    Src_Sht.Cells.Select
    
Set LastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Set MyRng = Src_Sht.Range("$A$1:" & LastCell.Address)

 X = 2
 Y = 2
 Z = 0

 Do Until Sht.Range("A" & Y).Value = ""
    Srch_Str = Sht.Range("A" & Y).Value
    Str_Cnt = Application.WorksheetFunction.CountIf(MyRng, Srch_Str)

'Set Srch_Result = Selection.Find(What:=Srch_Str, After:=LastCell)
 Set Srch_Result = Selection.Find(What:=Srch_Str, After:=LastCell, _
                    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Srch_Result Is Nothing Then
        MsgBox "Search Item Not Found in Source Data", vbOKOnly, "Search Completed"
    Else

        Do While Not Srch_Result Is Nothing
           Srch_Result.Activate 'Activating the search result in source data sheet
           Z = Z + 1
               Sht.Range("A" & X).Offset(0, 1).Value = ActiveCell.Value
               Sht.Range("A" & X).Offset(0, 2).Value = ActiveCell.Address
               Sht.Range("A" & X).Offset(0, 3).Value = Cells(1, ActiveCell.Column).Value
               Sht.Range("A" & X).Offset(0, 4).Value = ActiveCell.Column
               Sht.Range("A" & X).Offset(0, 5).Value = ActiveCell.Row
           Set Srch_Result = Selection.FindNext(After:=ActiveCell)
           
           X = X + 1
           If Z = Str_Cnt Then Exit Do
        Loop

        Z = 0    
    End If

    Y = Y + 1   'Increment of Search Strings range variable
  Loop

Sht.Activate
Sht.Range("A1").Select

Set Srch_Result = Nothing
Set Sht = Nothing
Set Src_Sht = Nothing
Set LastCell = Nothing
Set MyRng = Nothing

End Sub
-----------------------------------------------------------------------------------------------------------------------
Example: 
Suppose the we have the Source data as follows where we want to search/find a string as follows:

The Output of the Macro is as follows:

Thanks,
TAMATAM

Thursday, 22 September 2016

VBA Macro to Find or Search a String in a Specific Sheet in Excel

Excel VBA Macro to Find or Search a String  in a Specific Sheet and get the Address of Cell,Column and Row details
Please note that Search string should be unique in the source data and the below macro returns the first find result details only, though multiple occurrences find in source.
Sub FindStr()
Dim Srch_Result As Range
Dim LastCell as range
Set Sht = ThisWorkbook.Sheets("CPanel")
Set Src_Sht = ThisWorkbook.Sheets("Data")

Srch_Str = Sht.Range("A2").Value
    
    Src_Sht.Select
    Cells.Select

 Set LastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)   
Set Srch_Result = Selection.Find(What:=Srch_Str, After:=LastCell)
 Set Srch_Result =Selection.Find(What:=Srch_Str, After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
     xlNext, MatchCase:=False, SearchFormat:=False)
' Selection.FindNext(After:=ActiveCell).Activate

   If Not Srch_Result Is Nothing Then
   
        Srch_Result.Activate
        Sht.Range("A2").Offset(0, 1).Value = ActiveCell.Address
        Sht.Range("A2").Offset(0, 2).Value = Cells(1, ActiveCell.Column).Value
        Sht.Range("A2").Offset(0, 3).Value = ActiveCell.Column
        Sht.Range("A2").Offset(0, 4).Value = ActiveCell.Row
    
    Else
    
    MsgBox "Search Item Not Found", vbOKOnly, "Search Completed"
    ActiveSheet.Range("A1").Select
    
   End If
               Set Srch_Str = Nothing
               Set Sht = Nothing
               Set LastCell=Nothing
               Set Src_Sht = Nothing
 End Sub

Source Data , where we search a string : 

Macro output:

Thanks,
TAMATAM

How to Get the Cell Address of a Matching Column and Row in Excel

Excel Function to Get the Address of Matching Column and Row in Excel
Suppose we have the Field or Column names as follows in the source data tab as follows :


Now in our calculation sheet, if you want to capture the Address/Column Index Number/Name details of the lookup Fields/Column Names, we can do as follows :

Lets say the source data tab is "Data", and our lookup Fileds/Columns names in Column A which exists in Row 1 in source data tab, then now the formulas to get each item from source is as follows:
Field Address in Source:
 =(ADDRESS(1,MATCH(A2,Data!$A$1:$AH$1,0),1))

Field Row Number:
 =MID(B2,FIND("$",B2,2)+1,LEN(B2)-FIND("$",B2,2))

Field Column Number:
 =MATCH(A2,Data!$A$1:$AH$1,0)

Field Column Index Name:
 =LEFT(B2,FIND("$",B2,2)-1)

Thanks,
TAMATAM

Tuesday, 20 September 2016

VBA Switch Statement Syntax and Example

SWITCH statement in VBA:
The SWITCH statement in VBA evaluates a list of conditions and returns the value of the True/match condition from list.
Incase of False it will returns the NULL value, that we need to handle by If condition to perform the False case operation.
Syntax:
Switch ( Condition1,value1, Condition2,value2, ... ConditionN,valueN)
Here:
Condition1,Condition2...ConditionN are the conditions to be evaluate.
value1, value2, ... valueN are the values to be written if the corresponding condition is true.

Example:
In the below example we are checking the Range("A1") value and returing the Grade type in Range("B1") 

Sub SwitchStatement()
Dim XValue As String
Dim X As Integer
X = ActiveSheet.Range("A1").Value

 XVal = Switch((X > 75 Or X = 100), "A-Grade", (X >= 50 And X < 75), "B-Grade", (X < 50 And X >=35), "C-Grade")

If IsNull(XVal) = True Then
    ActiveSheet.Range("B1").Value = "Not Applicable"
Else
    ActiveSheet.Range("B1").Value = XVal
End If
End Sub

Thanks,
TAMATAM

Select Case Statement Syntax with Examples in VBA

VBA Select Case Statement Syntax with Examples :
The Select-Case statement Checks a Variable or an Expression for different Cases (values). If anyone of the Case becomes true then only that Case block is executed and the Program ignores all other Cases.

Syntax:
Select Case Condition/Variable
Case Val_1
Code to Execute When Condition = Val_1
Case Val_2
Code to Execute When Condition = Val_2
Case Val_3
Code to Execute When Condition = Val_3
Case Else
Code to Execute When all the other cases are False
End Select

Here:
 ‘Condition’ refers to the Variable or the Expression that is to be Tested and based on which anyone of the Case blocks will be executed.

'Val_1', 'Val_2' and 'Val_3' are the possible outcomes/values of the ‘Condition’. Whenever anyone of these values matches the ‘Condition’ then its corresponding Case block will execute.

'Else' is a kind of default Case value, which will only execute when all the above Case statements result into False. Else case is optional.

Example 1: Select Case Statement to Check a simple True or False Condition:
In the below example, we have supplied a condition (i.e. J=K) to the Select Case statement. If this is True then ‘Case True’ block will be executed and if it is False then ‘Case False’ block will execute.

Sub Case_Example1()  
'Enter the value for Input variables  
J= InputBox("Enter the value for J:")  
K = InputBox("Enter the value for K:")  
' Evaluating the expression  
Select Case J = K  
Case True  
  MsgBox "The expression is TRUE"  
Case False  
  MsgBox "The expressions is FALSE"  
End Select  
End Sub  

Example 2: Case statement to check Text Strings data:
In this example we will compare text strings in the Case statements. If a match is found then the corresponding Case block will execute otherwise the ‘Case Else’ block will execute.

Sub Case_Example2()  
'Enter the value for variable  Color_name
Color_name = InputBox("Enter the your favorite Color Name:")  
' Evaluating the expression  
Select Case Color_name
Case "Green"  
  MsgBox "You entered Color name as Green"  
Case "Blue"  
  MsgBox "You entered Color name as Blue"  
Case "Yellow"  
  MsgBox "You entered Color name as Yellow"    
Case Else  
  MsgBox "You entered Color name is not the in the Case"    
End Select  
End Sub  

Example 3: Case statement to Check Numbers:
In the below example we will check if the number entered by user is less than or greater than 10.

Sub Case_Example3()  
'Enter the value for  Input variable Num
Num = InputBox("Enter any Number between 1 to 20:")  
' Evaluating the expression  
Select Case Num  
Case Is < 10  
  MsgBox "The Number you entered is less than 10"  
Case Is = 10  
  MsgBox "The Number you entered is Equal to 10"  
Case Is > 10  
  MsgBox "The Number you entered is greater than 10"  
End Select  
End Sub  

Note: You can use IS keyword with Case Statement to compare values.


Example 4: Select Case statement to check multiple conditions inside a single case:
In this example we will enter any number from 1-10. and then we will check if the number is even or odd by using multiple conditions in the Case statement. Notice here I have used a “,” (comma) to check and compare multiple conditions in a single Case statement.

Sub Select_Case_Example4()  
'Enter the value for Input variable Num 
Num = InputBox("Enter any Number between 1 to 10:")  
'Evaluating the expression  
Select Case Num  
Case 2, 4, 6, 8, 10  
  MsgBox "Your Number is Even."  
Case 1, 3, 5, 7, 9  
  MsgBox "Your Number is Odd."  
Case Else  
  MsgBox "Your Number is out of the range."  
End Select  
End Sub  

Example 5: Case statement to check a continuous range as condition:
Here we will test a continuous range as a condition. We will enter any number between 1-10, if the number is between 1 to 5 (including both 1 and 5) then ‘Case 1 To 5’ will be ‘True’, if the number entered by the user is between 6 and 10 (including both 6 and 10) then ‘Case 6 To 10’ will be ‘True’, if both the previous cases are ‘False’ then ‘Case Else’ will be executed.

Sub Case_Example5()  
'Enter the value for Input variable Num 
Num = InputBox("Enter any Number between 1 to 10:")  
'Evaluating the expression  
Select Case Num  
Case 1 To 5  
  MsgBox "Your Number between 1 to 5"  
Case 6 To 10  
  MsgBox "Your Number between 6 to 10"  
Case Else  
  MsgBox "Your Number is out of the range."  
End Select 
End Sub  

Example 6 : Case Statement to to Check If a Variable or Range value exist in Multiple data ranges :
We can check if a Range A1 exist between 100 and 500 also between 501 and 1000 also between1001 and 1500 then execute the true Case statement as follows:

Sub Select_Case_Example6()
 X=Range("A1").Value
    Select Case X
        Case 100 To 500, 501 To 1000, 1001 To 1500
             Range("B1").Value =X
       Case Else
             Range("B1").Value = 0
     End Select
End Sub

Example 7 : Case Statement to to Check If a Variable or Range value exist in Multiple data ranges and mix of additional Numbers and Text values:
We can check if a Range A1 vale is [100 to 500] , 555 and [600 to 900],999 and [1001 to1500],2000 and "Product","Service" then execute the true Case statement as follows:

Sub Select_Case_Example7()
 X=Range("A1").Value
    Select Case X
           Case 100 To 500, 555,600 To 900, 999,1001 To 1500,2000,"Product", "Service"
                 Range("B1").Value = Range("A1").Value
           Case Else
                 Range("B1").Value = 0
     End Select
End Sub


Thanks,
TAMATAM

Saturday, 3 September 2016

How to Store the Formula result into a Variable in Excel VBA

Excel Formula to get the Workbook Path and store into a Variable in VBA
Sub Formula_Variable()
Dim SrcSht As Worksheet

Application.DisplayAlerts = False

    Set SrcSht = ThisWorkbook.Sheets("Src_Sht")

    F1 = Evaluate("=LEFT(CELL(""FileName""),FIND(""["",CELL(""FileName""),1)-1)")
    
    SrcSht.Range("C3").Value = F1

End Sub




Notes :
Here the Formula "=LEFT(CELL(""FileName""),FIND(""["",CELL(""FileName""),1)-1)" will returns the Active workbook path.

The Evaluate() Function returns the formula result.

Thursday, 1 September 2016

VBA Macro to Import the Data from Text file to Excel

VBA Macro to Import the Data from Text(.txt) file to Excel(.xls) and Save in Destination Folders
Sub Import_Txt_Data()

Dim Tgt_WB As Workbook
Dim SrcSht As Worksheet
Dim TgtSht As Worksheet

Application.DisplayAlerts = False

Set SrcSht = ThisWorkbook.Sheets("CPanel")

 Set Tgt_WB = Workbooks.Add(1)
 Set TgtSht = Tgt_WB.Sheets(1)

 TgtSht.Activate
 TgtSht.Range("A1").Select

 'ConStr = "TEXT;C:\Users\Tamatam\Desktop\Ad_hoc\Txt_Data.txt"
 ConStr = "TEXT;" & SrcSht.Range("C3").Value

    With TgtSht.QueryTables.Add(Connection:=ConStr _
        , Destination:=Range("$A$1"))
        .Name = "Data_Import"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
     '  .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    TgtSht.Range("A1").Select
   'Saving the Imported data as Excel[97-2003] format in Target Foders.
        For S = 3 To 6
            SavePath = SrcSht.Range("D" & S).Value & "\"
            Tgt_WB.SaveAs SavePath & "Txt_Data", 56
        Next S
            Tgt_WB.Close , True
            
 MsgBox "Data Imported from Source .txt File to XL[97-2003] and Saved in Destination  Folders", vbOKOnly, "Import Success"

    Application.DisplayAlerts = True
    Set SrcSht = Nothing

    Set Tgt_WB = Nothing
    Set TgtSht = Nothing
End Sub
---------------------------------------------------------------------------------------------------------------
Notes:
The main file formats in Excel 2007-2016:

51 = xlOpenXMLWorkbook (without Macros in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without Macros in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without Macros, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

We can use the File Format Numbers instead of the defined File Format Constants in the code so that it will compile OK when we copy the code into an Excel 97-2003 workbook. 

For example, Excel 97-2003 won't know what the File Format constant xlOpenXMLWorkbookMacroEnabled , but it understands File Format number 52.

In Excel for the Mac the Format Number values are +1 as follows :
xlsx = 52
xlsm = 53
xlsb = 51
xls = 57

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts