Wednesday, 24 December 2014

How to Find the Count of Instances or Occurrences of a Character or Substring in a String

Excel Function to Count the Number of Instances of Character or Substring in a String
In Excel we can find the Count of Instances or Occurrences of a Character or Substring in a String using the following formula :

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))

Example :

Suppose we have a String in a Cell "A1" which contains the character Semicolon  ";"

Now if we want to find how many Semicolons are there in that string , we can find using the below formula :

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))


Please observe the following examples for better understanding :


String
Count of Char/Sub String Formula
Result
ABC;DEF,@@;#@@;JKLM@@;DEF
 =LEN(A2)-LEN(SUBSTITUTE(A2,";",""))
4
ABC;DEF,@@;#@@;JKLM@@;DEF
 =LEN(A3)-LEN(SUBSTITUTE(A3,"@@","1"))
3
ABC;DEF,@@;#@@;JKLM@@;DEF
 =LEN(A4)-LEN(SUBSTITUTE(A4,"DEF","12"))
2
ABC;DEF,@@;#@@;JKLM@@;DEF
 =LEN(A5)-LEN(SUBSTITUTE(A5,"JKLM","123"))
1

Tip :
If you want to find the Instance of a Substring(Eg: ",") of length 1 character , we have to use No space ("") in above SUBSTITUTE Function.

In the Same way , If you want to find the Instance of a Substring(Eg: abc) of length 3 characters , we have to use 2 spaces("  ") or a string of length 2 characters(Eg: 12) in above SUBSTITUTE Function.

Similarly ,If you want to find the Instance of a Substring(Eg: abcd) of length 4 characters , we have to use 3 spaces("   ") or a string of length 3 characters(Eg: 123) in above SUBSTITUTE Function.

Thanks ,
TAMATAM




Tuesday, 9 December 2014

How to disable Cut,Copy,Paste and Delete Keys In Excel VBA

Excel VBA Application.Onkey to Disable Cut,Copy,Paste and Delete Keys
By using the Application.Onkey we can disable a particular Key or key combination or run a macro when you use a particular key or key combination. 

The Key argument can specify any single key combined with ALT, CTRL, or SHIFT, or any combination of these keys. Each key is represented by one or more characters, such as the  "^ {c}" for Key Combination "Ctrl+C", or "{TAB}" for the TAB key etc.

You can assigns "YourMacroName" to the key sequence CTRL+SHIFT+ENTER , as follows
Application.OnKey "^+{ENTER}", "YourMacroName"

Symbols used in Key Combinations:

Shift key = "+" (plus sign)
Ctrl key = "^" (caret)

Alt key = "%" (percent sign)

Example-I :
The following Macro is used to disable the Cut,Copy,Paste and Delete Key in Excel.
You can call this Macro in Worksheet or Workbook event to Disable or Enable Keys as per your requirement.

Sub Disable_Enable_CutCopyDel_Keys()

To Disable Copy,Cut,Past and Delete Keys
Application.EnableEvents = False

Application.OnKey "^{c}", ""              -----------------'Copy
Application.OnKey "^{x}", ""              -----------------'Cut
Application.OnKey "^{v}", ""              -----------------'Paste
Application.OnKey "{DEL}", ""

To Disable Cell Drag And Drop In Excel
Application.CellDragAndDrop = False

To Enable back the Copy,Cut,Past and Delete Keys

Application.EnableEvents = True

Application.OnKey "^{c}"              -----------------'Copy
Application.OnKey "^{x}"              -----------------'Cut
Application.OnKey "^{v}"              -----------------'Paste
Application.OnKey "{DEL}" 

To Enable back Cell Drag And Drop In Excel
Application.CellDragAndDrop = True

End Sub

Example-II :
The following Macro is used to disable the Cut,Copy options in mouse Right Click options.
You can call this Macro in Worksheet or Workbook event to Disable or Enable options as per your requirement.

Sub DisEnable_CutCopy_on_Mouse_Right_Click()

Dim Dis_Ctrl As Office.CommandBarControl

'Disable Cut Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=21)
         Dis_Ctrl.Enabled = False
     Next Dis_Ctrl

'Disable Copy Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=19)
          Dis_Ctrl.Enabled = False
     Next Dis_Ctrl

'Enable Cut Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=21)
            Dis_Ctrl.Enabled = True
     Next Dis_Ctrl

'Enable Copy Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=19)
            Dis_Ctrl.Enabled = True
     Next Dis_Ctrl

End Sub

Thanks,
TAMATAM


Tuesday, 2 December 2014

How to Copy the Files from Different Source Locations or Paths to Multiple Destinations

Macro to Copy the Files from Different Locations or Paths to Multiple Destinations
Sub Copy_SourceFiles_2_Destination()
Dim SrcFileName As String
Dim SrcFolderPath As String
Dim SrcFilExt As String
Dim SourceFile 'As String
Dim TargetFolderPath As String
Dim WS As Worksheet
Dim FSO As Object

'The Sheet in which we specify the Files details to Copy
Set WS = ThisWorkbook.Sheets("Bridge_Files_Trans")



Set FSO = CreateObject("Scripting.FileSystemObject")

For X = 2 To 100
If WS.Cells(X, 2) = "" Then Exit For

SrcFileName = WS.Cells(X, 2)
SrcFilExt = WS.Cells(X, 3)
SrcFolderPath = WS.Cells(X, 4)

'Setting the Source Folder path end with "\"
 If Right(SrcFolderPath, 1) <> "\" Then
        SrcFolderPath = SrcFolderPath & "\"
 End If

'Checking the Source Folder Exists or Not
If FSO.FolderExists(SrcFolderPath) = False Then
WS.Cells(X, 6) = "Failed"
MsgBox ("Source Folder Does Not Exist or Path Not Found")
GoTo Nxt:
End If

'Source File Name 
SourceFile = SrcFolderPath & SrcFileName & SrcFilExt

'Checking the Source File Existence
If FSO.FileExists(SourceFile) = False Then
WS.Cells(X, 6) = "Failed"
MsgBox ("Source File Does Not Exist or Path Not Found")
GoTo Nxt:
End If

'Source Folder Path
TargetFolderPath = WS.Cells(X, 5)

'Setting the Target Folder path end with "\"
If Right(TargetFolderPath, 1) <> "\" Then
        TargetFolderPath = TargetFolderPath & "\"
End If

'Checking the Target Folder Exists or Not
If FSO.FolderExists(TargetFolderPath) = False Then
WS.Cells(X, 6) = "Failed"
MsgBox ("Target Folder Does Not Exist or Path Not Found")
GoTo Nxt:
End If

'Copying the Files from Source location to Destination
FSO.CopyFile SourceFile, TargetFolderPath

'Changing the Source File Name in the Destination after Copy.
TgtFileName = TargetFolderPath & SrcFileName

Name TgtFileName & SrcFilExt As TgtFileName & " - " & WS.Cells(2, 7) & SrcFilExt
WS.Cells(X, 6) = "Success"

Nxt:

Next X

MsgBox "All Source Files SuccessFull Copied to Destination Folders", vbOKOnly, "Job Done"

Set WS = Nothing
Set FSO = Nothing

End Sub

Thanks,
TAMATAM


Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts