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.
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.
Comments