Hello,
I have an urgent question regarding Excel VBA. I have a Macro which is supposed to convert the numbers entered in sheet Inputs!C3 (and ranging between 1 & 180) into column headers, starting from Column B to Column 182; eg if I enter 25, the Macro correctly converts it into the letter Z (i.e. the 26th letter, since I want it to start counting from "B"), if I enter 55 in cell C3 of sheet Inputs, the macro converts it into the string BD
The string is then used by a formula to define a range
Sub RandomNumbers()
Sheets("Inputs").Calculate
Dim m As String
z = Sheets("Inputs").Range("C3
").Value
If z <= 26 Then
m = Chr(65 + z)
ElseIf z <= 52 Then
m = "A" & Chr(65 + (z - 26))
ElseIf z <= 78 Then
m = "B" & Chr(65 + (z - 2 * 26))
ElseIf z <= 104 Then
m = "C" & Chr(65 + (z - 3 * 26))
ElseIf z <= 130 Then
m = "D" & Chr(65 + (z - 4 * 26))
ElseIf z <= 156 Then
m = "E" & Chr(65 + (z - 5 * 26))
ElseIf z <= 182 Then
m = "F" & Chr(65 + (z - 6 * 26))
End If
k = Range("Inputs!C14").Value
For i = 2 To k + 1
Range("B" & i & ":" & m & i).FormulaArray = "=TRANSPOSE(RandBM(Inputs!
R3C3))"
Next i
End Sub
The problem now is as follows: the Macro works fine for any values between 1 & 182 EXCEPT for multiples of 26:
i.e. every time I insert 26, 52,78, 104, 130, 156, 182, in cell C3 of sheet inputs, the Macro returns following error message:
"Run-time error '1004',
Method 'Range of Object_Global' failed"
The Debugger then highlights the following line of the Macro, which refers to the Range :
Range("B" & i & ":" & m & i).FormulaArray = "=TRANSPOSE(RandBM(Inputs!
R3C3))"
Apparantly, the Macro as it is cannot convert any multiples of 26 into the corresponding characters (for instance, 26 is supposed to be converted into AA, 52 into BA etc) ...any help on how to solve this issue would be greatly appreciated!
Many thanx!
Burki
Start Free Trial
