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

Shane A. Workman
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.

user3598756

you are not using the current row index

use this:

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Formula returning Column A value for row containing MAX value of a range

From Dev

Line after first row and column in HTML table

From Dev

Returning first row of group

From Dev

First Empty Row in Google Script with dinamic column range

From Dev

First Empty Row in Google Script with dynamic column range

From Dev

vba macro - find first blank column from specific row range

From Dev

SQL returning 1 single column value on a multiple row

From Dev

MySQL Error: Out of range value for column 'amount' at row 1

From Dev

MYSQL IN only returning first row

From Dev

Head First C# xaml designer column/row sizes 1*

From Dev

How to exclude first row from sorting out Excel column Sort method of Excel.Range

From Dev

excel VBA function to select the first row in a column and it will will select all cells below it and make it range

From Dev

1265 data truncated for column at row 1 after few values

From Dev

Using Dynamic Row And Column in Range

From Dev

C# Gridview first row of data not returning

From Dev

Stored procedure returning only first row

From Dev

C# Gridview first row of data not returning

From Dev

MySQL JOIN: Select a row if a bool column is 1, otherwise select the first row

From Java

Convert row names into first column

From Dev

Searchng a sheet for a value and returning row & column numbers

From Java

Python Range Problem For Using Jype1 and can not use for loop every column and row

From Dev

PDO PHP - PDOException - Numeric value out of range: 1264 Out of range value for column 'customer_id' at row 1

From Dev

Insert data row-wise instead of column-wise and 1 blank row after each record

From Dev

finding first empty row to adjust the range in formal

From Dev

Exclude a row after returning it from a subquery mysql

From Java

Getting first element and returning after apply a function

From Dev

Getting first element and returning after apply a function

From Dev

I want to check if first column contains textbox1 and display full row in listview

From Dev

Convert first row as column name in a list of dataframes, then delete first row

Related Related

  1. 1

    Formula returning Column A value for row containing MAX value of a range

  2. 2

    Line after first row and column in HTML table

  3. 3

    Returning first row of group

  4. 4

    First Empty Row in Google Script with dinamic column range

  5. 5

    First Empty Row in Google Script with dynamic column range

  6. 6

    vba macro - find first blank column from specific row range

  7. 7

    SQL returning 1 single column value on a multiple row

  8. 8

    MySQL Error: Out of range value for column 'amount' at row 1

  9. 9

    MYSQL IN only returning first row

  10. 10

    Head First C# xaml designer column/row sizes 1*

  11. 11

    How to exclude first row from sorting out Excel column Sort method of Excel.Range

  12. 12

    excel VBA function to select the first row in a column and it will will select all cells below it and make it range

  13. 13

    1265 data truncated for column at row 1 after few values

  14. 14

    Using Dynamic Row And Column in Range

  15. 15

    C# Gridview first row of data not returning

  16. 16

    Stored procedure returning only first row

  17. 17

    C# Gridview first row of data not returning

  18. 18

    MySQL JOIN: Select a row if a bool column is 1, otherwise select the first row

  19. 19

    Convert row names into first column

  20. 20

    Searchng a sheet for a value and returning row & column numbers

  21. 21

    Python Range Problem For Using Jype1 and can not use for loop every column and row

  22. 22

    PDO PHP - PDOException - Numeric value out of range: 1264 Out of range value for column 'customer_id' at row 1

  23. 23

    Insert data row-wise instead of column-wise and 1 blank row after each record

  24. 24

    finding first empty row to adjust the range in formal

  25. 25

    Exclude a row after returning it from a subquery mysql

  26. 26

    Getting first element and returning after apply a function

  27. 27

    Getting first element and returning after apply a function

  28. 28

    I want to check if first column contains textbox1 and display full row in listview

  29. 29

    Convert first row as column name in a list of dataframes, then delete first row

HotTag

Archive