Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: DIFFICULT -

  1. #1
    Guest

    Default

    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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Essex, England
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,021
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    Gary,

    That seems to me the right approach to the OP's question.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •