Quick VBA method for looking up data
Hello All,
I was wondering if it is possible to have some VBA code read a file of data and then find each item in an excel sheet a paste a value into a given cell. I currently do this by defining the source range and the destination range. Then for each account in the destination range I check if it was in the source range and if it was then I paste the required value in a cell offset from the account number. The problem with this is that it is quite slow and the VBA Module is continually reading the data to find matches. Is there a way for the VBA to read the entire source range to memory and then check for each account in the destination range (they may no longer exist in the destination data) and paste the value required? I have posted my current code below. Sub Match() Dim CCRange As Range 'Define the destination range Dim MSRange As Range 'Define the source data range Dim CCAccount As Range Dim MSAccount As Range Set CCRange = FindRange(CCWorkbook.Worksheets("Cap Completions"), "a8") Set MSRange = FindRange(MSWorkbook.Worksheets("funding move"), "A1") For Each MSAccount In MSRange 'Begin loop Debug.Print MSAccount.Address If MSAccount.Value < "" Then 'Check for account on current line For Each CCAccount In CCRange If MSAccount.Value = CCAccount.Value Then CCAccount.Offset(, 15).Value = "NU" 'Offset destination account End If Next CCAccount End If Next MSAccount End Sub |
All times are GMT +1. The time now is 01:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com