return value to multiple columns based on specific text from a specific column

  • Hello,


    I need help with creating a macro that will populate two other columns based on text criteria from column A. For example, if specific the text in column A matches "abc.com", then column B should be populated with "fakecompany1" and column C with "fakesegment1".


    I tried to go with the code below to at least have column B populated, but the problem is that this way the value is locked in the cell and when cell value changes (which it with every new export) it will show "n/a" instead of identifying specific value in the entire column and when i tried to add "and" for the column C to be populated it was returning errors. So i need a code that will query whole column A for "abc.com" and then based on that will populate column B with "fakecompany1' and column C with "fakesegment1".




    Thank you for your help

  • Hello Fullhouse,


    You don't need the "AND" operator, so modifying your loop type code as follows may help:-

    You'll note that the last row has been defined so the code will loop each cell in Column A to the last row (the rows are defined by the variable 'i'), find the value 'abc.com' and add the text to Columns B and C. Based on the sample that you have supplied, Sheets("Sheet2") has the sheet code 'Sheet1' which I have used above.


    In this line of your code:-

    Code
    If Worksheets("Sheet1").Cells(2, 1).Value = "abc.com" Then

    I'm not sure if Worksheets("Sheet1") is a typo on your part.


    A loop type code will work fairly quickly on a relatively small data set but if your data set is large, or could grow to be very large, using the AutoFilter will be a far better option. For example:-


    I hope that this helps.


    Cheerio,

    vcoolio.

  • Hello vcoolio,



    Sheet1 was a typo ...


    I had no clue that i do not need "and" there and was wondering why this was not working properly...


    I will go with the autofilter option, because the data set is large and it is taking forever to run .. I had no clue that was even an option


    I am new to vba so i truly appreciate your help and detailed explanation and suggestions. Thank you for your help with the macro and for saving a little bit of sanity that i have left ^^

  • I finished the full code with the regular loop because I got issues with offset and until i figure that out, at least i will have something that will populate all the data.


    Considering that in real scenario i have few hundred domains, i went with the range of column, instead of the cell as this is what i originally was asking for and finaly was able to find reference for it elsewhere . Imagine having 100k rows and at the row40k you have a domain ... so to avoid complications on the cell locking i was happy to find references to the column. Please keep in mind that the ranges are based on the real sheet with the column E as domainname, D as OpCo and C as Segment.



    Code
    If Range("E" & i).Value = ("abc.com") Then
    Range("D" & i).Value = "fakecompany1"
    Range("C" & i).Value = "faksegment1"


    I am not sure how to do the offset on the full column range instead of locking it on the cell considering the amount of unique domains and their locations cell wise.


    My understanding is that if abc.com is in the column E and i want fake company in the column D, then the column offset is -1, but I am not sure what do i put on for row? because abc.com will be inconsistent because with every new export, the only constant is the column for the values, and not cells.


    I am sorry, i am at the very beginning of learning vba , so i may be a bit slow

  • Hello Fullhouse,



    Quote

    My understanding is that if abc.com is in the column E and i want fake company in the column D, then the column offset is -1, but I am not sure what do i put on for row?

    For each instance of abc.com, the offset for the same row but the previous column(D) is written as:

    .Offset(, -1)


    However, based on your last post, it appears that the sample you supplied is not exactly what you are working with so the best option is for you to upload a sample of your workbook which is an exact replica of your actual working workbook. If your data is sensitive then please use dummy data. We'll only need a dozen or so rows of data to test with. Please also include the code that you are presently working with. This will make it much easier for us to resolve this for you and will spare us guessing at what is supposed to actually be happening.


    Cheerio,

    vcoolio.

  • I got it working!!!!!!


    In the attached sheet, i posted screenshots of the criteria, desired results and the raw data table in the case someone else needs something similar to this.


    Also the code is completed with the fake data sample. Thank you for the clarification on the offset part, that was pretty confusing when i was reading about it, so you clarifying it, helped tremendously,


    I know that this looks very simple to you and other experienced folks, but I am almost dancing out of happiness as this is almost rocket science for me:) .. I guess there is still hope for me :D


    Thank you for your guidance, patience and pointers. It helped a lot!!!!!:thumbup:<3

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • fullhouse make sure that you read the Forum Rules before posting again.


    There is no nee for VB A to do this, you can easily use formulas - IF or IFS.

  • Hello Fullhouse,


    You're welcome and I'm glad to have been able to assist.

    I'm really happy that you spent the time foraging through information and working this out basically on your own. I laud you for that.


    If you're interested, below is a condensed version of your code (but excluding the date part that you added in):-


    If you're willing to do a bit more research, what I've done in the above code is extract all the unique values in Column E using the AdvancedFilter and temporarily placing them in Column M. These unique values are then placed into an array(ar) which is then looped through and filtered for each unique value. This prevents many, many iterations which in turn speeds up the code and, as you stated earlier, you have around 100K rows of data so the saved iterations would be in the thousands. I tested this code on 150K rows and it took about three seconds to execute on my machine.

    Column M is cleared at the end of code execution.

    As you can see in the code, I've used 'IF' statements to identify the unique values for processing once filtered. You may want to research 'case statements in VBA' if you have the time. 'Case statements' can be used in place of 'IF' statements and may actually work more quickly.

    Anyway, once again "well done" and good luck with your project.


    I've attached your sample workbook with the above code implemented just so you can see how it works. There are about 230 rows of data in the sample.


    Cheerio,

    vcoolio.


    Fullhouse.xlsm

  • fullhouse make sure that you read the Forum Rules before posting again.


    There is no nee for VB A to do this, you can easily use formulas - IF or IFS.

    Hello Roy,


    Please accept my apologies for not adding the code tag. I will read through forum rules so this does not happen again..

  • If you're willing to do a bit more research, what I've done in the above code is extract all the unique values in Column E using the AdvancedFilter and temporarily placing them in Column M. These unique values are then placed into an array(ar) which is then looped through and filtered for each unique value. This prevents many, many iterations which in turn speeds up the code and, as you stated earlier, you have around 100K rows of data so the saved iterations would be in the thousands. I tested this code on 150K rows and it took about three seconds to execute on my machine.

    Column M is cleared at the end of code execution.

    As you can see in the code, I've used 'IF' statements to identify the unique values for processing once filtered. You may want to research 'case statements in VBA' if you have the time. 'Case statements' can be used in place of 'IF' statements and may actually work more quickly.

    Anyway, once again "well done" and good luck with your project.

    Thank you very much. I will be reading about case statements and also "ubound" tonight and then adjust the code accordingly.


    THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Participate now!

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