menu
{ "item_title" : "Let's Play with Excel", "item_author" : [" Anurag Pandey "], "item_description" : "Two of 51 Macros from this book are given below: Macros to Encode and Decode the Sheet Please check both the Macros by copying into VB Editor of Any Excel file. Then RUN the Macro.] Sub Encode_the_Sheet_Selected_Rows_N_Columns()On Error GoTo ERRDim S1, S2 As VariantDim change(20), CHANGE2(20), l, M As IntegerDim ic, lc, ir, lr, hello, J, k As Long For I = 1 To 20If I change(I - 1) = I * ICHANGE2(I - 1) = I * 4Elsechange(I - 1) = I * 3CHANGE2(I - 1) = I * 2End IfNextic = Int(InputBox(Enter begining Column No from where you want to Encode the Data.))If ic lc = Int(InputBox(Enter Last Column No upto where you want to Encode the Data.))If lc hello = lclc = icic = helloEnd If ir = Int(InputBox(Enter begining Row No from where you want to Encode the Data.))If ir lr = Int(InputBox(Enter Last Row No upto where you want to Encode the Data.))If lr hello = lrlr = irir = helloEnd IfM = 0For J = ir To lr For k = ic To lcS1 = S1 = & Sheets(Sheet1).Cells(J, k).NumberFormat & S1 = S1 & Sheets(Sheet1).Cells(J, k).ValueSheets(Sheet1).Cells(J, k).NumberFormat = GeneralS2 = l = 0For I = 1 To Len(S1)S2 = S2 & Chr(Asc(Mid(S1, I, 1)) + change(l) + CHANGE2(M))l = l + 1If l > 19 Then l = 0NextM = M + 1If M > 19 Then M = 0Sheets(Sheet1).Cells(J, k).Value = S2NextNextExit SubERR:MsgBox ERR.Number & & ERR.DescriptionEnd Sub Sub Decode_the_Sheet_Selected_Rows_N_Columns()On Error GoTo ERRDim S1, S2, s3 As VariantDim change(20), CHANGE2(20), l As IntegerDim ic, lc, ir, lr, hello, J, k As Long For I = 1 To 20If I change(I - 1) = I * ICHANGE2(I - 1) = I * 4Elsechange(I - 1) = I * 3CHANGE2(I - 1) = I * 2End IfNextMsgBox (You are required to tell exact Begining and End Columns and Rows Nos. of Encoded Data.)ic = Int(InputBox(Enter begining Column No of the Encoded Data.))If ic lc = Int(InputBox(Enter Last Column No of the Encoded Data.))If lc hello = lclc = icic = helloEnd If ir = Int(InputBox(Enter begining Row No of the Encoded Data.))If ir lr = Int(InputBox(Enter Last Row No of the Encoded Data.))If lr hello = lrlr = irir = helloEnd IfM = 0For J = ir To lr For k = ic To lcS1 = S1 = Sheets(Sheet1).Cells(J, k).ValueS2 = l = 0For I = 2 To Len(S1)S2 = S2 & Chr(Asc(Mid(S1, I, 1)) - (change(l) + CHANGE2(M)))l = l + 1If l > 19 Then l = 0NextFor I = 2 To Len(S2)If Mid(S2, I, 1) = Thens3 = Mid(S2, 2, I - 2)S1 = Right(S2, Len(S2) - I)Exit ForEnd IfNextM = M + 1If M > 19 Then M = 0If Left(S2, 1) = And I Sheets(Sheet1).Cells(J, k).Value = S1Sheets(Sheet1).Cells(J, k).SelectSelection.NumberFormat = s3End IfNextNextExit SubERR:MsgBox ERR.Number & & ERR.DescriptionEnd Sub", "item_img_path" : "https://covers2.booksamillion.com/covers/bam/9/79/850/265/9798502654241_b.jpg", "price_data" : { "retail_price" : "11.99", "online_price" : "11.99", "our_price" : "11.99", "club_price" : "11.99", "savings_pct" : "0", "savings_amt" : "0.00", "club_savings_pct" : "0", "club_savings_amt" : "0.00", "discount_pct" : "10", "store_price" : "" } }
Let's Play with Excel|Anurag Pandey

Let's Play with Excel

local_shippingShip to Me
In Stock.
FREE Shipping for Club Members help

Other Available Formats

Paperback
11.99
Paperback
$14.99

show all formats

Overview

Two of 51 Macros from this book are given below: Macros to Encode and Decode the Sheet Please check both the Macros by copying into VB Editor of Any Excel file. Then RUN the Macro.] Sub Encode_the_Sheet_Selected_Rows_N_Columns()
On Error GoTo ERR
Dim S1, S2 As Variant
Dim change(20), CHANGE2(20), l, M As Integer
Dim ic, lc, ir, lr, hello, J, k As Long For I = 1 To 20
If I change(I - 1) = I * I
CHANGE2(I - 1) = I * 4
Else
change(I - 1) = I * 3
CHANGE2(I - 1) = I * 2
End If
Next
ic = Int(InputBox("Enter begining Column No from where you want to Encode the Data."))
If ic lc = Int(InputBox("Enter Last Column No upto where you want to Encode the Data."))
If lc hello = lc
lc = ic
ic = hello
End If ir = Int(InputBox("Enter begining Row No from where you want to Encode the Data."))
If ir lr = Int(InputBox("Enter Last Row No upto where you want to Encode the Data."))
If lr hello = lr
lr = ir
ir = hello
End If
M = 0
For J = ir To lr For k = ic To lc
S1 = ""
S1 = """" & Sheets("Sheet1").Cells(J, k).NumberFormat & """"
S1 = S1 & Sheets("Sheet1").Cells(J, k).Value
Sheets("Sheet1").Cells(J, k).NumberFormat = "General"
S2 = """"
l = 0
For I = 1 To Len(S1)
S2 = S2 & Chr(Asc(Mid(S1, I, 1)) + change(l) + CHANGE2(M))
l = l + 1
If l > 19 Then l = 0
Next
M = M + 1
If M > 19 Then M = 0
Sheets("Sheet1").Cells(J, k).Value = S2
Next
Next
Exit Sub
ERR:
MsgBox ERR.Number & " " & ERR.Description
End Sub Sub Decode_the_Sheet_Selected_Rows_N_Columns()
On Error GoTo ERR
Dim S1, S2, s3 As Variant
Dim change(20), CHANGE2(20), l As Integer
Dim ic, lc, ir, lr, hello, J, k As Long For I = 1 To 20
If I change(I - 1) = I * I
CHANGE2(I - 1) = I * 4
Else
change(I - 1) = I * 3
CHANGE2(I - 1) = I * 2
End If
Next
MsgBox ("You are required to tell exact Begining and End Columns and Rows Nos. of Encoded Data.")
ic = Int(InputBox("Enter begining Column No of the Encoded Data."))
If ic lc = Int(InputBox("Enter Last Column No of the Encoded Data."))
If lc hello = lc
lc = ic
ic = hello
End If ir = Int(InputBox("Enter begining Row No of the Encoded Data."))
If ir lr = Int(InputBox("Enter Last Row No of the Encoded Data."))
If lr hello = lr
lr = ir
ir = hello
End If
M = 0
For J = ir To lr For k = ic To lc
S1 = ""
S1 = Sheets("Sheet1").Cells(J, k).Value
S2 = ""
l = 0
For I = 2 To Len(S1)
S2 = S2 & Chr(Asc(Mid(S1, I, 1)) - (change(l) + CHANGE2(M)))
l = l + 1
If l > 19 Then l = 0
Next
For I = 2 To Len(S2)
If Mid(S2, I, 1) = """" Then
s3 = Mid(S2, 2, I - 2)
S1 = Right(S2, Len(S2) - I)
Exit For
End If
Next
M = M + 1
If M > 19 Then M = 0
If Left(S2, 1) = """" And I Sheets("Sheet1").Cells(J, k).Value = S1
Sheets("Sheet1").Cells(J, k).Select
Selection.NumberFormat = s3
End If
Next
Next
Exit Sub
ERR:
MsgBox ERR.Number & " " & ERR.Description
End Sub

This item is Non-Returnable

Details

  • ISBN-13: 9798502654241
  • ISBN-10: 9798502654241
  • Publisher: Independently Published
  • Publish Date: May 2021
  • Dimensions: 9 x 6 x 0.46 inches
  • Shipping Weight: 0.65 pounds
  • Page Count: 216

Related Categories

You May Also Like...

    1

BAM Customer Reviews