none
Test for 3 conditions, then print a string 5 cells to the right. RRS feed

  • Question

  • I put together a little script to look at a string in cells in ColumnD, and print a value 5 cells to the right.

     

     

    Dim cell As Range, rng As Range

    Set rng = Range("D1:D" & Cells.SpecialCells(xlCellTypeLastCell).Row)

    For Each cell In rng

        cell.Select

        If cell.Value Like "*ibes*" Or cell.Value Like "*IBES*" Then

            ActiveCell.Offset(0, 5).Value = "cx-db-a"

        End If

    Next

     

     

    So, I thought that was the end of it, but my colleague said he wants to look in ColumnA for the same company, and if there is ‘xf_est’ in ColumnE . . . . then run the code above.

     

    It looks like this:

     

    ColumnA             ColumnB             ColumnC                          ColumnE

    cseursch1                                                                            ul_est

    cseursch1                                                                            ul_est

    cseursch1                                                                            xf_est

    cseursch1                                                                            xf_est

    cseursch2                                                                            ul_est

    cseursch2                                                                            ul_est

    cseursch3                                                                            ul_est

    cseursch3                                                                            ul_est

    cseursch3                                                                            xf_est

    cseursch3                                                                            xf_est

    cseursch3                                                                            xf_est

    cseursch4                                                                            ul_est

    cseursch4                                                                            ul_est

     

    So, I'm trying to test for a CHANGE in ColumnA, and then look for 'xf_est' in ColumnE

    THEN . . . Like "*ibes*" Or cell.Value Like "*IBES*" in ColumnD

    THEN . . . print "cx-db-a" 5 cells to the right



    Does it make sense?  Let me know if you need some clarification.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, October 9, 2014 1:56 PM

All replies

  • Can you please elaborate on this : So, I'm trying to test for a CHANGE in ColumnA, ?

    What is it that you are actually looking for in Column A?

    Thursday, October 9, 2014 2:49 PM
  • Right, so when ColumnA changes form this:

    'cseursch1'

    To this:

    'cseursch2'

    That is the name of a virtual machine.  I know there are 2 'xf_est' for 'cseursch1' and 0 for 'cseursch2'

    THEN . . . Like "*ibes*" Or cell.Value Like "*IBES*" in ColumnD

    THEN . . . print "cx-db-a" 5 cells to the right

    I know it's confusing.  I know.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, October 9, 2014 3:15 PM
  • Please put more effort into explaining as I still don't get it....
    Thursday, October 9, 2014 3:52 PM
  • Sorry, I know a picture is worth 1,000 words.


    I have a VBA procedure that goes through all cells and does all kinds of operations on servers that we maintain.  A colleague just came to me yesterday and asked to add some logic to the code to check for "*ibes*" in ColumnD and "*xf*" in ColumnE and if it's in the same group as ColumnA, make sure 'cx-db-a' is printed in ColumnI.

    Do you see the 4 groups in ColumnA?  Those are 4 servers.  Then we have some connection strings and other stuff.

    So, if "*ibes*" in ColumnD and "*xf*" in ColumnE and if it's in the same group as ColumnA, make sure 'cx-db-a' is printed in ColumnI.  Either 'cx-db-a' or 'cx-db-b' can be in ColumnI if the rules aren't met.  But if the rules are met, 'cx-db-a' must be in ColumnI.


    I'm testing some ideas, but I haven't gotten anything working yet. 

    Thanks.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.



    • Edited by ryguy72 Thursday, October 9, 2014 5:04 PM
    Thursday, October 9, 2014 5:02 PM