![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
Guest
Posts: n/a
|
In one file I have multiple rows and columns of data.
In the second file I am trying to write a CSE(Array Formula) which will print values into column A of file2. It should: Look at all the values in file1!B2:B200 If the value in the file1!B column is "high" then it should print the exact text of the corresponding file1!E column into the next available slot in the A column of file2. Example: in file1 b1="low" ----e1="apples" b2="high" ----e2="oranges" b3="low" ----e3="pears" b4="high" ----e4="grapes" . . . then I would like a formula that inputs in file2 column A and returns oranges in A1 grapes in A2 . . . Thanks. |
|
|
|
#2 |
|
Board Regular
Join Date: Feb 2002
Location: Essex, England
Posts: 458
|
This is a bit crude, but I think it gets you where you need to be. I can't think of a formula to do what you want as it would need to keep pointing at different cells, but try adding this to your File2 and running it.
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 27/02/02 by GaryB ' Dim myrow As Integer Dim myrow2 As Integer Dim RowEtext As String myrow = 1 myrow2 = 0 For myrow = 1 To 200 Windows("file1.xls").Activate Range("A1").Select If ActiveCell.Offset(myrow - 1, 1) = "high" Then RowEtext = ActiveCell.Offset(myrow - 1, 4) Windows("file2.xls").Activate Range("A1").Select ActiveCell.Offset(myrow2, 0) = RowEtext myrow2 = myrow2 + 1 End If Next myrow ' End Sub HTH GaryB |
|
|
|
|
|
#3 |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Gary,
That seems to me the right approach to the OP's question. Aladin |
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|