Inserting VLookup using VBA - Problems concatinating contents of cell & variable

[scott_smith]

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

 

[Joel]

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.

 

[scott_smith]

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.

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.

 

[scott_smith]

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.

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

 

[scott_smith]

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.

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 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.

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

 

[scott_smith]

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