That's why I dropped the .worksheetfunction in the code I suggested.

And that's why I suggested this:

I'd add:
if iserror(stiprice) then
'no match, what should happen
else
'keep going
end if

If you return an error, what should happen he

If Security = "SunTrust Common Stock Fund" Then
.Offset(CountRows, 7) = Amount / STIPrice
Else: .Offset(CountRows, 7) = UnitsShares
End If

Maybe...

If Security = "SunTrust Common Stock Fund" Then
if iserror(stiprice) then
.offset(countrows,7) = "stiprice not found!"
else
if isnumber(stiprice) = false then
.offset(countrows,7) = "Stiprice not a number!"
else
if stiprice = 0 then
.offset(countrows,7) = "Stiprice = 0"
else
.Offset(CountRows, 7) = Amount / STIPrice
end if
end if
end if
Else
.Offset(CountRows, 7) = UnitsShares
End If


John Pierce wrote:

WorksheetFunction doesn’t work at all. I had to change to
“Application.Vlookup”. I think it’s an Excel version thing. I also
changed “Dim STIPrice” to Variant. Now the program will run past the
“STIPrice = Application.Vlookup …” line but stops farther down where I
try to use STIPrice. It says Run-time erro ‘13’, Type mismatch, and
the little yellow info box that pops up when I put the cursor on it
says STIPrice = Error 2042. Also, the info for the “STIPrice =
Application.Vlookup…” says Error 2042, which I understand is the VBA
equivalent of #N/A. Interestingly, the procedure runs perfectly when
Security doesn’t equal “SunTrust …”. Any ideas?

--

Dave Peterson