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

Updated: April 11, 2017 — 1:11 pm

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.