{"product_id":"lets-play-with-excel-anurag-pandey-9798502654241","title":"Let's Play with Excel","description":"Two of 51 Macros from this book are given below: \u003cb\u003eMacros to Encode and Decode the Sheet\u003c\/b\u003e[Please check both the Macros by copying into VB Editor of Any Excel file. Then RUN the Macro.] \u003cp\u003e\u003c\/p\u003eSub Encode_the_Sheet_Selected_Rows_N_Columns()\u003cbr\u003eOn Error GoTo ERR\u003cbr\u003eDim S1, S2 As Variant\u003cbr\u003eDim change(20), CHANGE2(20), l, M As Integer\u003cbr\u003eDim ic, lc, ir, lr, hello, J, k As Long \u003cp\u003e\u003c\/p\u003eFor I = 1 To 20\u003cbr\u003eIf I change(I - 1) = I * I\u003cbr\u003eCHANGE2(I - 1) = I * 4\u003cbr\u003eElse\u003cbr\u003echange(I - 1) = I * 3\u003cbr\u003eCHANGE2(I - 1) = I * 2\u003cbr\u003eEnd If\u003cbr\u003eNext\u003cbr\u003eic = Int(InputBox(\"Enter begining Column No from where you want to Encode the Data.\"))\u003cbr\u003eIf ic lc = Int(InputBox(\"Enter Last Column No upto where you want to Encode the Data.\"))\u003cbr\u003eIf lc hello = lc\u003cbr\u003elc = ic\u003cbr\u003eic = hello\u003cbr\u003eEnd If \u003cp\u003e\u003c\/p\u003eir = Int(InputBox(\"Enter begining Row No from where you want to Encode the Data.\"))\u003cbr\u003eIf ir lr = Int(InputBox(\"Enter Last Row No upto where you want to Encode the Data.\"))\u003cbr\u003eIf lr hello = lr\u003cbr\u003elr = ir\u003cbr\u003eir = hello\u003cbr\u003eEnd If\u003cbr\u003eM = 0\u003cbr\u003eFor J = ir To lr \u003cp\u003e\u003c\/p\u003eFor k = ic To lc\u003cbr\u003eS1 = \"\"\u003cbr\u003eS1 = \"\"\"\" \u0026amp; Sheets(\"Sheet1\").Cells(J, k).NumberFormat \u0026amp; \"\"\"\"\u003cbr\u003eS1 = S1 \u0026amp; Sheets(\"Sheet1\").Cells(J, k).Value\u003cbr\u003eSheets(\"Sheet1\").Cells(J, k).NumberFormat = \"General\"\u003cbr\u003eS2 = \"\"\"\"\u003cbr\u003el = 0\u003cbr\u003eFor I = 1 To Len(S1)\u003cbr\u003eS2 = S2 \u0026amp; Chr(Asc(Mid(S1, I, 1)) + change(l) + CHANGE2(M))\u003cbr\u003el = l + 1\u003cbr\u003eIf l \u0026gt; 19 Then l = 0\u003cbr\u003eNext\u003cbr\u003eM = M + 1\u003cbr\u003eIf M \u0026gt; 19 Then M = 0\u003cbr\u003eSheets(\"Sheet1\").Cells(J, k).Value = S2\u003cbr\u003eNext\u003cbr\u003eNext\u003cbr\u003eExit Sub\u003cbr\u003eERR: \u003cbr\u003eMsgBox ERR.Number \u0026amp; \" \" \u0026amp; ERR.Description\u003cbr\u003eEnd Sub \u003cp\u003e\u003c\/p\u003eSub Decode_the_Sheet_Selected_Rows_N_Columns()\u003cbr\u003eOn Error GoTo ERR\u003cbr\u003eDim S1, S2, s3 As Variant\u003cbr\u003eDim change(20), CHANGE2(20), l As Integer\u003cbr\u003eDim ic, lc, ir, lr, hello, J, k As Long \u003cp\u003e\u003c\/p\u003eFor I = 1 To 20\u003cbr\u003eIf I change(I - 1) = I * I\u003cbr\u003eCHANGE2(I - 1) = I * 4\u003cbr\u003eElse\u003cbr\u003echange(I - 1) = I * 3\u003cbr\u003eCHANGE2(I - 1) = I * 2\u003cbr\u003eEnd If\u003cbr\u003eNext\u003cbr\u003eMsgBox (\"You are required to tell exact Begining and End Columns and Rows Nos. of Encoded Data.\")\u003cbr\u003eic = Int(InputBox(\"Enter begining Column No of the Encoded Data.\"))\u003cbr\u003eIf ic lc = Int(InputBox(\"Enter Last Column No of the Encoded Data.\"))\u003cbr\u003eIf lc hello = lc\u003cbr\u003elc = ic\u003cbr\u003eic = hello\u003cbr\u003eEnd If \u003cp\u003e\u003c\/p\u003eir = Int(InputBox(\"Enter begining Row No of the Encoded Data.\"))\u003cbr\u003eIf ir lr = Int(InputBox(\"Enter Last Row No of the Encoded Data.\"))\u003cbr\u003eIf lr hello = lr\u003cbr\u003elr = ir\u003cbr\u003eir = hello\u003cbr\u003eEnd If\u003cbr\u003eM = 0\u003cbr\u003eFor J = ir To lr \u003cp\u003e\u003c\/p\u003eFor k = ic To lc\u003cbr\u003eS1 = \"\"\u003cbr\u003eS1 = Sheets(\"Sheet1\").Cells(J, k).Value\u003cbr\u003eS2 = \"\"\u003cbr\u003el = 0\u003cbr\u003eFor I = 2 To Len(S1)\u003cbr\u003eS2 = S2 \u0026amp; Chr(Asc(Mid(S1, I, 1)) - (change(l) + CHANGE2(M)))\u003cbr\u003el = l + 1\u003cbr\u003eIf l \u0026gt; 19 Then l = 0\u003cbr\u003eNext\u003cbr\u003eFor I = 2 To Len(S2)\u003cbr\u003eIf Mid(S2, I, 1) = \"\"\"\" Then\u003cbr\u003es3 = Mid(S2, 2, I - 2)\u003cbr\u003eS1 = Right(S2, Len(S2) - I)\u003cbr\u003eExit For\u003cbr\u003eEnd If\u003cbr\u003eNext\u003cbr\u003eM = M + 1\u003cbr\u003eIf M \u0026gt; 19 Then M = 0\u003cbr\u003eIf Left(S2, 1) = \"\"\"\" And I Sheets(\"Sheet1\").Cells(J, k).Value = S1\u003cbr\u003eSheets(\"Sheet1\").Cells(J, k).Select\u003cbr\u003eSelection.NumberFormat = s3\u003cbr\u003eEnd If\u003cbr\u003eNext\u003cbr\u003eNext\u003cbr\u003eExit Sub\u003cbr\u003eERR: \u003cbr\u003eMsgBox ERR.Number \u0026amp; \" \" \u0026amp; ERR.Description\u003cbr\u003eEnd Sub\u003cbr\u003e\u003cbr\u003e\u003cb\u003eAuthor:\u003c\/b\u003e Anurag Pandey\u003cbr\u003e\u003cb\u003eISBN-13:\u003c\/b\u003e 9798502654241\u003cbr\u003e\u003cb\u003ePublisher:\u003c\/b\u003e Independently Published\u003cbr\u003e\u003cb\u003eLanguage:\u003c\/b\u003e English\u003cbr\u003e\u003cb\u003ePublished:\u003c\/b\u003e 05\/11\/2021\u003cbr\u003e\u003cb\u003ePages:\u003c\/b\u003e 216\u003cbr\u003e\u003cb\u003eFormat:\u003c\/b\u003e Paperback\u003cbr\u003e\u003cb\u003eWeight:\u003c\/b\u003e 0.65lbs\u003cbr\u003e\u003cb\u003eSize:\u003c\/b\u003e 9.00h x 6.00w x 0.46d","brand":"Anurag Pandey","offers":[{"title":"Paperback","offer_id":48851548471551,"sku":"9798502654241","price":9.99,"currency_code":"USD","in_stock":true}],"url":"https:\/\/www.whiterainbookhouse.com\/products\/lets-play-with-excel-anurag-pandey-9798502654241","provider":"WR Book House","version":"1.0","type":"link"}