How to calculate error for squared data?

Using array to import data

• QUESTION: dear sir: Recently i engage in share business. Merchant banks in our country provides loan against securities of those companies which have P/E(price-earning ratio) less than 40. There are lots of companies against which loans are not given. In order to calculate the P/E ratio of some selected companies, i have to work hard. So i appear before you with a problem. In sheet1 almost 500 shares names with price information are given. I know which companies share are eligible to get margin (loan). so i want to search those companies from sheet1 C5:C505 and put in on Sheet2 C5:C505 range. for Example, C5 G5 Names close price ABBANK 1011.25 ACI 650.20 ACIFORMULA 2586.25 ACIZCBOND and so on AFTABAUTO AGNISYSL AGRANINS AIMS1STMF AL-HAJTEX ALARABANK ALLTEX ALPHATOBA AMBEEPHA AMCL(PRAN) ANLIMAYARN ANWARGALV APEXADELFT APEXFOODS APEXSPINN APEXTANRY APEXWEAV and so on from c5:c500 i want to import few of the selected companies names and close prices to sheet2 b5:b500 (for names) and d5:d500 (close Prices) I know that selecting few companies using Array() function could be easy. But what would be the VBA code i don't know. Please help in this regard. Thank you very much for your kind support in every aspect of excel. ANSWER: Badboy, >I know which companies share are eligible to get margin (loan). so i want to search those companies how do I know or how does VBA know which companies to search for? I will assume you will hard code the list in the macro since your subject mentions an array. Sub CopyItems() Dim rw As Long, v As Variant Dim i As Long, cell As Range Dim bfound As Boolean v = Array("ANWARGALV", "APEXSPINN", "APEXWEAV") rw = 5 For Each cell In Worksheets("Sheet1").Range("C5:C505") bfound = False For i = LBound(v) To UBound(v) If LCase(cell) = LCase(v(i)) Then bfound = True Exit For End If Next If bfound Then cell.Copy Worksheets("Sheet2").Cells(rw, "B") cell.Parent.Cells(cell.Row, "G").Copy Worksheets("Sheet2").Cells(rw, "D") rw = rw + 1 End If Next End Sub worked for me using your data and the array you see in the code. ---------- FOLLOW-UP ---------- QUESTION: it works for me. Only one question is that i am using array to categorize the data. i am using your code to import my desired data from 'Sheet2' to "Industrywise' form. while copying it returns the following message. (=#REF!/C6) error. please bring necessary change to copy my desired data as just values not as formula. I want to copy sheet2 B column and H column and paste it as value not formula to the 'Industrywise' sheet's B column and D column.

• Answer:

  Badboy, here is the adjustment: Sub CopyItems() Dim rw As Long, v As Variant Dim i As Long, cell As Range Dim bfound As Boolean v = Array("ANWARGALV", "APEXSPINN", "APEXWEAV") rw = 5 For Each cell In Worksheets("Sheet1").Range("C5:C505") bfound = False For i = LBound(v) To UBound(v) If LCase(cell) = LCase(v(i)) Then bfound = True Exit For End If Next If bfound Then ' do a paste special values rather than a straight copy and paste cell.Copy Worksheets("Sheet2").Cells(rw, "B").Pastespecial xlvalues cell.Parent.Cells(cell.Row, "G").Copy Worksheets("Sheet2").Cells(rw, "D").Pastespecial xlvalues rw = rw + 1 End If Next End Sub