Enter formula with VLOOKUP referencing another file

[Alan_CT06]

Hello All,

I am trying to create a sub to enter a formula with a vlookup that pulls data from another open file.

The formula references an account number, which I've assigned a variable "Account" (which is in column C), and needs to pull an Account Value into a cell I've assigned a variable "AccountValue".

The vlookup pulls from an open file named FIDELITYBALANCES.csv, that has one sheet also named FILDELITYBALANCES.

In FidelityBalances, account numbers are in Column A and Account Values are in Column B.

I'm working in row 2437 now, so the formula I need would read.

=VLOOKUP(C2437,FIDELITYBALANCES.csv!$A$1:$B$10000,2,FALSE)

I've made the below two attempts.

Test 1 results in:
=VLOOKUP(C2437,[FIDELITYBALANCES.csv]FIDELITYBALANCES!AB),2,FALSE)

with the parens around the B which causes a problem.


Test1A results in:
=VLOOKUP(C2437,FIDELITYBALANCES.csv!'A1':'B10000',2,FALSE)
with the single quotes around the cell references that cause an error

Thanks for any help.





Sub Test1()

Set CurrentCell = ActiveCell


Set AccountHead = Cells.Find(What:="Account", LookAt:=xlWhole)
Set Account = Cells(CurrentCell.Row, AccountHead.Column)

Set AccountValueHead = Cells.Find(What:="Account Value", LookAt:=xlWhole)
Set AccountValue = Cells(CurrentCell.Row, AccountValueHead.Column)


externalFile = "[FIDELITYBALANCES.csv]FIDELITYBALANCES"
lookupRange = "'" & externalFile & "'!A:B" ' Lookup table is in columns A and B


With AccountValue

.Formula = "=VLOOKUP(" & Account.Address(False, False, ReferenceStyle:=xlR1C1, RelativeTo:=.Item(1)) & "," & lookupRange & ",2,FALSE)"

.NumberFormat = "#,###"

End With


End Sub




Sub Test1A()

Set CurrentCell = ActiveCell


Set AccountHead = Cells.Find(What:="Account", LookAt:=xlWhole)
Set Account = Cells(CurrentCell.Row, AccountHead.Column)

Set AccountValueHead = Cells.Find(What:="Account Value", LookAt:=xlWhole)
Set AccountValue = Cells(CurrentCell.Row, AccountValueHead.Column)



With AccountValue

.Formula = "=VLOOKUP(" & Account.Address(False, False, ReferenceStyle:=xlR1C1, RelativeTo:=.Item(1)) & ",FIDELITYBALANCES.CSV!A1:B10000,2,FALSE)"

.NumberFormat = "#,###"

End With


End Sub

 

[Alan_CT06]

I figured it out.

 

[Peter_SSs]

I figured it out.

Good to hear.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[Alan_CT06]

Hello All,

I am trying to create a sub to enter a formula with a vlookup that pulls data from another open file.

The formula references an account number, which I've assigned a variable "Account" (which is in column C), and needs to pull an Account Value into a cell I've assigned a variable "AccountValue".

The vlookup pulls from an open file named FIDELITYBALANCES.csv, that has one sheet also named FILDELITYBALANCES.

In FidelityBalances, account numbers are in Column A and Account Values are in Column B.

I'm working in row 2437 now, so the formula I need would read.

=VLOOKUP(C2437,FIDELITYBALANCES.csv!$A$1:$B$10000,2,FALSE)

I've made the below two attempts.

Test 1 results in:
=VLOOKUP(C2437,[FIDELITYBALANCES.csv]FIDELITYBALANCES!AB),2,FALSE)

with the parens around the B which causes a problem.


Test1A results in:
=VLOOKUP(C2437,FIDELITYBALANCES.csv!'A1':'B10000',2,FALSE)
with the single quotes around the cell references that cause an error

Thanks for any help.





Sub Test1()

Set CurrentCell = ActiveCell


Set AccountHead = Cells.Find(What:="Account", LookAt:=xlWhole)
Set Account = Cells(CurrentCell.Row, AccountHead.Column)

Set AccountValueHead = Cells.Find(What:="Account Value", LookAt:=xlWhole)
Set AccountValue = Cells(CurrentCell.Row, AccountValueHead.Column)


externalFile = "[FIDELITYBALANCES.csv]FIDELITYBALANCES"
lookupRange = "'" & externalFile & "'!A:B" ' Lookup table is in columns A and B


With AccountValue

.Formula = "=VLOOKUP(" & Account.Address(False, False, ReferenceStyle:=xlR1C1, RelativeTo:=.Item(1)) & "," & lookupRange & ",2,FALSE)"

.NumberFormat = "#,###"

End With


End Sub




Sub Test1A()

Set CurrentCell = ActiveCell


Set AccountHead = Cells.Find(What:="Account", LookAt:=xlWhole)
Set Account = Cells(CurrentCell.Row, AccountHead.Column)

Set AccountValueHead = Cells.Find(What:="Account Value", LookAt:=xlWhole)
Set AccountValue = Cells(CurrentCell.Row, AccountValueHead.Column)



With AccountValue

.Formula = "=VLOOKUP(" & Account.Address(False, False, ReferenceStyle:=xlR1C1, RelativeTo:=.Item(1)) & ",FIDELITYBALANCES.CSV!A1:B10000,2,FALSE)"

.NumberFormat = "#,###"

End With


End Sub

Here is my solution:

Add the declaration of the variables at top, and the single quotes around the file name and sheet.




Sub Test1B()

Dim CurrentCell As Range
Dim AccountHead As Range, Account As Range
Dim AccountValueHead As Range, AccountValue As Range

Set CurrentCell = ActiveCell

Set AccountHead = Cells.Find(What:="Account", LookAt:=xlWhole)
Set Account = Cells(CurrentCell.Row, AccountHead.Column)

Set AccountValueHead = Cells.Find(What:="Account Value", LookAt:=xlWhole)
Set AccountValue = Cells(CurrentCell.Row, AccountValueHead.Column)

With AccountValue
.Formula = "=VLOOKUP(" & Account.Address(False, False) & ",'[FIDELITYBALANCES.xlsx]Sheet1'!A1:B10000,2,FALSE)"
.NumberFormat = "#,###"
End With

AccountValue.Value = AccountValue.Value

End Sub

 

[Alan_CT06]

Good to hear.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Added the solution. Thx.