MrExcel Publishing
Your One Stop for Excel Tips & Solutions

alphabetical numbering


Posted by Jeff on November 09, 2000 6:10 PM

I am trying to create a macro that will accept a numerical value for "number of variables," assign letters for those variables (var1 = a, var2 = b, etc...) and further textual values (english statements) to those variables.

Here is my code

Sub Logic()
Dim Vars
Dim Cols
Vars = InputBox("How many variables are in the statement? (Note: Variables will be named in descending order beginning with 'a.'", "Step One", 2)
Range("A1").Select
For Cols = 1 To Vars
Var1 = InputBox("Let variable " & (Cols) & "=", Type:=2)
ActiveCell.Formula = Var1
ActiveCell.Offset(0, 1).Select
Next Cols
End Sub


Posted by Tim Francis-Wright on November 09, 2000 9:11 PM

If all you want to do is to populate row 1 of
the spreadsheet, the following code will do it:

Sub Logic()
Dim Vars
Dim Cols
Dim Var1
Vars = InputBox("How many variables are in the statement? (Note: Variables will be named in descending order beginning with 'a.'", "Step One", 2)
Range("A1").Select
' **
For Cols = 1 To Vars
Var1 = Application.InputBox("Let variable " & (Cols) & "=", , , , , , 2)
' **
ActiveCell.Formula = Var1
ActiveCell.Offset(0, 1).Select
Next Cols
End Sub

If you need for the code to keep track of--and
remember--which statement goes with each variable,
you can include
Dim info()
at the beginning of the code;
ReDim info(Vars)
after the first comment line; and
info(Cols) = Var1
after the second comment line.

HTH!