Get Info From Another Sheet
 

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

Get Info From Another Sheet
 

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

Get Info From Another Sheet
 

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

Get Info From Another Sheet
 

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

Get Info From Another Sheet
 

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

Get Info From Another Sheet
 

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

Get Info From Another Sheet
 

Thanks for all your help Bob. I changed my line from:

Worksheets("Sheet14").Activate

to

Worksheets(1).Activate

and it all works fine now.

I have pasted my complete macro below incase it can help anyone else.

Thanks again
Regards
Liz

Sub PrintSheet()
'
' PrintSheet Macro
' Macro recorded 16/11/2005 by abc
'
Sheets.Add

Range("A1").Select
ActiveCell.FormulaR1C1 = "Search No."
Range("A2").Select
ActiveCell.FormulaR1C1 = "AONB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Advert Control"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Ancient Monument"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Article IV"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Breach of Condition"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Conservation Area"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Conservation Interest (SNCI)"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Enforcements"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Landscape Area (SLA)"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Nature Reserves (NNR)"
Range("A12").Select
ActiveCell.FormulaR1C1 = "Rail Link within 0m"
Range("A13").Select
ActiveCell.FormulaR1C1 = "Rail Link within 200m"
Range("A14").Select
ActiveCell.FormulaR1C1 = "SSSI"
Range("A15").Select
ActiveCell.FormulaR1C1 = "Tree Preservation Order"
Range("A16").Select
ActiveCell.FormulaR1C1 = "Waste"
Range("A17").Select
ActiveCell.FormulaR1C1 = "Minerals"
Range("A18").Select
ActiveCell.FormulaR1C1 = "HSE (Hazardous)"
Range("A19").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "Address"
Range("C2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "Parish"
Range("D2").Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "Ward Boundaries"
Range("E2").Select

If SheetExists("Land Search") Then
Range("B1").Select
ActiveCell.FormulaR1C1 = "='Land Search'!R[1]C"
Range("B2").Select
Else
Range("B1").Select
ActiveCell.FormulaR1C1 = "None"
End If

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(1).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


If SheetExists("Parish") Then
Range("D2").Select
ActiveCell.FormulaR1C1 = "='Parish'!RC[0]"
Range("D3").Select
Else
Range("D2").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Ward Boundaries") Then
Range("E2").Select
ActiveCell.FormulaR1C1 = "='Ward Boundaries'!RC[-2]"
Range("E3").Select
Else
Range("E2").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("AONB") Then
Range("B2").Select
ActiveCell.FormulaR1C1 = "='AONB'!RC[8]"
Range("B3").Select
Else
Range("B2").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Advert Control") Then
Range("B3").Select
ActiveCell.FormulaR1C1 = "='Advert Control'!R[-1]C[6]"
Range("B4").Select
Else
Range("B3").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Ancient Monument") Then
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Ancient Monument'!R[-2]C[6]"
Range("B5").Select
Else
Range("B4").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Article IV") Then
Range("B5").Select
ActiveCell.FormulaR1C1 = "='Article IV'!R[-3]C[6]"
Range("B6").Select
Else
Range("B5").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Conservation Area") Then
Range("B7").Select
ActiveCell.Formula = "='Conservation Area'!R[-5]C[1]"
Range("B8").Select
Else
Range("B7").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Conservation Interest") Then
Range("B8").Select
ActiveCell.FormulaR1C1 = "='Conservation Interest'!R[-6]C[2]"
Range("B9").Select
Else
Range("B8").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("SLA") Then
Range("B10").Select
ActiveCell.FormulaR1C1 = "='SLA'!R[-8]C[2]"
Range("B11").Select
Else
Range("B10").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Nature Reserves") Then
Range("B11").Select
ActiveCell.FormulaR1C1 = "='Nature Reserves'!R[-9]C[12]"
Range("B12").Select
Else
Range("B11").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("SSSi") Then
Range("B14").Select
ActiveCell.FormulaR1C1 = "='SSSi'!R[-12]C[14]"
Range("B15").Select
Else
Range("B14").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("TPO") Then
Range("B15").Select
ActiveCell.FormulaR1C1 = "='TPO'!R[-13]C[8]&""
""&'TPO'!R[-13]C[11]"
Range("B16").Select
Else
Range("B15").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Waste") Then
Range("B16").Select
ActiveCell.FormulaR1C1 = "='Waste'!R[-14]C[5]"
Range("B17").Select
Else
Range("B16").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Mineral") Then
Range("B17").Select
ActiveCell.FormulaR1C1 = "='Mineral'!R[-15]C[12]"
Range("B18").Select
Else
Range("B17").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("HSE") Then
Range("B18").Select
ActiveCell.FormulaR1C1 = "='HSE'!R[-16]C[6]"
Range("B19").Select
Else
Range("B18").Select
ActiveCell.FormulaR1C1 = "None"
End If

If SheetExists("Notices") Then
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(""Breach Of Condition"",Notices!C[10],Notices!C[2])"
ActiveCell.Offset(1, 0).Range("A1").Select
Range("B7").Select
Range("B6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Range("B6").Select
If ActiveCell.FormulaR1C1 = "#N/A" Then
ActiveCell.FormulaR1C1 = "None"
End If
Else
Range("B6").Select
ActiveCell.FormulaR1C1 = "None"
End If
If SheetExists("Notices") Then
Range("B9").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(""Enforcement Notice"",Notices!C[10],Notices!C[2])"
ActiveCell.Offset(1, 0).Range("A1").Select
Range("B10").Select
Range("B9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Range("B9").Select
If ActiveCell.FormulaR1C1 = "#N/A" Then
ActiveCell.FormulaR1C1 = "None"
End If
Else
Range("B9").Select
ActiveCell.FormulaR1C1 = "None"
End If


Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

End Sub
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function


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