Sat 24 Dec 2016

Tips & Tricks 152 - While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page

By |Saturday, December 24th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8......Last Page should say 8 of 8.

The above problem will have to be solved through VBA.

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Copy paste the Macro code given
7. Save your file as .xlsm
8. Create a button and attach this macro to that button

 

 

Mon 19 Dec 2016

Solution - Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

By |Monday, December 19th, 2016|Categories: Solutions, VBA|Tags: , , , , , , , , , , , , |0 Comments

Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

(more…)

Sat 26 Nov 2016

Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

You have got a workbook and you want to save each worksheet as different workbook - You can adopt following method to do so if worksheets are not many in number.

1. Right click on a worksheet tab
2. Move or Copy
3. Select new book
4. Save this new workbook
5. Do it for all 20 worksheets.

(more…)

Sat 19 Nov 2016

Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

By |Saturday, November 19th, 2016|Categories: Challenges, VBA|Tags: , , , , , , , , , , , , |1 Comment

We made ourselves familiarized with Caesar's Shift in Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher and made its decrypter in Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter. We also made a fully functional encrypter in Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher based on VBA solution as formula based solution has its own limitation.

The challenge before you is to write a VBA function to create a fully functional Caesar's Shift Cipher Decrypter. This should take cell or text as first argument and amount of shift as second argument to generate the Decrypted Text.

1

Hence, to generate answer in A3, we have called the function as =DecryptCS(A2,C2)

Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.

The Excel file related to this challenge can be downloaded from Challenge 56 – Cryptography Challenge 5 – Decryption - Fully Functional Caesar’s Shift Cipher

The answer to this challenge would be published after a month i.e. on 19-Dec-16.

Sat 20 Aug 2016

Tips and Tricks 143 - Increment a Number when Workbook is Opened (Invoice or PO Number)

By |Saturday, August 20th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double Click on "This Workbook"
5. Copy paste the Macro code given - Replace Sheet1 and B1 as per your need
6. Save your file as .xlsm if you intend to reuse Macro again.

< A workbook illustrating this can be downloaded from Invoice Number Generator >

'**** Macro Starts

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("B1") = Worksheets("Sheet1").Range("B1") + 1
End Sub

Sat 09 Jul 2016

Tips & Tricks 140 - Multiple Hyperlinks within Excel Text Box

By |Saturday, July 09th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You created a Text Box and you put many words inside that say Yahoo, IBM, Microsoft etc...Now, you have given respective hyperlinks to them. But if you try to click on any hyperlink inside the text box, it will always open only one hyperlink.

It is possible to circumvent this behavior by work-around. You can execute following steps

1. Create a Text Box where you want to put all Hyperlinks.
2. Create many new Text Boxes.
3. Put the words in Text Boxes which you created in step 2 and give them Hyperlinks.
4. Drag the Text Boxes of Step 3 into Step 1 Text Box.
5. Align them properly and format them to remove borders.
6. Select all Text Boxes along with Step 1 Text Box > Page Layout > Group
7. Now, all individual Hyperlinks can be clicked separately.

 

Sat 28 May 2016

Tips & Tricks 137 - VBA - Delete Blank Rows for a Range Quickly (without Looping)

By |Saturday, May 28th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks.

1

You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same

 

Sun 27 Mar 2016

Solution - Challenge 37 – Find nth Alphabet from the Bottom

By |Sunday, March 27th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 37 – Find nth Alphabet from the Bottom.

Put following formula as Array formula in E1

=IF(SUM(--(ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13))>=C1,
INDEX(A1:A100,LARGE((ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13)
*ROW(A1:A100),C1)),"")

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The workbook containing the solution can be downloaded from Solution - Challenge 37 - nth Alphabet from the Bottom.

Sun 13 Mar 2016

Article 37 - VBA - Generating Unique (Non-repeating) Random Numbers Efficiently

By |Sunday, March 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100.

The algorithm to ensure uniqueness is following -

1. Given lower limit and upper limit, generate all numbers and populate an array. Now, this array contains all numbers between lower limit and upper limit sequentially. Hence, in case of 10 to 100, it will contain entries 10, 11, 12, .....99,100.
2. Next is to shuffle the array randomly using Fisher Yates algorithm so that the array contains the numbers 10, 11, 12...99,100 in a random order.
(more…)

Tue 01 Mar 2016

Solution - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm

By |Tuesday, March 01st, 2016|Categories: Solutions|Tags: , , , , , , , , |1 Comment

Below is a possible solution to the challenge - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm.

Enter the below formula as Array formula

=MOD(SUM(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)
=0,0,MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)*2-1,9)
+1)+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,2)),10)=0

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The workbook containing the above solution can be downloaded from Solution - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm.