Make a table

billy1r · November 27th, 2012, 01:13 PM

I want to use VBA to make a table starting at A2 and going to E21 (this should be 100 cells). This function will place the numbers 1 through 100 in the cells sequentially.
Anybody have an idea how to do that?

billy1r · November 27th, 2012, 01:46 PM

I can make it change background color by doing this, but can't figure out how to make it put in the numbers.

Code:

Sub Make_Table()
'/===================================================================
'/
'/===================================================================
Dim nRowCounter             As Integer
Dim nColCounter             As Integer
 
For nRowCounter = 1 To 20
    For nColCounter = 0 To 4
 
        ThisWorkbook.Worksheets(1).Range("A1") _
            .Offset(nRowCounter, nColCounter).Interior.Color = RGB(0, 150, 0)
    Next
 
Next
 
End Sub

allenm · December 19th, 2012, 03:36 PM

There are a lot of ways of accomplishing this task. There needs to be more specific instructions on what you really want to accomplish to let programmers responding to you know what you are doing and how you hope to accomplish it so we can work within your framework. I'll list 2 ways you can do this.

This one uses a formula to calculate the value for each cell:

Code:

Public Sub PlaceNumbersUsingRowCol()

'Places numbers using formula with row and column numbring from 1 to 100 left to right
' and top to bottom in cells A2 through E21 and changes background color for all cells

'Declaring variables correctly makes coding easier & enables office's built in auto complete
  Dim wsTarget As Worksheet, rCellOn As Range
  Set wsTarget = Me
  With wsTarget
    .Range("A2:E21").Interior.Color = RGB(0, 150, 0)
    For Each rCellOn In .Range("A2:E21") 'For each itterates through a set of objects sequentially
      rCellOn.Value = (rCellOn.Row - 2) * 5 + (rCellOn.Column) 'used formula to calculate #'s
    Next
  End With
  Set wsTarget = Nothing

End Sub

Another way to do this is to track the number yourself. This is a little less elegant but may help clarify how for/each loops (left to right, top to bottom):

Code:

Public Sub PlaceNumbersUsingOwnVariable()

'Declaring variables correctly makes coding easier & enables office's built in auto complete
  Dim wsTarget As Worksheet, rCellOn As Range, iCounter As Long
  Set wsTarget = Me
  With wsTarget
    .Range("A2:E21").Interior.Color = RGB(0, 150, 0)
    For Each rCellOn In .Range("A2:E21") 'For each itterates through a set of objects sequentially
      iCounter = iCounter + 1 'Calculate my own number
      rCellOn.Value = iCounter 'Place my own value in the current cell from For/Each loop
    Next
  End With
  Set wsTarget = Nothing

End Sub

I very rarely use offset, preferring to be specific and track cell positions myself. However if you wish to use your method, you can just add a line to place the number. I'd still put the color in the entire range first to reduce number of actions to desired goal:

Code:

Public Sub Make_Table()
'/===================================================================
'/
'/===================================================================
Dim nRowCounter             As Integer
Dim nColCounter             As Integer
 
For nRowCounter = 1 To 20
    For nColCounter = 0 To 4
        With ThisWorkbook.Worksheets(1).Range("A1").Offset(nRowCounter, nColCounter)
            .Interior.Color = RGB(0, 150, 0)
            .Value = (nRowCounter - 1) * 5 + nColCounter + 1
        End With
    Next
Next
 
End Sub

Notice I use with so I don't have to type the entire line referring to the same cell over & over again.

Still not sure what your actual question/need is but I hoped this helped point you in the right direction.

billy1r · December 20th, 2012, 06:50 PM

Thanks, allenm. I tried using your code and got a "compile error: Improper use of Me keyword."
As far as need. I am trying to learn more about VBA and wanted to be able to make a spreadsheet with the table in it quickly so I can practice doing different things to it with VBA and maybe become smarter!

What I was doing when I posted this was using For Next loops to change the cell colors left-to-right and top-to-bottom or vice-versa.

Thanks for your help. Let me digest this a little while and maybe I'll get if figured out. Just trying to keep my mind fresh.

allenm · December 20th, 2012, 07:07 PM

Did you put the code in a Module or in your spreadsheet? My guess is you're working with code in a module. Me is a reserved reference to the object your coding in. IF you are putting the code in a worksheet, this would work because a worksheet variable can inherit the worksheet attributes of Me. If it's a Module then it doesn't have any worksheet attributes to inherit.

Try replacing the line:
Set wsTarget = Me

With this line:
Set wsTarget = ThisWorkbook.ActiveSheet

-OR- this line, replacing Tab Name of Worksheet with actual name of the tab (sheet1 or whatever):
Set wsTarget = ThisWorkbook.Worksheets("Tab Name Of Worksheet")

billy1r · December 20th, 2012, 07:14 PM

Yep, right you are! I was using it in a module. I was just about to try it in a spreadsheet code window when you reply popped up. By golly, I may just mess around and learn this VBA yet.
Thanks, allenm.