Macro named argument - Error

[aavvijit]

Hi,

Can some please help me to correct this mcro code - this I am using to fill a range with alphabatical list.

Sub AlphaFill()
    Dim Cell, CellChars
    Dim Default, Prompt, Title
    Dim rangeSelected As Range
    Dim UpperCase As Boolean
    Title = "AlphaFill Cell Selection"
    Default = Selection.Address
    Prompt = vbCrLf _
      & "Use mouse in conjunction with " _
      & "SHIFT and CTRL keys to" & vbCrLf _
      & "click and drag or type in name(s) " _
      & "of cell(s) to AlphaFill" & vbCrLf & vbCrLf _
      & "Currently selected cell(s): " & Selection.Address
    On Error Resume Next
    Set rangeSelected = InputBox(Prompt, Title, Default, Type:=8)
    If rangeSelected Is Nothing Then Exit Sub
    UpperCase = True
    Randomize
    For Each Cell In rangeSelected
        CellChars = Chr(64 + Int((Rnd * 26) + 1))
        If Not UpperCase Then CellChars = LCase(CellChars)
        Cell.Value = CellChars
    Next
End Sub

Above code is giving error "Named argument error" on following line : "Type:=8"

I am using Excel 2003.

Please help where the code is going wrong.

Regards,

 

[GlennUK]

I believe that you are trying to use the Application.InputBox Method, but are instead calling the InputBox function.

Put Application. in front of the word InputBox.

 

[aavvijit]

Thanks Glenn,

It is wonderfully solved, Can I request another small issue.

I want to put "the message box" promt to select range in this macro, Please help how to do that.

Public Sub Autofill()
Dim A As Long
Dim B As Long
Dim C As Long
Dim s As String
Dim row As Long
Dim rangeSelected As Range

Application.ScreenUpdating = False
With Selection
For A = 65 To 90
           row = row + 1
           s = Chr(A) & Chr(B) & Chr(C)
           Range("M" & CStr(row)).Value = s
Next
Application.ScreenUpdating = True
End With
End Sub

Thanks,

 

[GlennUK]

Aren't you asking how to do exactly what your previous macro does?

 

[aavvijit]

yes, But some difference,

My previous macro put the alphabet in a randomly manner in the selected range, but with the next macro I want to put the alphabet in a sequential manner in the selected range.

Thant means I want to put like this - A-B-C-D........ in the selected rows.

Please help.

 

[GlennUK]

What I meant was, your request was for help

to put "the message box" promt to select range

You already know how to do that, because you are already doing that in the previous macro.

 

[aavvijit]

Please Help,

I am not able to put execute the message box promt in my 2nd macro.

Thanks,

 

[GlennUK]

Let me just try to get things clear ...

1) do you have a macro, named Autofill, in which you prompt the user to select a range?

2) are you saying that you do not know how to prompt the user for a range in a second macro?

 

[Ruddles]

I think he's saying he needs an Application.InputBox with a prompt of "the message box", type:=8, then he wants code to fill each cell in turn with "A", "B", "C", etc.

 

[aavvijit]

Yes, Mr. Ruddles is right,

In my second macro I want to put a message box prompt which will fill the alphabet (A, B, C, D ...........) in a sequential manner in the selected range.

Thanks.