Valou Choc
10-28-2013, 07:24 AM
Hi Guys,
I have an issue with a VBA macro that I am writing. I will try to explain it as good as I can, feel free to ask me questions.
I have a Database with about 3000 lines and I try to cut some of the lines and paste them into another tab.
In TablColG, I have all the data from my database. In List_IndDesc_Excl, I have all the criteria of the lines I would like to remove from the database.
If in TabColG, the element contains one of the element in List_IndDesc_Excl, I cut this line and paste it into a tab called "Deleted".
In my Database and Tab Deleted, I have 6 rows of header before finding the data.
Here is the code I wrote:
For Lig = LBound(TablColG) + 1 To UBound(TablColG)
For cpt = LBound(List_IndDesc_Excl) To UBound(List_IndDesc_Excl)
If InStr(TablColG(Lig), List_IndDesc_Excl(cpt)) <> 0 Then
ActiveSheet.Range("G" & Lig + 6 - LigDelete).EntireRow.Cut Destination:=Sheets("Deleted").Range("A" & LigDelete + 7)
Range("G" & Lig + 6 - LigDelete).EntireRow.Delete
LigDelete = LigDelete + 1
Else
Range("G" & Lig + 6 - LigDelete).EntireRow.Select
End If
Next cpt
Next Lig
Lig is going through my database; cpt through the list of criteria; If the criteria is met, it select the row number Lig+6-Ligdelete (Cause I have 6 rows of header and I remove the lines already cut) and I paste this line in my other tab line Ligdelete+7 (Due to the header as well and the lines already cut/paste)
I wrote an else part to this if cause I wanted to make sure that the macro is actually going through all the lines and it does.
This is not working properly, it does check all elements, it finds all the one to delete BUT from row 200 around, it starts cutting the row located 2 rows above the one it should actually remove.
I guess I have an issue with my index, however I do not get why it works until a certain point. All variable above are declared as Long so I shouldn't have an issue with having too many digits.
Let me know if you have any idea about my issue... Hopefully I missed something!
Thanks a lot!!!!
I have an issue with a VBA macro that I am writing. I will try to explain it as good as I can, feel free to ask me questions.
I have a Database with about 3000 lines and I try to cut some of the lines and paste them into another tab.
In TablColG, I have all the data from my database. In List_IndDesc_Excl, I have all the criteria of the lines I would like to remove from the database.
If in TabColG, the element contains one of the element in List_IndDesc_Excl, I cut this line and paste it into a tab called "Deleted".
In my Database and Tab Deleted, I have 6 rows of header before finding the data.
Here is the code I wrote:
For Lig = LBound(TablColG) + 1 To UBound(TablColG)
For cpt = LBound(List_IndDesc_Excl) To UBound(List_IndDesc_Excl)
If InStr(TablColG(Lig), List_IndDesc_Excl(cpt)) <> 0 Then
ActiveSheet.Range("G" & Lig + 6 - LigDelete).EntireRow.Cut Destination:=Sheets("Deleted").Range("A" & LigDelete + 7)
Range("G" & Lig + 6 - LigDelete).EntireRow.Delete
LigDelete = LigDelete + 1
Else
Range("G" & Lig + 6 - LigDelete).EntireRow.Select
End If
Next cpt
Next Lig
Lig is going through my database; cpt through the list of criteria; If the criteria is met, it select the row number Lig+6-Ligdelete (Cause I have 6 rows of header and I remove the lines already cut) and I paste this line in my other tab line Ligdelete+7 (Due to the header as well and the lines already cut/paste)
I wrote an else part to this if cause I wanted to make sure that the macro is actually going through all the lines and it does.
This is not working properly, it does check all elements, it finds all the one to delete BUT from row 200 around, it starts cutting the row located 2 rows above the one it should actually remove.
I guess I have an issue with my index, however I do not get why it works until a certain point. All variable above are declared as Long so I shouldn't have an issue with having too many digits.
Let me know if you have any idea about my issue... Hopefully I missed something!
Thanks a lot!!!!