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]

And what if the user chooses a range that consists of more than 26 cells?

 

[aavvijit]

Then it should start repeating the sequence once again.

Like (AA, AB, AC, AD .............. & so on)

Hope I am clear to explain.

Thanks,

 

[GlennUK]

No, that is not clear, as the sample you have shown is NOT a repeat of the original sequence. Can you explain what you want?

 

[aavvijit]

you better take the instance upto 26 alphabet now, we will restrict the user to use upto 26 only.

if need arise we look beyound that in future.

 

[GlennUK]

This does what you want ( and you'll see that it's almost identical to your first macro ... I can't understand how you couldn't do this yourself, to be honest ):

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 = Application.InputBox(Prompt, Title, Default, Type:=8)
    If rangeSelected Is Nothing Then Exit Sub
    UpperCase = True
    Randomize
    If rangeSelected.Cells.Count > 26 Then _
        MsgBox "Only first 26 cells with be filled"
    alphapointer = 0
    For Each Cell In rangeSelected
        alphapointer = alphapointer + 1
        If alphapointer > 26 Then Exit For
        CellChars = Chr(64 + alphapointer)
        If Not UpperCase Then CellChars = LCase(CellChars)
        Cell.Value = CellChars
    Next
End Sub

... it displays a message warning the user that only the first 26 cells will be filled, should they select more than 26 cells.

 

[aavvijit]

Thanks

I somehow missing the alphapointer.

Anyway thaks very much for help.

Regards,

 

[Ruddles]

Something like this:-

Dim rng As Range
Dim iChr As Integer
Dim oCell As Range
 
Set rng = Application.InputBox("Select a range to fill:-", , Selection.Address, , , , , Type:=8)
iChr = 64
For Each oCell In rng
  iChr = iChr + 1
  oCell = Chr(iChr)
Next oCell