Posts by mythu

    There are multiple Workbooks in a folder and have similar column which Header name is "Name" but in each file column position is chnaged.

    I want to search that header in 1st row of each workbook if finds then copy that entire column from multiple workbooks availble in Folder and Paste Unique result (values) into an open workbook where from code is being run.

    There is one more thing that i want to extract multiple column by Header please add Array method so i can add more column name. I have attached 3 workbooks and result file

    I would appreciate your help.

    Yes you're right COUNTIF is more faster than VBA but that time COUNTIF is not giving an accurate answer. You can try on attached file.

    First i used COUNTIF then SUMPRODUCT since countif answers were wrong then went to sumproduct which was perfect but its processing were slow over large datasets.

    Then found above code. I would appreciate if you could help to modify the above code.

    I am using CountIF VBA version which gives me ease due to its fast calculation while working with the large datasets. My Data has text values as well as numerice values so it is basicaly a combination of different values.

    I just want to count the number of repeated values but my function is giving wrong result as you can see in attached file.

    I would appreciate if someoene can look into it and make it more efficent in terms of:

    Accurate count for repeated values

    Code Speed

    and the last thing is i want to use this code in Personal.XLSB so i can recall it using shortkey.

    I hope to get help. Thanks

    I have been using these two codes CellVal and Private Sub Worksheet_Change(ByVal Target As Range)

    When i run the code CellVal that updates the next ID in the Sheets("Sheet1").Range("B6") if B6 is empty then it loads the first ID and set a trigger on cell B6 for second code, that whenever that cell is update second code is run.

    Explaining second code

    when 1st ID is load in Cell B6 then second code runs and copy the Sheet2.Range(B4) and Paste into Sheet3.Range(B4)

    When 2nd ID is load in Cell B6 then second code runs and copy the Sheet2.Range(B4) and Paste into Sheet3.Range(C4)

    When 3rd ID is load in Cell B6 then second code runs and copy the Sheet2.Range(B4) and Paste into Sheet3.Range(D4)

    same for 4th and 5th and 6th and 7th`and 8th

    Now the problem is that i run the code 8th times to load the next ID in Sheets("Sheet1").Range("B6") I want a help that How to set a trigger for code CellVal in sheet3 that ID will update automatically. If it got resolved then i have to run the code one time to update all values one by one rather than running the code 8th times.

    Any help will be appreciated.

    Thank you very much rory for sure Arrays are better. But second issue is still exists that if i run the Module1 code the result comes with error #VALUE! rather than output.


    I have a problem, hopefully someone may be able to help. I have been using MS Office 2016 wherein TEXTJOIN function does not work so i have added EXCEL UDF Function to make it work like TEXTJOIN works.

    Here is the formula


    I have pasted this formula through VBA but it returns with an error that is #VALUE! I have attached a file below wherein UDF and VBA formula both codes are available.

    The code is extremely slow and is it possible to convert this below range into used range like we mostly used lastfor = Sheet2.Cells(Rows.Count, 148).End(xlUp).Row might speed could be better this way if i am not wrong.


    I will really appreciate the help.