refering to a variable in a cell.formula

  • Thread starter Thread starter cw
  • Start date Start date
C

cw

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
 
ActiveSheet.Cells(i ,9).Formula = "=MATCH(" & _
ActiveSheet.Cells(1 ,"B") & ",'Brand list'!B$1:B$1861, 0)"
 
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
 
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
 
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)"
 
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.
 
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
 
Back
Top