Posts by fullhouse

Quote from vcoolio

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Quote from royUK

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..

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

Thank you for your guidance, patience and pointers. It helped a lot!!!!!

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.

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 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

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".

Sub populateBnC()


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


Worksheets("Sheet2").Cells(2, 2).Value = "fakecompany1" And Worksheets("Sheet2").Cells(2, 3).Value = "fakesegment1"




Else


Worksheets("Sheet2").Cells(2, 2).Value = "n/a" And Worksheets("Sheet2").Cells(2, 3).Value = "n/a"
End If




End Sub

Thank you for your help