VBA to search column A and return Column B

  • What I am needing to do is search Sheet 1 for specific text and return value from column B. Here is an Example of what I have:
    Sheet 1:
    [TABLE="width: 500"]

    [tr]


    [td]

    CMTS

    [/td]


    [td]

    Node

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    ABC1

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    ID5

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    JOE7

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    MISS9

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    JACK3

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    SEE4

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    PRIDE8

    [/td]


    [/tr]


    [/TABLE]


    Sheet 2
    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    CMTS 1

    [/td]


    [td]

    CMTS 2

    [/td]


    [td]

    CMTS 3

    [/td]


    [td]

    CMTS 4

    [/td]


    [td]

    CMTS 5

    [/td]


    [/tr]


    [tr]


    [td]

    ABC1

    [/td]


    [td]

    JOE7

    [/td]


    [td]

    JACK3

    [/td]


    [td]

    PRIDE8

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    ID5

    [/td]


    [td]

    MISS9

    [/td]


    [td]

    SEE4

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    I have tried to use VLOOKUP, SEARCH, & ISNUMBER statements but cant get the results I need. Any help will be greatly appreciated.

  • Re: VBA to search column A and return Column B


    Will the resulting table always have only 5 columns (in other words there can never be a CMTS 6, or any number greater than CMTS 5)?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA to search column A and return Column B


    You can try this array formula in A2 of sheet2 and copy down and across.


    =IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!$A$2:$A$8,VALUE(SUBSTITUTE(A$1,"CMTS ",""))),INDEX(Sheet1!$B$2:$B$8,SMALL(IF(Sheet1!$A$2:$A$8=VALUE(SUBSTITUTE(A$1,"CMTS ","")),ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A2))),"")


    [arf]*[/arf]

  • Re: VBA to search column A and return Column B


    Neat formula Stephen! :)


    Attached is a file with a VBA solution working. Click the button on Sheet2.


    Row 1 on Sheet2 is the header row, the code will insert the data from Sheet 1 below those headers.


    Code assigned to the button is:

  • Re: VBA to search column A and return Column B


    To make Stephen's formula dynamic for any number of rows of data on sheet 1 try this (also an array formula so confirm with CTRL+SHIFT+ENTER).


    =IF(ROWS(A$2:A2)<=COUNTIF(OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A)),VALUE(SUBSTITUTE(A$1,"CMTS ",""))),INDEX(OFFSET(Sheet1!$B$1,1,,COUNTA(Sheet1!$B:$B)-1),SMALL(IF(OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A))=VALUE(SUBSTITUTE(A$1,"CMTS ","")),ROW(OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A)))-ROW(Sheet1!$A$2)+1),ROWS(A$2:A2))),"")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!