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

I don't know how CCRange and MSRange get set, but if CCRange is one column, you
could use application.match:

Option Explicit
Sub match()

'add a declare
Dim res As Variant
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
res = Application.match(MSAccount.Value, CCRange, 0)
If IsError(res) Then
'no match found
Else
CCRange(res).Offset(0, 15).Value = "NU"
End If
Next MSAccount

End Sub



Richard wrote:

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

--

Dave Peterson