Unable to locate a range object with an incremented string variable inside a For... Next Loop-CodePudding

'Declaratives
Dim DataRows As Long
    DataRows = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Dim DataColumns As Long
    DataColumns = ActiveSheet.Cells(3, ActiveSheet.Columns.Count).End(xlToLeft).Column
Dim TitleLocation As Range
Dim TitleStringArray() As String
    TitleStringArray = Split("Location Code, Job Category, Email, ", ",")
Dim InsertedStringArray() As String
    InsertedStringArray = Split("Job Category, Email, ", ",")
Dim TitleCol As Long

'Placeholder'
    'inserting location code column'
    For i = 1 To 3
        Set TitleLocation = Worksheets(1).Range(Cells(3, 1), Cells(3, DataColumns)).Find(TitleStringArray(i - 1))
            TitleCol = TitleLocation.Column   1
            Columns(TitleCol).Select
            Selection.Insert Shift:=xlToRight       'to right of Title'
            Cells(3, TitleCol).Value = InsertedStringArray(i-1)
            DataColumns = ActiveSheet.Cells(3, ActiveSheet.Columns.Count).End(xlToLeft).Column 'updates column count'
    Next i

It works fine on the first pass, but on the second pass, the .Find(TitleStringArray(i-1)) doesn't return an object which causes the TitleCol = TitleLocation.Column 1 to return error 91.

Strangely enough, if I replace this code and use this instead, it works fine;

For i = 1 To 3
        Set TitleLocation = Worksheets(1).Range(Cells(3, 1), Cells(3, DataColumns)).Find(TitleStringArray(i - 1))
            TitleCol = TitleLocation.Column   1
            Columns(TitleCol).Select
            Selection.Insert Shift:=xlToRight       'to right of Title'
            Cells(3, TitleCol).Value = TitleStringArray(i)
            DataColumns = ActiveSheet.Cells(3, ActiveSheet.Columns.Count).End(xlToLeft).Column 'updates column count'
    Next i

Can anyone explain why?

CodePudding user response：

This worked for me:

Sub tester()
    'Declaratives
    Const TITLE_ROW As Long = 3 'use Const for fixed values
    Dim ws As Worksheet, TitleCol As Long, m, i As Long
    Dim Titles() As String, Inserts() As String

    Set ws = ActiveSheet
    Titles = Split("Location Code,Job Category,Email", ",")
    Inserts = Split("Job Category2,Email2,xxxx2", ",")

    For i = LBound(Titles) To UBound(Titles)
        m = Application.Match(Trim(Titles(i)), ws.Rows(TITLE_ROW), 0) 'find a match; use Trim() in case of spaces...
        If Not IsError(m) Then 'got a match?
            ws.Columns(m   1).Insert Shift:=xlToRight
            ws.Cells(TITLE_ROW, m   1).Value = Inserts(i)
        End If
    Next i
End Sub

CodePudding user response：

Answer provided by @VBasic2008

You don't want to use spaces between the strings and the delimiters (commas) with Split: TitleStringArray = Split("Location Code,Job Category,Email", ","). – VBasic2008

Thanks, this quick change made it run as I expected. I guess it's because it's searching for the string WITH the space in front of it.