INDEX as an alternative for INDIRECT

**Hein** · 02-18-2009, 04:24 AM

Thank You DonkeyOte for your reply

Rebuilding the function using VBA is a very usable option, thanks for the suggestion

Using the INDIRECT.EXT means, I believe, installing 3rd party software. This is not an option since we are not allowed to install this.

Did you see my Edit - remarks (the PS), I think our messages crossed each other.

Anyway, I will use my VBA skills again

Thanks
Hein

**Hein** · 02-18-2009, 08:21 AM

I have the code working, apart from 1 little thing that I can't figure out

During the code I determine the name of the current worksheet
(The target worksheet in the source file has the same name).

When I keep the mousepointer over the sheetname a popup appears with the sheetname "Target".

However: when the code is run it asks to confirm which sheet is needed in the sourcefile. The name it gives is "_Target"

Is this "_" the problem and how can I get rid of it?

I have added a demo to demonstrate what happens

How can I fix this?

Thanks in advance
Hein

**DonkeyOte** · 02-18-2009, 11:33 AM

I'm not sure I follow quite what you're looking to do but in principle (if nothing more) you need:

ActiveCell.Formula = _
    "=INDEX(" & DirectoryNameDeelEen & Sheetname & DirectoryNameDeelTwee & " , Row(), Column())"

**Hein** · 02-19-2009, 03:43 AM

Hi DonkeyOte,

Thanks, it had be to be something with the " and the &.
This was 1 of the many versions I have tried, but I must have made an error on the way.

What I'm trying to do is to create a path to the directory, file and worksheet I need to get the data from.

The 1st part ("'C:\Temp\[SourceFile.xls]") is the directory and nam of the file.

The middle part is the name of the worksheet (worksheetname = Target). I use the same name for the corresponding worksheets in the source and in the workfile. Therefore by determining the worksheet name in the workfile, I have also the name of the wanted worksheet in the sourcefile.

The part ("'!$1:$65536") is the area where index searches for the data.

The last part (, Row(), Column())") from the formula is the target cell.

The outcome is a duplicate from the source file/worksheet

This is not the first time I am having trouble with the & and the "
Is it possible to shed light on the theory behind it?

Thanks anyway
Hein

**DonkeyOte** · 02-19-2009, 05:29 AM

Sub Tryout()
SheetName = ActiveSheet.Name
DirectoryNameDeelEen = "'C:\Temp\[SourceFile.xls]"
DirectoryNameDeelTwee = "'!$1:$65536"
ActiveCell.Formula = _
    "=INDEX(" & DirectoryNameDeelEen & " & Sheetname & " & DirectoryNameDeelTwee & " , Row(), Column())"
End Sub

The above would generate the following formula string:

=INDEX('C:\Temp\[SourceFile.xls] & Sheetname & '!$1:$65536 , Row(), Column())

Ampersand in VBA is used to concatenate strings, the use of quotations determines where you are entering text explicitly... perhaps we can explain using a simpler example, eg:

Dim a As String, b As String
a = "Hello"
b = "World"
Debug.Print a & " & b " 'outputs string Hello & b
Debug.Print a & b        'outputs string HelloWorld
Debug.Print a & " " & b 'outputs string Hello World (ie we add a space)

So in the first debug statement the second Ampersand in the code is seen by VBA as being part of an explicitly defined text string (& b) whereas is the 2nd & 3rd examples it is only being used to concatenate variables into one string. The 3rd example illustrates how we can insert explicit strings between our concatenated string variables.

So to generate your formula which if entered in native XL would be:

=INDEX('C:\Temp\[SourceFile.xls]Target'!$1:$65536,Row(),Column())

(whether I agree with the formula is different :-))

To generate the above in VBA you thus need:

"=INDEX(" & DirectoryNameDeelEen & Sheetname & DirectoryNameDeelTwee & ",Row(),Column())"

So you use & to concatenate the string variables into one string... & should not be used literally (ie explicitly in the string itself) .. the only parts of the string that are fixed (ie no variables) are:

"=INDEX("

and

",ROW(),COLUMN())"

The remainder should be generated by concatenating the variables.

Make sense ?

(I guess re: the formula using in the way you're using it you could set both row & column index to 0 rather than using ROW(),COLUMN())

**Hein** · 02-19-2009, 06:10 AM

Hi DonkeyOte,
Thanks for the trouble of explaining the wonders of VBA and what you can do with it!!

Your explanation makes sense to me. It made me realise that all 3 declared variables are the same. For some reason I thought the DirectoryNameDeelEen and ...Twee were text. Maybe because their contents are text, while in real life they are also declared.

Next time I try to make a formula in VBA it must be easier!

I am always open for tutoring: what I am trying to do is to make a copy from another file/worksheet.
In your text you say:
"
(whether I agree with the formula is different :-))
"

How would you try to do this

Thanks again
Hein