Share via

Destination reference is not valid

Anonymous
2014-10-22T23:31:46+00:00

I am trying to figure out why I am getting the above error all of a sudden. This sub was working fine as of this afternoon now I can't get past the highlighted section of the code below.

Any ideas?

Sub convert_textTOnumber(ws As Worksheet)

Dim colLtr As String

'==============================================================================

    With ws

        .Select

        prlCol = .Cells(1, Columns.Count).End(xlToLeft).Column

        prlRow = .Cells(Rows.Count, "A").End(xlUp).Row

        For Each c In .Range(Cells(1, 1), Cells(1, prlCol)).Cells

            If c.Value = "Project #" Or c.Value = "Existing PO#" Or c.Value = "Purchase Request #" Or c.Value = " Requisition #" Or c.Value = "PO #" Then

                colLtr = findcolumn(c.Value, ws, 1, True)

                .Range(colLtr & "2:" & colLtr & prlRow).Select

                MsgBox "colLtr: " & colLtr

                Selection.TextToColumns Destination:=.Range(colLtr & "2"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True

            End If

        Next c

    End With

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-10-23T18:07:44+00:00

    Thanks for the response. I tried and I couldn't figure out the problem so I re-wrote the sub using .Formula, everything is working fine again:

    Sub convert_textTOnumber(ws As Worksheet)

    Dim colLtr As String, testCol As String

    '==============================================================================

        With ws

            .Select

            prlCol = .Cells(1, Columns.Count).End(xlToLeft).Column

            prlRow = .Cells(Rows.Count, "B").End(xlUp).Row

            .Cells(1, prlCol + 1) = "TEST"

            testCol = findcolumn("TEST", ws, 1, True)

            For Each c In .Range(Cells(1, 1), Cells(1, prlCol)).Cells

                If c.Value = "Project #" Or c.Value = "Existing PO#" Or c.Value = "Purchase Request #" Or _

                   c.Value = " Requisition #" Or c.Value = "PO #" Or c.Value = "Line #" Or c.Value = "QTY" Then

                    colLtr = findcolumn(c.Value, ws, 1, True)

                    fillrange = .Range(testCol & "2:" & testCol & prlRow).Address

                    .Range(testCol & "2").Formula = "=IF(" & colLtr & "2="""","""",VALUE(" & colLtr & "2))"

                    .Range(testCol & "2").AutoFill .Range(fillrange), xlFillDefault

                    .Range(fillrange).Calculate

                    .Range(fillrange).Value = .Range(fillrange).Value

                    .Range(fillrange).Copy

                    .Cells(2, c.Column).PasteSpecial xlValue

                End If

            Next c

            fltrCol = findcolumn("TEST", ws, 1, False)

            Columns(fltrCol).Select

            Selection.Delete Shift:=xlToLeft

            .Range("B2").Select

        End With

    End Sub

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-10-23T16:25:51+00:00

    Re: text to columns error

    Two possibilities:

    "findcolumn" is questionable...

       colLtr = findcolumn(c.Value, ws, 1, True)

    Is the Selection appropriate...

       Selection.TextToColumns Destination:=

    '---

    Jim Cone

    Portland, Oregon USA

    free & commercial excel programs  (n/a xl2013)

    https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

    or

    http://jmp.sh/K95N3ee

    Was this answer helpful?

    0 comments