Variable reference
 

I am tyring to put a value in a cell using the
Activecell.formular1c1 function. Through code I get two
cell values and store them in variables. I then try to
use those two variables in the Activecell.formular1c1
function and it doesn't seem to recognize them as
variables.

CODE:

Cells.Find(What:="Illinois", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,_
SearchDirection:=xlNext, MatchCase:=False).Activate
With Selection
.Offset(2, 2).Select
End With

a = Selection.Address

Cells.Find(What:="INDIANA", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,_
SearchDirection:=xlNext, MatchCase:=False).Activate
With Selection
.Offset(-1, 2).Select
End With

b = Selection.Address

Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sum(a:b)"

When I run this code Cell A1 has #NAME? in it because
instead of Excel references a and b as their variable
values, they are just recognized as letters.

What do I need to do in order to get those variables in a
format that will actually take the cell that variable
references?

Thanks,
John

Variable reference
 

Range("A1").Select
ActiveCell.Formula = "=Sum(" & a & ":" & b & ")"


Should work.

Regards,
Tom Ogilvy

"John" wrote in message
...
I am tyring to put a value in a cell using the
Activecell.formular1c1 function. Through code I get two
cell values and store them in variables. I then try to
use those two variables in the Activecell.formular1c1
function and it doesn't seem to recognize them as
variables.

CODE:

Cells.Find(What:="Illinois", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,_
SearchDirection:=xlNext, MatchCase:=False).Activate
With Selection
.Offset(2, 2).Select
End With

a = Selection.Address

Cells.Find(What:="INDIANA", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,_
SearchDirection:=xlNext, MatchCase:=False).Activate
With Selection
.Offset(-1, 2).Select
End With

b = Selection.Address

Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sum(a:b)"

When I run this code Cell A1 has #NAME? in it because
instead of Excel references a and b as their variable
values, they are just recognized as letters.

What do I need to do in order to get those variables in a
format that will actually take the cell that variable
references?

Thanks,
John