Suprajathvam

Wednesday 4 October 2023

Swaraprastharam- Abhogi

1. MSS- Manjudai Putti

A short, but stormy swirl of Kalpanaswaras by MSS in her rendition of the Annamarcharya Krithi "Manjudai putti" in the ragam Abhogi

1. dSdm-gmgr-s,rg

2. m,grg-m,grg-srgm-grrsrgm

3. Sarvalaghu

m,md-

m,mgmd,d,-

ddddmm,m-

ggmgmddmgrg,g,,,-

rgmgrs,-

srgmgrs,-

ss,s,rgmgrs-

rgmddmmggrssrgm,-

gmddmgrgmddmdS-

d,SRGMMGRSdS-

GRRSSddmmggrrssd,srgm

Wednesday 29 March 2023

Macro to unprotect multiple workbooks in a Folder

Object of the Macro:

Multiple excel workbooks are in a folder.

Each workbook is protected by a password.

I want to get them unprotected using a the password and get them pooled into a folder.

Code:

Sub Hell3()

    Dim WB As Workbook

    Dim xFd As FileDialog

    Dim xFdItem As Variant

    Dim xFileName As String

    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)

    If xFd.Show = -1 Then

        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator

        xFileName = Dir(xFdItem & "*.xls*")

        MkDir xFdItem & "\Password Removed Files"

        Do While xFileName <> ""

            Set WB = Workbooks.Open((xFdItem & xFileName), Password:="pass")

                WB.SaveAs Filename:=xFdItem & "Password Removed Files\" & xFileName, FileFormat:=51, Password:="", WriteResPassword:="", _

                ReadOnlyRecommended:=False, CreateBackup:=False

                WB.Close True

     xFileName = Dir

        Loop

    End If

End Sub

Note:

Ensure to replace the word "pass" with your actual password in the code

Thursday 12 January 2023

Ind AS 2- Inventories

My posts on Ind- AS 2- Inventories in Linkedin:

Wednesday 21 December 2022

Macro to compare two data sets

1. Object of the macro

a)The data is populated as follows:

b) I want to compare based on the Unique identifier List A and List B

c) Highlight the common items in List A and List B

d) Get populated in Sheet 2, items present in List A, but not in List B

e)Get populated in Sheet 3, items present in List B, but not in List A

2. Code

Sub Comparedatasets()
Application.ScreenUpdating = False
lr1 = Range("A" & Rows.Count).End(xlUp).Row
lr2 = Range("D" & Rows.Count).End(xlUp).Row
Sheets(1).Range("A2", "B2").Copy
Sheets(2).Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets(3).Range("A1").PasteSpecial Paste:=xlPasteValues
check = False
For r = 3 To lr1
Set compare1 = Cells(r, 1)
For q = 3 To lr2
Set Compare2 = Cells(q, 4)
If compare1 = Compare2 Then Compare2.Interior.Color = vbYellow: check = True
Next q
If check = False Then
Range(compare1, compare1.End(xlToRight)).Copy
Sheets(3).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _Paste:=xlPasteValues
End If
check = False
Next r
check = False
For r = 3 To lr2
Set compare1 = Cells(r, 4)
For q = 3 To lr1
Set Compare2 = Cells(q, 1)
If compare1 = Compare2 Then Compare2.Interior.Color = vbYellow: check = True
Next q

If check = False Then
Range(compare1, compare1.End(xlToRight)).Copy
Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _Paste:=xlPasteValues
End If
check = False
Next r
Application.ScreenUpdating = True
End Sub

3) Result

Wednesday 14 December 2022

Diaries on VBA- Scripting Dictionary

1. Part 1- Basics of Dictionary

a) Dictionary is used to assign a value to a key/item

b)Syntax

i) If I want to create Gowtham as new dictionary

Dim Gowtham as Newdictionary

ii) Add items to the dictionary Gowtham

For Eg. I want to assign 1 to a, 2 to b

gowtham. add(a,1)

gowtham. add(a,2)

c) When a key is not already declared and is called later, it will get created in dictionary

For Eg,

Sub usedictionary()

Dim dict As New Dictionary

dict.Add "a", 1
dict.Add "b", 2
dict.Add "c", 3
dict("d") = dict("d") + 100

Debug.Print dict("d")

End Sub

..., the above code will print 100

Tuesday 6 December 2022

Macro to count the number of characters in cells and populate the data set sperately

Object of the Macro

I want to:

count the number of HSN digits in Column A,
get them populated in Column C,
filter the items with HSN less than 6 digits and
get the dataset pasted at the cell H1.

2. The Code

Sub countcharactersincell()
Dim a As String
Dim b As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
j = Cells.End(xlDown).Row
For i = 2 To j
a = Cells(i, 1)
b = Len(a)
Cells(i, 3) = b
Next i
Range("A1").AutoFilter Field:=3, Criteria1:="<6"
Range("a1:c" & Range("a1").End(xlDown).Row).SpecialCells(xlCellTypeVisible).Copy
Cells(1, 8).PasteSpecial
End Sub

3. Output