greenbubble
New Member
- Joined
- Nov 13, 2022
- Messages
- 7
- Office Version
- 2003 or older
Hi,
Currently im using below VBA that is found in excel forum and its working fine for less data. I tried to run the VBA
for more than 2000 row of data it take some time to complete or most of the time it will crashed.
1 have 2 set of data:
1st set (Col:A,B,C,D)
2nd set Col:E,F,G,H)
I need to compare col A with col E data vise versa, matching by offset either 1st set or 2nd set.
i try to search for the solution and stuck with it. I expect help from you for my problem
Sub compare_c ()
Dim oLeft As Range
Dim oRight As Range
Set oLeft = Sheets("Summary").Range("A1,B1,C1,D1")
Set oRight = Sheets("Summary").Range("E1,F1,G1,H1")
Do Until oLeft.Value = "" Or oRight.Value = ""
If oLeft.Value = oRight.Value Then
Set oLeft = oLeft.Offset(1, 0)
Set oRight = oRight.Offset(1, 0)
ElseIf oLeft.Value > oRight.Value Then
oLeft.Insert Shift:=xlShiftDown
Set oRight = oLeft.Offset(0, 4)
Else
oRight.Insert Shift:=xlShiftDown
Set oLeft = oRight.Offset(0, -4)
End If
Loop
End Sub
Currently im using below VBA that is found in excel forum and its working fine for less data. I tried to run the VBA
for more than 2000 row of data it take some time to complete or most of the time it will crashed.
1 have 2 set of data:
1st set (Col:A,B,C,D)
2nd set Col:E,F,G,H)
I need to compare col A with col E data vise versa, matching by offset either 1st set or 2nd set.
i try to search for the solution and stuck with it. I expect help from you for my problem
Sub compare_c ()
Dim oLeft As Range
Dim oRight As Range
Set oLeft = Sheets("Summary").Range("A1,B1,C1,D1")
Set oRight = Sheets("Summary").Range("E1,F1,G1,H1")
Do Until oLeft.Value = "" Or oRight.Value = ""
If oLeft.Value = oRight.Value Then
Set oLeft = oLeft.Offset(1, 0)
Set oRight = oRight.Offset(1, 0)
ElseIf oLeft.Value > oRight.Value Then
oLeft.Insert Shift:=xlShiftDown
Set oRight = oLeft.Offset(0, 4)
Else
oRight.Insert Shift:=xlShiftDown
Set oLeft = oRight.Offset(0, -4)
End If
Loop
End Sub