Aaron,

Generally, the more native Excel functionality you use, and the less
looping, the faster your code will run. Well-written worksheet formulas
trump almost any VBA code - I'm sure Harlan will jump in here and prove me
wrong, so I will add the caveat "in most cases." And, frankly, I'm surprised
that it took 8 seconds, though that could be impacted by other formulas,
etc. The more experience you have in using Excel, the better you can make
those decisions. One of the most commonly done tasks (deleting rows based
on a value) is almost always faster after a sort than by looping through,
for example.

But, of course there are cases where the function just doesn't exist, or is
easier to implement by using a User-Defined-Function (UDF). A lot depends
on the circumstances, the requirements, the data layout, the skill of the
coder, etc.

Bernie


"Aaron" wrote in message
...
Wow, from 22 seconds to 8 seconds. So is that a rule? I mean, if I want
to
populate a cell should I only use VBA if no formula exists?


"Bernie Deitrick" wrote:

Find a formula that works to extract the correct data, then insert that
formula in the third column
(matching your table) using the macro. No looping involved. Something
like this one line:

Range("A2", Range("A2").End(xlDown)).Offset(0, 2).Formula =
"=VLOOKUP(A2,AHPart,2,FALSE)"

You could then convert that to values if you wanted.

HTH,
Bernie
MS Excel MVP


"Aaron" wrote in message
...
This code is the work horse of many of my programs but I wish it would
run
faster. It basically runs through a list of values one at a time and
looks
them up on a larger list and returns some coresponding data from the
larger
list.

Sub Generate()
Dim s As Date
Dim f As Date
Dim t As Long
Dim rptr As Long
Dim data As Long
Dim DataPart As Object
Dim RptPrt As String

s = now

rptr = 2
data = 0

Sheets("Report").Select

While Cells(rptr, 1) < ""
RptPrt = Cells(rptr, 1)
'If WorksheetFunction.CountIf(Range("AHPart"), RptPrt) 0 Then
With Range("AHpart")
Set DataPart = .Find(RptPrt)
'Set DataPart = .Find(What:=RptPrt, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With
If Not DataPart Is Nothing Then
data = data + DataPart.Offset(0, 1).Value
Cells(rptr, 3) = data
rptr = rptr + 1
data = 0
Else
rptr = rptr + 1
End If
'Else
'rptr = rptr + 1
'End If
Wend

f = now
t = DateDiff("s", s, f)
MsgBox (t)

End Sub

If I use the countif or the explicit find the code runs even slower.
AHPart
is a dynamic range on the large list so it is only as long as it needs
to be.

Can this be faster?