On Apr 19, 10:17 pm, wrote:
On Apr 19, 5:30 pm, Joel wrote:



I have tried to duplicate the error. I think it has to do with the
parameters you are passing into the routine. If you send the 3 paramters I
will try to duplate the failure. right know it doesn't fail.

" wrote:
Hi,

I'm trying to insert a Vlookup into a cell based on variables that are
collceted via a user form.

Only problem is, for the "Else" part of the the below "If" statement I
get a "Run-time error '1004': Application-defined or object-defined
error".

I think it's becuase it doesn't like the concatenation of the FundCode
and Series variables, but I can't work out how to fix it.

The first part of the "If" statement works when the Fundcode variable
is passed as a string, but in the latter case when Fundcode is passed
as a cell address it doesn't work.

Can anyone give me any help or advice?

Thanks in advance,
Scott

-----------------

Sub InsertFundPrice(FundCode As Variant, Series As String, Pricedate
As Date)

Dim PriceMonth, PriceYear As String

PriceMonth = Left(MonthName(Month(Pricedate)), 3)
PriceYear = Right(Pricedate, 4)

If Len(FundCode) <= 2 Then
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""" & FundCode & Series & """,'J:\RESTRICT
\Performance\Bulletin\Fund Prices\" & PriceYear & "\" & PriceMonth & "\
[fund prices 01" & PriceMonth & PriceYear & ".xls]Summary'!
R2C4:R2000C5,2,FALSE)"
Else
VTarget = FundCode & " & " & Series
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(" & VTarget & ",'J:\RESTRICT\Performance\Bulletin
\Fund Prices\" & PriceYear & "\" & PriceMonth & "\[fund prices 01" &
PriceMonth & PriceYear & ".xls]Summary'!R2C4:R2000C5,2,FALSE)"
End If
End Sub

Thanks for the reply, Joel.

I'm not at work anymore so I don't have access to the code, but I'll
post it as soon as I get in tomorrow morning.

On Apr 19, 10:17 pm, wrote:
On Apr 19, 5:30 pm, Joel wrote:



I have tried to duplicate the error. I think it has to do with the
parameters you are passing into the routine. If you send the 3 paramters I
will try to duplate the failure. right know it doesn't fail.

" wrote:
Hi,

I'm trying to insert a Vlookup into a cell based on variables that are
collceted via a user form.

Only problem is, for the "Else" part of the the below "If" statement I
get a "Run-time error '1004': Application-defined or object-defined
error".

I think it's becuase it doesn't like the concatenation of the FundCode
and Series variables, but I can't work out how to fix it.

The first part of the "If" statement works when the Fundcode variable
is passed as a string, but in the latter case when Fundcode is passed
as a cell address it doesn't work.

Can anyone give me any help or advice?

Thanks in advance,
Scott

-----------------

Sub InsertFundPrice(FundCode As Variant, Series As String, Pricedate
As Date)

Dim PriceMonth, PriceYear As String

PriceMonth = Left(MonthName(Month(Pricedate)), 3)
PriceYear = Right(Pricedate, 4)

If Len(FundCode) <= 2 Then
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(""" & FundCode & Series & """,'J:\RESTRICT
\Performance\Bulletin\Fund Prices\" & PriceYear & "\" & PriceMonth & "\
[fund prices 01" & PriceMonth & PriceYear & ".xls]Summary'!
R2C4:R2000C5,2,FALSE)"
Else
VTarget = FundCode & " & " & Series
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(" & VTarget & ",'J:\RESTRICT\Performance\Bulletin
\Fund Prices\" & PriceYear & "\" & PriceMonth & "\[fund prices 01" &
PriceMonth & PriceYear & ".xls]Summary'!R2C4:R2000C5,2,FALSE)"
End If
End Sub

Thanks for the reply, Joel.

I'm not at work anymore so I don't have access to the code, but I'll
post it as soon as I get in tomorrow morning.

I actually wrote a more comprehensive reply than the one above a few
hours ago, but for some reason it doesn't seem to have appeared.

It basically just said that all variables were taken from the
following options on a user form:

Fundcode = This can be typed in a Textbox or a cell containing the
fund code can be selected using a Refedit. I have passed this
parameter as a variant because I'm not sure whether the user will
chose to type the fund code or select a cell.
Series = This is selected using a combobox that is populated with
fixed values at the initialization of the user form.
Pricedate = This is typed into a text box

Not sure if that helps you diagnose the problem, but I will post the
rest of the code tomorrow.

Thanks again,
Scott