Dictionary in VBA using example

Option Explicit

Sub dictionaryexample()

Sheets("conditionalformattingvba").Select
Dim dict As Object 'Declare a generic Object reference
Set dict = CreateObject("Scripting.Dictionary") 'Late Binding of the Dictionary
Range("T2").Select
While ActiveCell.Value <> ""
Dim key, val
key = ActiveCell.Value: val = ActiveCell.Offset(0, 1).Address
'Add item to VBA Dictionary
If Not dict.Exists(key) Then
    dict.Add key, val
End If
ActiveCell.Offset(1, 0).Select


Wend

Debug.Print dict.Count 'Result: 1

For Each key In dict.Keys
   Debug.Print key
Next key

'Print all items
For Each val In dict.Items
   Debug.Print val
Next val


'copy format of cells to different location
Dim tempaddress As String
Range("V2").Select
tempaddress = Selection.Address
For Each val In dict.Items

Range(val).Select
Selection.Copy
Range(tempaddress).PasteSpecial Paste:=xlPasteFormats
tempaddress = ActiveCell.Offset(1, 0).Address

Next val
Application.CutCopyMode = False


'Dispose of VBA Dictionary
Set dict = Nothing
End Sub

Conditional formatting using VBA where the conditions and the respective formatting is given,VBA Teacher Kolkata,Sourav Bhattacharya

If you zoom in the picture on the right side there are conditions and the formatting 

after running the macro the formatting based on the given conditions will be applied on the data 


The source code :


Option Explicit

Sub conditionsuperfinal()
Sheets("conditionalformattingvba").Select
Dim dict As Object 'Declare a generic Object reference
Set dict = CreateObject("Scripting.Dictionary") 'Late Binding of the Dictionary
Range("T2").Select
While ActiveCell.Value <> ""
Dim key, val
key = ActiveCell.Value: val = ActiveCell.Offset(0, 1).Address
'Add item to VBA Dictionary
If Not dict.Exists(key) Then
    dict.Add key, val
End If
ActiveCell.Offset(1, 0).Select


Wend

'Debug.Print dict.Count 'Result: 1
'
'For Each key In dict.Keys
'   Debug.Print key
'Next key
'
''Print all items
'For Each val In dict.Items
'   Debug.Print val
'Next val


'copy format of cells where condition matches
'Dim tempaddress As String
'Range("V2").Select
'tempaddress = Selection.Address
'For Each val In dict.Items
'
'Range(val).Select
'Selection.Copy
'Range(tempaddress).PasteSpecial Paste:=xlPasteFormats
'tempaddress = ActiveCell.Offset(1, 0).Address
'
'Next val
'Application.CutCopyMode = False

Sheets("conditionalformattingvba").Select
Dim workingrange, cell As Range
Range("I2:N19").Select
Set workingrange = Selection

For Each cell In workingrange
For Each key In dict.Keys
If InStr(1, cell.Value, key, vbTextCompare) > 0 Then
Range(dict(key)).Select
Selection.Copy
cell.PasteSpecial Paste:=xlPasteFormats
Exit For

Else
cell.ClearFormats
End If
Next key

Next cell


'Dispose of VBA Dictionary
Set dict = Nothing


End Sub

Conditional formatting using VBA

Option Explicit


Sub conditionalfinal()
Sheets("conditionalformattingvba").Select
'Range("G1").Select
'
'Range(Selection, Selection.End(xlDown)).Select
'Range(Selection, Selection.End(xlToRight)).Select
'
Dim workingrange, cell As Range

'Set workingrange = Selection
'


'MsgBox (workingrange.Address)

'Dim workingrange As Range
'Set workingrange = Application.InputBox(Title:="Select the range for conditionalformatting", Prompt:="select the range", Type:=8)
'MsgBox (workingrange.Address)

'Sheets("conditionalformatting").Select
'Range("I5").CurrentRegion.Select
'
'Set workingrange = Selection
'
'
'
'MsgBox (workingrange.Address)

Sheets("conditionalformatting").Select
Range("I2:N19").Select
Set workingrange = Selection
MsgBox (workingrange.Address)


For Each cell In workingrange
If InStr(1, cell.Value, "North", vbTextCompare) > 0 Then
cell.Interior.ColorIndex = 3
cell.Font.ColorIndex = 19
ElseIf InStr(1, cell.Value, "South", vbTextCompare) > 0 Then
cell.Interior.ColorIndex = 23
cell.Font.ColorIndex = 2
ElseIf InStr(1, cell.Value, "West", vbTextCompare) > 0 Then
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 30
ElseIf InStr(1, cell.Value, "East", vbTextCompare) > 0 Then
cell.Interior.ColorIndex = 27
cell.Font.ColorIndex = 1
Else
cell.ClearFormats
End If




Next cell





End Sub

Adding datestamp to a filename using a function with a filename as a parameter in Powershell 7 in Debian 10

function adddatetofilename{
    Param(
        [Parameter(Mandatory=$true,position=1)]
        [string]$filename

    )
   
#$filename="\home\sourav\test.txt"
new-item -type file -name $filename
$todaysdate=get-date -Format MMddyy
$file=Get-ChildItem $filename 
$newfilename=$file.BaseName+"."+$todaysdat+$file.Extension
Rename-Item -path $filename -NewName $newfilename -verbose
}
adddatetofilename -filename ./test.txt

Adding a datestamp to a filename in powershell 7 in Debian 10

create a test file

new-item -type file -name test.txt

$filename="\home\sourav\test.txt"

$todaysdate=get-date -Format MMddyy

$file=Get-ChildItem $filename  

$newfilename=$file.BaseName+"."+$todaysdat+$file.Extension

Rename-Item -path $filename -NewName $newfilename -verbose