craigjones1203
New Member
- Joined
- Apr 9, 2021
- Messages
- 2
- Office Version
- 2013
- Platform
- Windows
Hey everyone - new VBA user here!
I'm working on trying to make a repetitive task easier with VBA and have been having issues with my code taking a really long time to run or the file just crashing. For reference, I'll typically be dealing with ~50,000 rows of data. What I'm trying to do is use a VLookup function for a table on a different sheet but it keeps crashing for larger sets of data. Does anyone have any tips for how to make more efficient? Any tips are appreciated!
Code below:
Dim last As Long
Dim i as Long
last = Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
i = 1
For Each Cell In Range ("Q2:Q" & last)
i = i + 1
Cell.Value = Application.VLookup(Range("F" & i), Worksheets("Revenue").Range("A:E"), 5, False)
Range("R" & i) = Application.VLookup(Range("F" & i), Worksheets("Revenue").Range("A:E"), 4, False)
Next
I'm working on trying to make a repetitive task easier with VBA and have been having issues with my code taking a really long time to run or the file just crashing. For reference, I'll typically be dealing with ~50,000 rows of data. What I'm trying to do is use a VLookup function for a table on a different sheet but it keeps crashing for larger sets of data. Does anyone have any tips for how to make more efficient? Any tips are appreciated!
Code below:
Dim last As Long
Dim i as Long
last = Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
i = 1
For Each Cell In Range ("Q2:Q" & last)
i = i + 1
Cell.Value = Application.VLookup(Range("F" & i), Worksheets("Revenue").Range("A:E"), 5, False)
Range("R" & i) = Application.VLookup(Range("F" & i), Worksheets("Revenue").Range("A:E"), 4, False)
Next