List Box Columns - Revisited

[Mark]

If this accidentally posts twice, then I apologize as I have been having
connection problems today.
This goes back to a post I made on Aug.12 about populating a list box. Now,
I was wondering how, or if it's even possible, to substitute variables into
the VLOOKUP formula:

..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"

Specifically, the document path, the sheet name, and the range. I have the
document path declared as:
Public Const studentFile As String = "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls"

The sheet name as:
Public grade As String

And as far as the range, I thought:
Sheets(grade).UsedRange.address

I've tried to substitute these variables into the formula in various
combinations, but
I can't seem to get anything to work. Does anyone have any ideas on this one?
Mark

 

[Dave Peterson]

Check your original thread.

If this accidentally posts twice, then I apologize as I have been having
connection problems today.
This goes back to a post I made on Aug.12 about populating a list box. Now,
I was wondering how, or if it's even possible, to substitute variables into
the VLOOKUP formula:

.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"

Specifically, the document path, the sheet name, and the range. I have the
document path declared as:
Public Const studentFile As String = "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls"

The sheet name as:
Public grade As String

And as far as the range, I thought:
Sheets(grade).UsedRange.address

I've tried to substitute these variables into the formula in various
combinations, but
I can't seem to get anything to work. Does anyone have any ideas on this one?
Mark

 

[OssieMac]

Hi Mark,

You might have your answer to this by now as Dave referred you back to a
previous post. I became interested and decided to put some time in and do
some testing. I have come up with an answer I thought I would post it anyway
in case you are still having problems. The main thing is to enure that your
formula string contains everything exactly as it would be if you created it
on the worksheet.

Things to note:
If you need to copy a formula down a page then the lookup value in the
formula must not be absolute. Conversely, the lookup array must be absolute
if you want to copy the formula down a page.

The parameters for the VBA .Address. No parameters default to absolute.
Using 1 or 0 returns relative or partial absolute depending on the
combination.

Sub CreateFormula()

Dim strLUpPath As String
Dim strLUpWb As String
Dim strLUpSht As String
Dim strLUpRng As String
Dim strLUpVal As String
Dim rowNdx As Long
Dim colNdx As Long

'Note last backslash
strLUpPath = "C:\Users\OssieMac\Documents\Excel\Test Macros\"

'Note Square brackets around workbook name
strLUpWb = strLUpPath & "[MyLookupTable.xlsm]"

strLUpSht = "My Sheet"

'Following could be created from .Address
strLUpRng = "$F$23:$J$50"

rowNdx = 3
colNdx = 1

'Assign non absolute address to string variable
strLUpVal = Cells(rowNdx, colNdx).Address(0, 0)

'Note single quotes around workbook and sheet name
Range("C3").Formula = "=VLOOKUP(" & strLUpVal & ",'" & _
strLUpWb & strLUpSht & "'!" & strLUpRng & ",2,FALSE)"

End Sub