refering to a variable in a cell.formula
 

Hi All

I just have a quick question, is it possible to refer to a variable in the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861, 0)"

Thanks
Craig

refering to a variable in a cell.formula
 

ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"

"cw" wrote:

Hi All

I just have a quick question, is it possible to refer to a variable in the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861, 0)"

Thanks
Craig

refering to a variable in a cell.formula
 

Nice

Giving it a go now

thanks
"Joel" wrote in message
...
ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"

"cw" wrote:

Hi All

I just have a quick question, is it possible to refer to a variable in
the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861, 0)"

Thanks
Craig

refering to a variable in a cell.formula
 

Sometime I have to use a temporary variable to get it to work. It is also
easier to debug problems doing it this way.

temp = "=MATCH(" & ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"
ActiveSheet.Cells(i ,9).Formula = temp

"cw" wrote:

Nice

Giving it a go now

thanks
"Joel" wrote in message
...
ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"

"cw" wrote:

Hi All

I just have a quick question, is it possible to refer to a variable in
the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861, 0)"

Thanks
Craig

refering to a variable in a cell.formula
 

Had to change it slightly

ActiveSheet.Cells(i, 12).Formula = "=MATCH(" & Chr(34) &
ActiveSheet.Cells(i, 6) & Chr(34) & ",'Vaillant Brand list'!B$1:B$1861, 0)"

but it works well

thanks for that
Craig




"cw" wrote in message
...
Nice

Giving it a go now

thanks
"Joel" wrote in message
...
ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"

"cw" wrote:

Hi All

I just have a quick question, is it possible to refer to a variable in
the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861,
0)"

Thanks
Craig

refering to a variable in a cell.formula
 

Instead of chr(34) you can use "" (two double quotes together). You end up
with three double quotes together. I never get this right the 1st time.
that is why I usually use the temporary variable.

ActiveSheet.Cells(i, 12).Formula = "=MATCH("""
ActiveSheet.Cells(i, 6) & """,'Vaillant Brand list'!B$1:B$1861, 0)"


"cw" wrote:

Had to change it slightly

ActiveSheet.Cells(i, 12).Formula = "=MATCH(" & Chr(34) &
ActiveSheet.Cells(i, 6) & Chr(34) & ",'Vaillant Brand list'!B$1:B$1861, 0)"

but it works well

thanks for that
Craig




"cw" wrote in message
...
Nice

Giving it a go now

thanks
"Joel" wrote in message
...
ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"

"cw" wrote:

Hi All

I just have a quick question, is it possible to refer to a variable in
the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861,
0)"

Thanks
Craig

refering to a variable in a cell.formula
 

that may be exactly what you want, but hard coding in the value of a cell in
your match formula is not the same a refering to that cell as in your
original post.


ActiveSheet.Cells(i, 12).Formula = "=MATCH(" & _
ActiveSheet.Cells(i, 6).Address(0,0) & ",'Vaillant Brand
list'!B$1:B$1861, 0)"

Will put in the cell reference in the formula instead of the current value
of the cell (continuing to work if the cell value changes.

--
Regards,
Tom Ogilvy


"cw" wrote:

Had to change it slightly

ActiveSheet.Cells(i, 12).Formula = "=MATCH(" & Chr(34) &
ActiveSheet.Cells(i, 6) & Chr(34) & ",'Vaillant Brand list'!B$1:B$1861, 0)"

but it works well

thanks for that
Craig




"cw" wrote in message
...
Nice

Giving it a go now

thanks
"Joel" wrote in message
...
ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"

"cw" wrote:

Hi All

I just have a quick question, is it possible to refer to a variable in
the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861,
0)"

Thanks
Craig

refering to a variable in a cell.formula
 

Thanks for that, although you are right I don't need it as the end result
will be made to a CSV file for a database import.

but thanks again, that will come in handy in the future I am sure

Craig



"Tom Ogilvy" wrote in message
...
that may be exactly what you want, but hard coding in the value of a cell
in
your match formula is not the same a refering to that cell as in your
original post.


ActiveSheet.Cells(i, 12).Formula = "=MATCH(" & _
ActiveSheet.Cells(i, 6).Address(0,0) & ",'Vaillant Brand
list'!B$1:B$1861, 0)"

Will put in the cell reference in the formula instead of the current value
of the cell (continuing to work if the cell value changes.

--
Regards,
Tom Ogilvy


"cw" wrote:

Had to change it slightly

ActiveSheet.Cells(i, 12).Formula = "=MATCH(" & Chr(34) &
ActiveSheet.Cells(i, 6) & Chr(34) & ",'Vaillant Brand list'!B$1:B$1861,
0)"

but it works well

thanks for that
Craig




"cw" wrote in message
...
Nice

Giving it a go now

thanks
"Joel" wrote in message
...
ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"

"cw" wrote:

Hi All

I just have a quick question, is it possible to refer to a variable
in
the
formula below

where B1 is after MATCH, I want that to match the variable 'i' in
the
Cells.(i,9)

ActiveSheet.Cells(i ,9).Formula = "=MATCH(B1,'Brand list'!B$1:B$1861,
0)"

Thanks
Craig