Hi All
I need to get cell entries from another sheet and I don't know how man
rows will be involved. I have the following code that gives me th
right info but, as you can see, only if there are up to 3 row
involved. Can anyone sugest how I could use an array to get my inf
regardless of the number of rows involved?
Thanks in advance
Liz

If SheetExists("Address") Then
Range("C2").Select

If Range("Address!G2") 0 Then
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(Address!RC[3]&"" ""&Address!RC[4]&"
""&Address!RC[5]&"" ""&Address!RC[6]&"" ""&Address!RC[7])"
Range("C3").Select
Else
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(Address!RC[3]&"" ""&Address!RC[5]&"
""&Address!RC[6]&"" ""&Address!RC[7])"
Range("C3").Select
End If

If Range("Address!G3") 0 Then
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(Address!RC[3]&"" ""&Address!RC[4]&"
""&Address!RC[5]&"" ""&Address!RC[6]&"" ""&Address!RC[7])"
Range("C4").Select
Else
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(Address!RC[3]&"" ""&Address!RC[5]&"
""&Address!RC[6]&"" ""&Address!RC[7])"
Range("C4").Select
End If

If Range("Address!G4") 0 Then
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(Address!RC[3]&"" ""&Address!RC[4]&"
""&Address!RC[5]&"" ""&Address!RC[6]&"" ""&Address!RC[7])"
Range("C5").Select
Else
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(Address!RC[3]&"" ""&Address!RC[5]&"
""&Address!RC[6]&"" ""&Address!RC[7])"
Range("C5").Select
End If

Else
Range("C2").Select
ActiveCell.FormulaR1C1 = "None"
End I

--
Liz
-----------------------------------------------------------------------
LizS's Profile: http://www.excelforum.com/member.php...fo&userid=2899
View this thread: http://www.excelforum.com/showthread.php?threadid=48719