I copy some data from a web site and paste it onto an Excel sheet.
Then I read the data into an array and then write it, with some
modifications, to another sheet. At one point I need some data from a
table on a third sheet so I am trying to use vlookup but I am having
problems with that. I get a Type Mismatch error at the line “STIPrice
=”. I also tried to use Evaluate to do the Vlookup.

Private Sub cmdProcess_Click()
Dim NumRows As Integer, NumCols As Integer
Dim ArrayTransactionData As Variant
Dim StartCell As Range
Dim i As Integer
Dim CountRows As Long
Dim LastTx As Integer
Dim ProcessDate As Date, Amount As Currency, TransactionType As String
Dim UnitsShares As Single, US As String, Price As Currency
Dim Security As String, Transaction As String, Shares As Single
Dim SharePrice As Currency, STIPrice As Integer

'Paste data on sheet starting at A1
Sheets("Data").Activate
NumRows = Range("A1").CurrentRegion.Rows.Count - 1 'subtract one to
accommodate header row
NumCols = Range("A1").CurrentRegion.Columns.Count
ArrayTransactionData = Sheets("Data").Range(Cells(2, 1), Cells(NumRows
+ 1, NumCols))

Sheets("Transactions").Activate
Set StartCell = Cells(Cells(65536, 1).End(xlUp).Row + 1, 1)

i = 1
CountRows = 0
LastTx = UBound(ArrayTransactionData, 1)
Do Until i LastTx
ProcessDate = ArrayTransactionData(i, 1)
Amount = ArrayTransactionData(i, 2)
TransactionType = ArrayTransactionData(i, 3)
UnitsShares = ArrayTransactionData(i, 4)
US = ArrayTransactionData(i, 5)
Price = ArrayTransactionData(i, 6)
Security = lstSecurities.Text
STIPrice = WorksheetFunction.VLookup(ProcessDate,
Names("STI_Prices").RefersToRange, 5, False)
' STIPrice = Evaluate("=IF(security = ""SunTrust Common Stock
Fund"",VLOOKUP(processdate,STI_Prices,5,FALSE),uni tsshares)")

With StartCell
.Offset(CountRows, 0) = TransactionType
.Offset(CountRows, 1) = Transaction
.Offset(CountRows, 2) = ProcessDate
.Offset(CountRows, 3) = Security
.Offset(CountRows, 4) = Format(UnitsShares, "0.00000")
.Offset(CountRows, 5) = Price
.Offset(CountRows, 6) = US
If Security = "SunTrust Common Stock Fund" Then
.Offset(CountRows, 7) = Amount / STIPrice
Else: .Offset(CountRows, 7) = UnitsShares
End If
If Security = "SunTrust Common Stock Fund" Then
.Offset(CountRows, 8) = STIPrice
Else: .Offset(CountRows, 8) = Price
End If
.Offset(CountRows, 9) = Amount
End With
i = i + 1
CountRows = CountRows + 1
Loop
Unload Me
End Sub