Run TIme Error 9, Subscript out of Range

[chipsworld]

Hey All...

I know I am missing a period or comma or something in the below, but can not figure it out. Any help would be greatly appreciated

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim cell As Range
    Dim oldChar As String
    Dim newChar As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Delete Analysis")

    ' Set the table
    Set tbl = ws.ListObjects("Table1") 'Set table name from sheet

    oldChar = "/" ' Character to be replaced
    newChar = "-" ' New character

    ' Loop through each cell in column F of the table
    For Each cell In tbl.ListColumns("F").DataBodyRange
        If Not IsEmpty(cell) Then
            cell.Value = Replace(cell.Value, oldChar, newChar)
        End If
    Next cell

 

[Joe4]

Does it give you a "Debug" button option at the bottom of the error message?
If so, click it and see what line it returns. That is the line it is have problems with.

Also, please answer the following questions:
1. Where is this VBA code stored? Is it stored in the workbook you are running it against?
2. Are you sure that your sheet is named "Delete Analysis", and the sheet name has no extra spaces at the beginning or end of it?
3. Are you sure that you have a table named "Table1" on your sheet?

 

[chipsworld]

Does it give you a "Debug" button option at the bottom of the error message?
If so, click it and see what line it returns. That is the line it is have problems with.

Also, please answer the following questions:
1. Where is this VBA code stored? Is it stored in the workbook you are running it against?
2. Are you sure that your sheet is named "Delete Analysis", and the sheet name has no extra spaces at the beginning or end of it?
3. Are you sure that you have a table named "Table1" on your sheet?

Joe,
No Debug..Just an OK button. (Frustrating!)

VBA is in the sheet it is being used on
Yes, Sheet is Named Delete Analysis and there are no extra characters at the beginning or end
Yes...I re-named the Table to Table1 just to make it simple. II did have the same failure prior to the name change, however.

I have always been able to find these types of things in the past, but this one is making my head hurt, so thought I would ask the pros!

It is not that complicated. I just need to replace one character with another in a table column...

 

[Joe4]

What if you try stepping into the code, and progressing through it one line at a time using the F8 key, and seeing where it errors out?

Also, is there anything anywhere else in column F that you DON'T want to be affected by these updates?
If so, then maybe just do a simple Find/Replace on the whole column F instead of just trying to limit it to your table.

 

[chipsworld]

What if you try stepping into the code, and progressing through it one line at a time using the F8 key, and seeing where it errors out?

Also, is there anything anywhere else in column F that you DON'T want to be affected by these updates?
If so, then maybe just do a simple Find/Replace on the whole column F instead of just trying to limit it to your table.

I have tried stepping into it, but it fails before anything runs. I hit F8 and I get the pop-up immediately.

I have thought of just using Substitute and doing it that way, but the table is generated from a query, and I want to output certain columns from the table to a new sheet that will be used to upload into a system. Just trying to automate the whole process into a simple button that "anyone" (users) can use to create the system upload. Eliminate the opportunity for errors as much as possible.

Not sure, but maybe I could output the data from the table into a new sheet as a range first and then run some code to make the character swap. Would be a much simpler code and no "Tables" to deal with.
I can't help but think that the table is the issue somehow.

 

[Joe4]

Regardless of whether the data is in a table or not, this block of code will automatically replace all "/" found in column F with "-".

Sub MyReplaceF()
    Sheets("Delete Analysis").Activate
    Columns("F:F").Replace What:="/", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Could you just use that instead?

 

[chipsworld]

Regardless of whether the data is in a table or not, this block of code will automatically replace all "/" found in column F with "-".

Sub MyReplaceF()
    Sheets("Delete Analysis").Activate
    Columns("F:F").Replace What:="/", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Could you just use that instead?

Joe,
That will fit the bill! Thanks...Still bugs me why the other code isn't working! LOL

 

[Joe4]

You are welcome.
Glad I was able to help!

When I try your code, I get the error, but I get the Debug option, and it highlights this line of code:

    For Each cell In tbl.ListColumns("F").DataBodyRange

I tried re-writing your code using the methodology you initially tried, and got it to work like this:

Sub MyMacro()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim myArray As Variant
    Dim cell As Range
    Dim oldChar As String
    Dim newChar As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Delete Analysis")

    ' Set the table
    Set tbl = ws.ListObjects("Table1") 'Set table name from sheet
    Set myArray = tbl.ListColumns(1).Range

    oldChar = "/" ' Character to be replaced
    newChar = "-" ' New character

    ' Loop through each cell in column H of the table
    For Each cell In myArray
        If Not IsEmpty(cell) Then
            cell.Value = Replace(cell.Value, oldChar, newChar)
        End If
    Next cell
  
End Sub

Note that the number "1" on this line may need to change:

    Set myArray = tbl.ListColumns(1).Range

The number represents the column reference within your table.
In my example, column F is the first column in Table1.
If your first column in Table1 is column A, then you would need to change this number to 6.

Also note, even though this works, I would probably not use this method.
All things equal, it is generally advised to avoid using Loops in VBA code when better options exist.
Loops are notoriously slow and inefficient.
If you do not have that many rows in your table, you probably wouldn't notice. But if the tables could have thousands of rows, you may notice some slow behavior.

 

[chipsworld]

You are welcome.
Glad I was able to help!

When I try your code, I get the error, but I get the Debug option, and it highlights this line of code:

    For Each cell In tbl.ListColumns("F").DataBodyRange

I tried re-writing your code using the methodology you initially tried, and got it to work like this:

Sub MyMacro()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim myArray As Variant
    Dim cell As Range
    Dim oldChar As String
    Dim newChar As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Delete Analysis")

    ' Set the table
    Set tbl = ws.ListObjects("Table1") 'Set table name from sheet
    Set myArray = tbl.ListColumns(1).Range

    oldChar = "/" ' Character to be replaced
    newChar = "-" ' New character

    ' Loop through each cell in column H of the table
    For Each cell In myArray
        If Not IsEmpty(cell) Then
            cell.Value = Replace(cell.Value, oldChar, newChar)
        End If
    Next cell
  
End Sub

Note that the number "1" on this line may need to change:

    Set myArray = tbl.ListColumns(1).Range

The number represents the column reference within your table.
In my example, column F is the first column in Table1.
If your first column in Table1 is column A, then you would need to change this number to 6.

Also note, even though this works, I would probably not use this method.
All things equal, it is generally advised to avoid using Loops in VBA code when better options exist.
Loops are notoriously slow and inefficient.

You go Joe! Why did I not think of using an Array? LOL

This rocks...a few extra lines, but with a solid result!

Thanks...this is a much cleaner way to do it for sure.

 

[Joe4]

You are welcome!