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

Hi
Try this:

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

Hopes it helps.

Regards,
Per

"John Pierce" 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),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

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.

John Pierce wrote:

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

--

Dave Peterson

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?

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