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

sFormula = "&""""&Address!RC[5]&"" ""&Address!RC[6]&"" ""&Address!RC[7]"
If SheetExists("Address") Then
For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row
If Worksheets("Address").Range("G" & i) 0 Then
Cells(i, "C").FormulaR1C1 = _
"=Address!RC[3]&""""&Address!RC[4]" & sFormula
Else
Cells(i, "C").FormulaR1C1 = _
"=Address!RC[3]" & sFormula
End If
Next i
Else
Range("C2").Value = "None"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"LizS" wrote in message
...

Hi All
I need to get cell entries from another sheet and I don't know how many
rows will be involved. I have the following code that gives me the
right info but, as you can see, only if there are up to 3 rows
involved. Can anyone sugest how I could use an array to get my info
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 If


--
LizS
------------------------------------------------------------------------
LizS's Profile:
http://www.excelforum.com/member.php...o&userid=28991
View this thread: http://www.excelforum.com/showthread...hreadid=487198

Thanks for the reply Bob
I've tried your code but it goes from

For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row

to

End If

Count contains 65536 and i is empty

Regards
Liz


--
LizS
------------------------------------------------------------------------
LizS's Profile: http://www.excelforum.com/member.php...o&userid=28991
View this thread: http://www.excelforum.com/showthread...hreadid=487198

I know that Rows.Count is 65536, but what do you mean by i is empty? It
starts as empty, but as soon as you enter the For loop, it gets initialised.

I tried it on some test data, and it worked fine. What exactly do you
experience?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LizS" wrote in message
...

Thanks for the reply Bob
I've tried your code but it goes from

For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row

to

End If

Count contains 65536 and i is empty

Regards
Liz


--
LizS
------------------------------------------------------------------------
LizS's Profile:
http://www.excelforum.com/member.php...o&userid=28991
View this thread: http://www.excelforum.com/showthread...hreadid=487198

Thanks Bob and apologies for my mistake - I did update my thread but
obviously after you'd looked at it. i is 2 (not empty as I previously
said.

I get no result in my cell and when I step through the code it goes
straight from the For statement to the end if.

I have attached a sample of the Address sheet with another sheet
showing my entire macro incase something else I am doing is affecting
it.

Thanks for your time and expertise on this.
Regards
Liz


+-------------------------------------------------------------------+
|Filename: AddressExample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4058 |
+-------------------------------------------------------------------+

--
LizS
------------------------------------------------------------------------
LizS's Profile: http://www.excelforum.com/member.php...o&userid=28991
View this thread: http://www.excelforum.com/showthread...hreadid=487198

Bob
I have sussed out my problem - When I do the For statement I'm on a
sheet that has nothing in G. When I change to Address and run it, it's
fine. I now have the following problem

sFormula = "&""""&Address!RC[5]&"" ""&Address!RC[6]&""
""&Address!RC[7]"
If SheetExists("Address") Then
Worksheets("Address").Activate
For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row
Worksheets("Sheet14").Activate
If Worksheets("Address").Range("G" & i) 0 Then
Cells(i, "C").FormulaR1C1 = _
"=Address!RC[3]&""""&Address!RC[4]" & sFormula
Else
Cells(i, "C").FormulaR1C1 = _
"=Address!RC[3]" & sFormula
End If
Next i
Else
Range("C2").Value = "None"
End If

How do I store the sheet name in a variable? In my example the new
sheet is Sheet14 but that won't always be the case.

Thanks again for your time
Regards
Liz


--
LizS
------------------------------------------------------------------------
LizS's Profile: http://www.excelforum.com/member.php...o&userid=28991
View this thread: http://www.excelforum.com/showthread...hreadid=487198