MACRO VBA Code Fails with only one line of data

[LNG2013]

Hey Everyone,

My code is failing when there is only one line/row of data in the csv. Otherwise it succeeds flawlessly.

The code originially comes from this thread:
http://www.mrexcel.com/forum/showthread.php?t=553033

ALL HELP IS VERY VERY APPRECIATED!

The code is below:

Sub foo()

Dim Found1 As Long, Found2 As Long, Found3 As Long, Found4 As Long, Found5 As Long, Found6 As Long, Found7 As Long
Dim lastcol As Long, delcol As Long, lastrow As Long, i As Long
Dim Temp1 As String, Temp2 As String

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

lastcol = Last(2, .Cells)
lastrow = Last(1, .Cells)

For delcol = lastcol To 1 Step -1
If .Cells(1, delcol) = "Combo" Then .Cells(1, delcol).EntireColumn.Delete
Next

Found1 = FindColumn("ProdCode")
Found2 = FindColumn("ClientCode")
Found3 = FindColumn("Type")
Found4 = FindColumn("SubType")
Found5 = FindColumn("Month")
Found6 = FindColumn("Week")
Found7 = FindColumn("Year")

.Cells(1, lastcol + 1).Value = "combo"

.Cells(2, lastcol + 1).FormulaR1C1 = "=RC" & Found1 & "& "","" & RC" & Found2 & "& "","" & RC" & Found3 & "& "","" & RC" & Found4 & "& "","" & RC" & Found5 & "& "","" & RC" & Found6 & "& "","" & RC" & Found7
.Cells(2, lastcol + 1).AutoFill Destination:=Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1))

Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Calculate
Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Value = Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Value

End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

 

[kpark91]

It seems like it needs 2 rows to do what it's supposed to do.
I am guessing that your code isn't working only when there is a header row.


So, I would suggest inserting this code:
It will exit the code when there's only one line of code as no processing needs to be done.

Sub foo()
 
Dim Found1 As Long, Found2 As Long, Found3 As Long, Found4 As Long, Found5 As Long, Found6 As Long, Found7 As Long
Dim lastcol As Long, delcol As Long, lastrow As Long, i As Long
Dim Temp1 As String, Temp2 As String
 
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
 
With ActiveSheet
 
lastcol = Last(2, .Cells)
lastrow = Last(1, .Cells)
 
If lastrow < 2 Then Exit Sub
 
For delcol = lastcol To 1 Step -1
If .Cells(1, delcol) = "Combo" Then .Cells(1, delcol).EntireColumn.Delete
Next
 
Found1 = FindColumn("ProdCode")
Found2 = FindColumn("ClientCode")
Found3 = FindColumn("Type")
Found4 = FindColumn("SubType")
Found5 = FindColumn("Month")
Found6 = FindColumn("Week")
Found7 = FindColumn("Year")
 
.Cells(1, lastcol + 1).Value = "combo"
 
.Cells(2, lastcol + 1).FormulaR1C1 = "=RC" & Found1 & "& "","" & RC" & Found2 & "& "","" & RC" & Found3 & "& "","" & RC" & Found4 & "& "","" & RC" & Found5 & "& "","" & RC" & Found6 & "& "","" & RC" & Found7
.Cells(2, lastcol + 1).AutoFill Destination:=Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1))
 
Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Calculate
Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Value = Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Value
 
End With
 
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[LNG2013]

Hey Kpark,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you for your post. I was incorrect in my earlier post. There are actually 2 lines/rows of data. The first row is all of the headers; the second row is a row of data that coincides with the headers. When there are no additional rows filled in, no data past the second row, the code fails.

<o></o>

It seems like it needs 2 rows to do what it's supposed to do.
I am guessing that your code isn't working only when there is a header row.


So, I would suggest inserting this code:
It will exit the code when there's only one line of code as no processing needs to be done.

Sub foo()
 
Dim Found1 As Long, Found2 As Long, Found3 As Long, Found4 As Long, Found5 As Long, Found6 As Long, Found7 As Long
Dim lastcol As Long, delcol As Long, lastrow As Long, i As Long
Dim Temp1 As String, Temp2 As String
 
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
 
With ActiveSheet
 
lastcol = Last(2, .Cells)
lastrow = Last(1, .Cells)
 
If lastrow < 2 Then Exit Sub
 
For delcol = lastcol To 1 Step -1
If .Cells(1, delcol) = "Combo" Then .Cells(1, delcol).EntireColumn.Delete
Next
 
Found1 = FindColumn("ProdCode")
Found2 = FindColumn("ClientCode")
Found3 = FindColumn("Type")
Found4 = FindColumn("SubType")
Found5 = FindColumn("Month")
Found6 = FindColumn("Week")
Found7 = FindColumn("Year")
 
.Cells(1, lastcol + 1).Value = "combo"
 
.Cells(2, lastcol + 1).FormulaR1C1 = "=RC" & Found1 & "& "","" & RC" & Found2 & "& "","" & RC" & Found3 & "& "","" & RC" & Found4 & "& "","" & RC" & Found5 & "& "","" & RC" & Found6 & "& "","" & RC" & Found7
.Cells(2, lastcol + 1).AutoFill Destination:=Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1))
 
Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Calculate
Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Value = Range(.Cells(2, lastcol + 1), .Cells(lastrow, lastcol + 1)).Value
 
End With
 
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[kpark91]

Hi, LNG. I think replacing the red-colored text with

If lastrow < 3 Then Exit Sub

should do the trick.

Please keep in mind that this will not allow your remainder of the code to run.

The error is most likely due to lastcol + 1 parts of the code.