Vlookup and Type Mismatch

J

John Pierce

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),unitsshares)")

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
 
P

Per Jessen

Hi
Try this:

STIPrice = Application.WorksheetFunction.VLookup(....

Hopes it helps.

Regards,
Per

"John Pierce" <[email protected]> skrev i meddelelsen
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),unitsshares)")

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
 
D

Dave Peterson

Change your declaration of STIPrice to:

Dim STIPrice as Variant 'could be an error

then use:
STIPrice = application.VLookup(ProcessDate, _
Names("STI_Prices").RefersToRange, 5, False)

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

This could avoid the problems when you do arithmetic with #n/a's.
 
J

John Pierce

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?
 
D

Dave Peterson

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 here:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subscript out of range, and more 5
vlookup issue 2
Run time error 13 - type mismatch 6
Select contiguous cells by ActiveCell.Interior.ColorIndex and applyborders 7
Type Mismatch with UBound and arrays 7
Search faster using array - But it slows down 24
vlookup type mismatch error 3
A Script to Create a Recordset and Import Data to a Spreadsheet 7

Top