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

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

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

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

On 19 Apr, 22:23, wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Here's the rest of the code (for the user form) as promised.

You may notice that I defined the FundCodeCell parameter (the input
from the RefEdit) as a Variant - this is because when I tried to use
address it didn't work.

This could be part of the problem, so any advice on that is also much
appreciated.

Thanks,
Scott

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

Private Sub CommandButton1_Click()

Dim FundCode As String
Dim Series As String
Dim Pricedate As Date
Dim FundCodeCell As Variant

FundCode = FundCodeBox.Value
Series = SeriesBox.Value
Pricedate = DateBox.Value
FundCodeCell = CellFundCode.Value
FundCodeCell = Replace(FundCodeCell, "$", " ")

If FundCode < "" Then
Call InsertFundPrice(FundCode, Series, Pricedate)
Else
Call InsertFundPrice(FundCodeCell, Series, Pricedate)
End If


End Sub


Private Sub CommandButton2_Click()

Unload FundPriceForm

End Sub

Private Sub UserForm_Initialize()

With SeriesBox
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
End With

SeriesBox.ListIndex = 1

DateBox.Value = "01/01/07"

End Sub