Resolved Question: Excel VBA help, what is wrong with my code?
I keep throwing getting an error in my code, "Run-time Error '1004': Application-defined or object defined error" What I am trying to do is have Excel loop through all the cells in column 1, if the value of the cell starts with "Tel" it will check the next row to see if it has "Website" in it at all. If it doesnt, insert a new row. However, when I stepping through the program it errors out when trying to select the row. Appreciate any help.
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
If .Value Like "Tel:*" Then
ActCell = Lrow + 1
Range(ActCell).Select <---------------Error generated here
Dim s As String
Dim i As Integer
s = .ActiveCell.Value
i = InStr("Website", s)
If i = 0 Then
.EntireRow.Insert
End If
End If
End If
End With
Next Lrow
Thanks garbo, however I tried changing it to Cells(ActCell, "A").Select but it still gives me the same error in the same place.
BTW ActCell is declared as an integer, is this ok?
Thanks AJ, that worked great, kinda. :/ The code didnt generate any error, and it added a new row after the row with "Tel" however, it did it to every row after "Tel" whether that row had website in it or not. Any ideas?
Ok, a bit of tinkering and I got it to work, final code:
Dim iRow As Integer
Dim iLastRow As Integer
Dim strX As String
iLastRow = 5000
For iRow = 1 To iLastRow
Cells(iRow, 1).Select
If Left(ActiveCell.Text, 3) = "Tel" Then
strX = ActiveCell.Offset(1, 0).Text
If Left(strX, 7) <> "Website" Then
Cells(iRow + 1, 1).Select
Selection.EntireRow.Insert
iLastRow = iLastRow + 1
End If
End If
Next
I had to give best answer to AJ, since he got me on the right track.
