Input Box Trouble

[Philip1957]

Greetings,

This is my first attempt at using the Input Box Method.

I have this piece of code that strips away extraneous characters and leaves just the serial number:

Option Explicit

Sub Extract_Count()
Application.ScreenUpdating = False

'Change Column Headings
ActiveSheet.Range("B1").Select
    ActiveCell.Value = "Assy #"
ActiveSheet.Range("C1").Select
    ActiveCell.Value = "Sta #"
ActiveSheet.Range("D1").Select
    ActiveCell.Value = "Date"

' Trim Away all but S/N
Range("A" & Rows.Count).End(xlUp).Select

Do Until ActiveCell.Address = "$A$1"
    ActiveCell = Right(ActiveCell, Len(ActiveCell) - 55)
    ActiveCell = Left(ActiveCell, Len(ActiveCell) - 4)

    ActiveCell.Offset(-1, 0).Select
Loop

Application.ScreenUpdating = True
End Sub

The serial number data may not always be in column A however, so I'm trying to use the Input Box to allow the user to specify the column.

I've tried various permutations of the following with no luck:

Option Explicit

Sub Clean_Serial_Numbers()
Dim myCol As Range
Set myCol = Application.InputBox("Select the column with the serial number data.", Type:=8)

Range("myCol" & Rows.Count).End(xlUp).Select

Do Until ActiveCell.Address = "$myCol$1"
    ActiveCell = Right(ActiveCell, Len(ActiveCell) - 55)
    ActiveCell = Left(ActiveCell, Len(ActiveCell) - 4)

    ActiveCell.Offset(-1, 0).Select
Loop

End Sub

This hangs at the Range statement before the Do Until, with an error message of Run-time error '1004': Method 'Range' of 'object_Global' failed.

I'm also unclear if the user should select the whole column, a cell in that column, or just input the letter.

Any assistance would be greatly appreciated.

Thanks,
~ Phil

 

[Joe4]

Avoid using ".Select" statements where possible. It is inefficient and slows your code down.

See if those does what you want:

Sub Clean_Serial_Numbers()
    
    Dim myCol As Range
    Dim lastRow As Long
    Dim r As Long
    Dim c As Long
    Dim myCell As Range

    Application.ScreenUpdating = False

'   Prompt user to type in cell address
    Set myCol = Application.InputBox("Select the column with the serial number data.", Type:=8)

'   Capture column number
    c = myCol.Column

'   Find row of populated cell in the column they selected
    lastRow = Cells(Rows.Count, c).End(xlUp).Row

'   Loop through all rows from 1 to last row
    For r = 1 To lastRow
        Set myCell = Cells(r, c)
        myCell = Right(myCell, Len(myCell) - 55)
        myCell = Left(myCell, Len(myCell) - 4)
    Loop

    Application.ScreenUpdating = True

End Sub

 

[Philip1957]

Joe,

Thanks for the rapid response. I think kind of understand what your doing here ...

I had to change "Loop" to "Next r" in order to get it to compile but then when I run it I get a Run-time error '5': Invalid procedure call or argument at the first trim instruction in the For statement (myCell = Right ...), which I don't understand at all because it works fine in my first application without the Input Box.

Thanks,
~ Phil

 

[Joe4]

What are you entering in for the Input Box value?

 

[Philip1957]

I've tried selecting a cell in the target column and selecting the whole column. Neither works.

 

[Joe4]

I think that type of Input Box you set up wants a typed in value.
If I type in the address of the cell, it seems to work for me.

Note that when I want to do something like this, I often use the following method.
I usually tell them to select the range they want BEFORE running the macro, and then just run the code against the Selected range.
So, instead of an InputBox at the beginning, I have a MsgBox asking them "Have the selected the proper range you want this to run against" If not, select "No", select your range, and run this macro again."

 

[Philip1957]

I got it!

Option Explicit

Sub Clean_Serial_Numbers()

Application.ScreenUpdating = False

On Error Resume Next

Dim clmn As String
clmn = Application.InputBox("Enter the letter designating the column where the serial number data is located.")

Range(clmn & Rows.Count).End(xlUp).Select

Do Until ActiveCell.Row = "1"
    ActiveCell = Right(ActiveCell, Len(ActiveCell) - 55)
    ActiveCell = Left(ActiveCell, Len(ActiveCell) - 4)

    ActiveCell.Offset(-1, 0).Select
Loop

Application.ScreenUpdating = True

End Sub