HBN Infotech

Training – Service – Consultancy

How to create a number chart 1 to 100 using MS Excel macro

Excel Tutorial VBA Macros – How to create a number chart 1 to 100 using Excel macro – Excel Macro Examples Series – Excel VBA For Next Loop Example

Step 1 – Launch Excel’s Visual Basic Editor

To do that just click on developer tab, then click on visual basic to launch the Visual Basic Editor (VBE)

Step 2 – Insert Module

In the Visual Basic Editor, click on insert menu and then click on module

Step 3 – Copy and paste the below code to create the number chart 1 to 100 is as follows. In this example I emphasize Excel VBA For Next Loop

 

Sub india2()

‘The below for next loop creates the number chart

For n = 1 To 10
For m = 1 To 10
Cells(n, m).Select
a = a + 1
ActiveCell.Value = a
Next m
Next n

‘The below excel vba code formats the number chart and make the cells appear square
Range(“A1:J10”).Select
Selection.RowHeight = 25
Selection.ColumnWidth = 4

‘The excel vba code below centers the contents to middle center

Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter

‘ applying the borders

Selection.Borders(xlEdgeLeft).Weight = xlThick
Selection.Borders(xlEdgeTop).Weight = xlThick
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeRight).Weight = xlThick
Selection.Borders(xlInsideVertical).Weight = xlThick
Selection.Borders(xlInsideHorizontal).Weight = xlThick

‘ Now this part of excel vba code below adds a column before the number chart

Range(“A1”).Select
Selection.EntireColumn.Insert
Selection.EntireRow.Insert

‘ Now this part of excel vba code below adjusts the first column and row height

Selection.RowHeight = 63.75
Selection.ColumnWidth = 13.29

‘ Now this part of excel vba code below creates a title to the number chart and format it

Range(“B1:K1”).Select
Selection.Merge
ActiveCell.Value = “Number Chart 1 to 100”
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.Font.Bold = True
Selection.Font.Size = 18

‘ Now the number chart is ready.
‘You can create a number chart in other dimensions in the same way.
‘ Just change the values

‘ Hope you enjoyed the tutorial

End Sub

Step 4 – Run the Macro

To run the macro follow any of the methods below:

Method 1: Place the cursor anywhere in the code you just added to the module and click on Run Sub/UserForm (F5) button or press the F5 key on your keyboard.

Method 2: Click on Run Menu and then click on Run Sub/UserForm (F5)

Method 3: Go to Excel’s Developer Tab and click on Macros, select the macro by name and click on run button (You may also press Alt + F8 keys to display the macros dialog)

Method 4: Go to Excel’s View Tab, click on macros and then click on view macros. Then select the macro name and click on run.

excel vba for next loop

Similar Tutorial: Excel macro examples – How to insert single or multiple worksheet based on active cell data or content – Excel VBA For Each Loop Example

himaghiri

Founder at HBN Infotech
Himaghiri Thanayan.N is the founder of HBN Infotech. He has great dreams and passion for Graphic Design, Web Design, CAD Design, Arts & Crafts, Science & Information Technology and Article Writing.
Updated: April 11, 2017 — 1:11 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

3 × 2 =

HBN Infotech © 2016 Frontier Theme