Posts by mchilapur

    Re: VBA_To find a cell value in range_Worksheet change event

    Thanks a lot for your response...But i have one more small issue.
    Your Code targets column 3 only, but i have an situation where in user can paste data from column 1 to column 3 from other database..
    To put it in simple words, we have database where data is available till first 3 columns..Users copy them and paste it till first 3 columns..
    During this situation you code doesnt appear to update 'Single' and "multiple'.

    Please help me to fix this..
    many thanks in advance...

    Dear all,
    Somewhere i got messed up with below code. Below is something what i am trying to achieve.

    Whenever a user enters new value in any cell of column C:C, vba code must check for only that call value already exists in entire column C:C, if its found, then it must set corresponding column I:I cell value "Multiple". If not found, it must set to "Single".

    Challenges faced:
    1) Currently i am looping through entire C:C column which is not correct. I want value for cell in column I:I to be updated for new entries in columns C:C only.
    (I have values update 5000 + rows..So looping slows down the process)
    2) User may enter multiple cell values in one shot (Like, he copies 10 rows of data from some other database and pastes it directly into Excel sheet upto column H:H).

    How to i achieve it..? Please help..Attached is the sample work book and has got code it (Which basically doesn't work).

    Please note: This post is a cross post from below link.…rksheet-change-event.html

    Re: Excel VBA to extract part of string and concatenate them with "," (Unique values

    Hello Stephen,
    Now i realized by mistake...Thanks your code..:)
    And sorry for cross-posting..I am not among those who just put in their concern and let others work on it..
    I try to understand the logic and realize where i commit mistake..

    Your code still make "ALS" as duplicate entry....

    I think it still requires modification..Please help..

    Re: Excel VBA to extract part of string and concatenate them with "," (Unique values

    Thanks for your response..
    I want this value to be stored as a variable..I am upto the below code, but i am failing to pull UNIQUE values.
    Can anyone please help..?

    Dear all,

    In the data available in attachment, i am trying to extract starting 3 letters of each cell value and add only UNIQUE values with ',' separator.

    I tried to find logic with below code but i know it fails.
    Can anyone please help me to correct it or provide fresh code to achieve the intent.
    Output required is as shown in the attachment.

    Many thanks.

    Just in order to get best possible solution, this querry is cross posted @

    Dear all,

    Is it possible to make a cell hyperlinked to samesheet..?

    I have recorded a macro below, When i run this procedure it makes the cell hyperlinked. But when i key-in in some other procedure, its doent make it hyperlinked.
    I need to keyin this code in some other module, which at the end must make the cell hyperlinked

    Any suggestions..?

    Dear all,

    PLease review the attachment and code it carries in it.

    This code counts the data from sheet2 and stores it in 'Count' variable and in-turn inserts that value in sheet1 (D3).

    I was this D3 value (Basically count value) to be hyper-linked to Data it counted. Is it possible to achieve..?

    My basic intent is, if the user clicks on this value (hyperlinked) he must get all the data of that count.

    This is first time i am trying this kind of, kindly help.
    Any alternate suggestions are most welcome.


    Hello all,
    I use below code to select header by name "XYZ".
    Few times it gets changed to "XYZA".
    I need "Application.Match" code to select either of these header whichever is available, Like Application.Match("XYZ" or "XYZA").

    How best i can modify it..?

    Cells(1, Application.Match("ABC", Range("1:1"), 0)).Select

    Re: Searching for a Solution to a Long-Standing Excel Problem

    Also you can try:

    For example Excel can get confused about the actual range of your worksheets.
    Press CTRL + END and see where the selection ends up.
    Best case scenario it will be right underneath the the bottom-most right-most used cell. However, sometimes it will be way off of that with a lot of empty space to your actual data.
    Select all the empty cells, then right-click and select Delete (this is different from pressing the DEL key!). Save your file and test again.
    Do this for all the worksheets of your workbook to clear unnecessary “empty cell” bloat.

    I already mentioned that formatting can get funky. And with more and more formatting it can get more and more problematic. If you were conservative with formatting clearing and resetting it shouldn’t take too long and might help.
    * Definitely create a backup of the entire workbook beforehand.
    For example if you have a header row with a certain formatting select the entire row, then on the Home tab in the editing group click Clear > Clear Formats. Then reset the format.
    Actually, a good place to start might be the non-formatted cells! Probably most of your data entries are in the standard format. Select all of it and clear the formats to make sure it isn’t “made to look like standard format” ~ this will also clear data formatting (e.g. Time, Date) so this is something one has to keep in mind.

    Finally I would clear the Formula error checking.
    On the Formula tab there is a Formula auditing group. Click on Error Checking > Reset Ignored errors. In general, messy formulas and a lot of nested formulas create performance hits. Now, if there is some error in one of the initial formula it will be carried through all the nested ones too creating a lot of background work for Excel

    Re: Searching for a Solution to a Long-Standing Excel Problem

    Most important question, does that happen with a new sheet/file too? Create a new one, add a couple of random formulas and data.

    If the file resides on a server try using a local copy to see if the network factor has an impact. Changes in Offices Trust Center could be causing these issues then.

    Have you tried copying the contents into a new spreadsheet (unformatted)?
    I have a small Excel sheet where I keep track of numbers from a website and unless I clear them from their formatting (via a plain text editor) it will cripple Excel down to not responding for half a minute every time I click something.

    I understand that given the size of your sheet this is probably a painful solution but you could at least try it with a partial data set to see if that has any impact – or the other way round, copy portions of your data with their formatting into a new one and see which portion of your sheet creates those performance issues.

    Dear all,
    I am trying to find a IF function with multiple criteria to get the output as "" (Blank).

    I want If function to return "", if its vlookup value is "" or #N/A.
    Below is the function i am using, but it still throws #N/A for few cells, while it must return ""


    Please find the attachment.

    Please correct my small mistake.

    Dear all,
    I am trying to define a new sheet with custom name and check if it exits before i do something with code..
    Actually in long run i will add this sheet. Before i need to make a cross check if it exists.
    But my code throws an error. Please help me fix this error.
    Below is my code..