Range("IV1").End(xlToLeft).Column Returning 1 after first row

Question

Sub UpdateText()
' The purpose of this Macro is to set up an excel spreadsheet in order for
' it to be properly mapped to be exported into an CRM application.
' Some variables to use.
Dim fName As String
Dim lName As String
Dim LastRow As Long
Dim LastCell As Long
Dim i As Long
Dim x As Long
' Get the last Row in the worksheet.
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
' Start the loop for each row.
For i = 1 To LastRow
    ' Grab how many cells that are in the current row.
    LastCell = Range("IV1").End(xlToLeft).Column ' <---- My Error is here. After first row, it returns 1 everytime.
    ' Start the loop through each Cell.
    For x = 1 To LastCell
        ' Look for the consistant tag for each technician to grab Name.
        If ActiveCell = "Location" Then
            lName = ActiveCell.Offset(0, -2).Value
            fName = ActiveCell.Offset(0, -1).Value
            ' Skip to the next cell.
            ActiveCell.Offset(0, 1).Select
        ' Check for the Service to drop in the name of the Technician.
        ElseIf ActiveCell.Value Like "HC:H*" Then
            ' Step back 1 cell to drop before date of service.
            ActiveCell.Offset(0, -1).Select
            ' Insert 3 cells.
            Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            ' drop in data.
            ActiveCell.Value = lName
            ActiveCell.Offset(0, 1).Value = fName
            ActiveCell.Offset(0, 2).Value = "Location"
            ' Go back to the next cell that should be checked.
            ActiveCell.Offset(0, 5).Select
        ' Delete the blank cells that seperate the next technician group.
        ElseIf ActiveCell.Value = "" Then
            Selection.Delete Shift:=xlToLeft
        ' If nothing is to do done, Skip to next cell.
        Else
            ActiveCell.Offset(0, 1).Select
        End If
    Next x
    ' End of row, go to the beginning of next row to start.
    Cells(i + 1, 1).Select
Next i
End Sub

On line 16

LastCell = Range("IV1").End(xlToLeft).Column

Returns a value of 1 every row after the first row. I cannot understand why. The purpose of this macro is to format the spreadsheet so that each column has consistent data so that it can be mapped and imported into a CRM file. I need to step into each cell going left to right check the value and either grab the value or insert cells and drop in the values grabbed.

Are you clearing the data from row 1? I do see a delete in there, but it's hard to follow because you're using `ActiveCell` and `Selection` a lot. I recommend taking a look at [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to clean up your code before doing anything else. — tigeravatar, Oct 12 '16 at 14:51

score 1 · Accepted Answer · answered Oct 12 '16 at 14:52

1

you are not using the current row index

use this:

LastCell = Cells(i, "IV").End(xlToLeft).Column

answered Oct 12 '16 at 14:52

user3598756

28,893
4
18
28

This almost fixed my issue. It now selectively returns 1 when running a 2nd set of for loops to re-scan the document again to delete some duplicate entries. Any idea as to why? – Shane A. Workman Oct 12 '16 at 16:30
that is much probably the _heritage_ of all those Selection/Activation, which good coding practice cares to avoid. just follow @tigeravatar comment link about how to avoid that and if you still have issues post a new question with your code efforts – user3598756 Oct 12 '16 at 16:38

Range("IV1").End(xlToLeft).Column Returning 1 after first row

1 Answers1