MrExcel Publishing
Your One Stop for Excel Tips & Solutions

excel macro


Posted by Tim on July 17, 2001 10:41 AM

What is the command for getting the user to enter the size of a variable?
I want the macro to copy a cell into every cell in a square of the size that the user types in.
Can you help?


Posted by Russell on July 17, 2001 10:48 AM

You could do something like this:

Sub UserSquare()

Dim UserInput
Dim SomeValue ' what you will put in each cell in the square
Dim intI As Integer
Dim intJ As Integer

UserInput = InputBox("Enter size of square")

SomeValue = "X"

' This assumes you are starting at cell A1
For intI = 1 To UserInput
For intJ = 1 To UserInput

Cells(intI, intJ).Value = SomeValue

Next intJ
Next intI

End Sub

Posted by Tim on July 17, 2001 1:07 PM

I tried this-

Sub Macro1()
Dim UserInput
Dim I As Integer
Dim J As Integer
Dim TimValue
UserInput = InputBox("Length of Triangle", "Define Triangle")
TimValue = Selection.Copy
For intI = 1 To UserInput
For intJ = 1 To UserInput
Cells(intI, intJ).Value = TimValue
Next intJ
Next intI
End Sub

All I get is "TRUE" in every cell, but I want the number or whatever is in the original cell in every cell in the square.

Posted by Tim on July 17, 2001 1:10 PM

Oops, ok, I fixed the typo. It's I and J instead of intI and intJ, but I still get "TRUE" instead of the number? Help.

Here is the code:

Sub Macro1()
Dim UserInput
Dim I As Integer
Dim J As Integer
Dim TimValue
UserInput = InputBox("Length of Triangle", "Define Triangle")
TimValue = Selection.Copy
For I = 1 To UserInput
For J = 1 To UserInput
Cells(I, J).Value = TimValue
Next J
Next I
End Sub

Posted by Tim on July 18, 2001 8:46 AM

What if I don't want to start at cell A1?
I want to start at the cell that has the data I am copying.