Hi VBA gurus,
When i run my code, the application status shows the count but stops after a short while and it just turns to "Not Responding". Could it be that my code is inefficient? I'm not an expert at vba as its all mostly self-taught. please someone help!!!
Alittle background:- the code is found on a master workbook (which i leave open in the background), I then open a separate workbook each time to run the macro.
- the data starts from row 4 in each separate workbook.
- for each row of data found in a separate workbook- 2 points are used to trigger a match in the "ref" sheet of the master workbook. Namely: the active workbook's name and one other data points (sort of like a sumproduct/ 2 intersection)
- once the match is found, other data from the "ref" sheet is then copied over to the active workbook. and a string is introduced to another cell in the row as well
Is there a more efficient way to do this??
btw, i am running close to 25000 rows of data in one instance. and i have to do this for 15~20 separate workbooks
Sub Matcher()
Dim x, y, z As Long
Dim wsf As WorksheetFunction
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wsf = Application.WorksheetFunction
lrow = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For x = 4 To lrow
For y = 2 To ThisWorkbook.Worksheets("Ref").Cells(Rows.Count, 1).End(xlUp).Row
If Replace(ActiveWorkbook.Name, ".xlsx", "") = ThisWorkbook.Worksheets("Ref").Cells(y, 1) And ActiveWorkbook.Sheets(1).Cells(x, 5) = ThisWorkbook.Worksheets("Ref").Cells(y, 16) Then
ActiveWorkbook.Sheets(1).Cells(x, 96) = ThisWorkbook.Worksheets("Ref").Cells(y, 4)
ActiveWorkbook.Sheets(1).Cells(x, 97) = ThisWorkbook.Worksheets("Ref").Cells(y, 8)
With ActiveWorkbook.Sheets(1)
.Cells(x, 108) = Replace(ActiveWorkbook.Name, ".xlsx", "") & "-" & .Cells(x, 107) & "-" & .Cells(x, 96)
.Cells(x, 14).Copy Destination:=Cells(x, 105)
End With
End If
Next y
Application.StatusBar = "Updating " & x & "of " & lrow & "(" & Format(x / lrow, "0%") & ")"
Application.StatusBar = False
Next x
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox ("Done")
End Sub
Bookmarks