Posts by sofusok

    Re: VBA, Search and replace strings in range, multiple variables

    Hi again

    With inspiration from this post : , I think I found the answer to my post.

    This code does the required search and replacement:

    Hope this could be to inspiration for others.. and thank you to Will Riley for easy-to-understand replies.

    Hi Forum
    I have a table "Data" with a "Date" and "Description" column in the sheet "Data".
    In the sheet "Keywords" I have the table "Keywords" with columns called "Code" and "Keyword".

    Now, I need to search the "Description" column for the values in the column "Code" in the "Keywords"-table. If a match is found, then the "code" part of the string should be replaced with the content of the cell in the Keyword-column.
    An example - please also see attached sample workbook:
    "Data" table in sheet "Data":
    Date - Description
    01-03-2013 - Bla bla bla 287494940 4107683F
    05-03-2013 - Bla bla bla Aft859065384 06590888K
    06-03-2013 - Bla bla bla 103206 2688077F

    "Keywords" table in sheet "Keywords"
    Code - Keyword
    4107683F - Shop A
    06590888K - Shop B
    2688077F - Shop C

    After running the code, the "Data" table in sheet "Data" should look like this:
    01-03-2013 - Bla bla bla 287494940 Shop A
    05-03-2013 - Bla bla bla Aft859065384 Shop B
    06-03-2013 - Bla bla bla 103206 Shop C

    In my "Data" table, there can be multiple rows with the same "Code" that needs to be replaced with the "Keyword" value. E.g. multiple rows in the "Data" table can contain "4107683F" that needs to be replaced with "Shop A".

    Hope you Excel wizards can point me in the right direction - I've toyed around with Find and replace, but can't seem to find any posts on how to loop through two ranges and replace values.

    Re: Find matching values across tables and return values from another column

    Hi jindon
    The step debug did indeed help my understanding of how your code works. I've been trying to adjust it to insert both a category and a subcategory if a keyword is present in the description. I ended up just copying your code and then adjusting the column numbers to take the subcategory column instead of the category column. It works fine but I guess there is a more elegant way of achieving the same. Thanks for your patience with me.

    Re: Find matching values across tables and return values from another column

    Hi jindon
    I didn't know about Scripting.Dictionary, but it seems to be a clever way to go about checking for Keywords in the Description column.
    I need the code to first check if the cell in the Category column is empty, i.e. the Category is not already filled in. If the Category is blank then run your code, if Category is already filled in, then skip this row. In order to make this adjustment I am trying to understand what your code does. Could you explain it to me by breaking it down? Sorry for being such a just trying to understand and learn from your wisdom. Thanks for sharing.

    Re: Find matching values across tables and return values from another column

    Thanks for your quick replies, am very grateful.

    Andrew - I guess I should go with VBA and not formulas then. Yet another possibility for me to learn.

    jindon - thanks for your example. It does exactly what I need. Brilliant. Maybe you could explain how you've done it, so I and others who read this thread can learn from it?

    Thanks again, really appreciate it.

    Hello Excel wizards,

    Have searched various forums and have looked at Vlookup and Index-match as well as other formulas, but have not figured out how to solve my problem. Any help is highly appreciated. It would also be helpful if you could point me to relevant posts or tell me if it is best done with formulas or VBA, so I can narrow my search.

    Have attached a sample workbook and here is what I am trying to accomplish:

    On sheet "Data" I have a table "Transactions" with a list of transactions, each with columns: A: Date, B: Category (blank), C: Description and D: Amount.
    On sheet "Keywords" I have a table "Keywords" with columns: A: Keywords and B: Category (the corresponding category for each keyword)

    I want to search for the Keywords (listed in the "Keywords" table) in the Description column (in the "Transactions" table) and fill out the correct category in the "Transactions" table.
    In the "Keywords" table I have a Keyword: "Train" and the category is "Transport"
    In the "Transactions" table I have a Description: "Return ticket here to there, This train station".
    Now I want the "Category" cell to be filled out with "Transport" for this transaction.

    See attached file for more examples.

    Re: Set dynamic range to copy in for loop (looping in reverse through sheets)

    Hi Shree,

    Thanks for taking your time to help me.

    I got it to work by editing this line of code:

    With sht
               Set SourceRng = Sheets(i).Range("c12", Sheets(i).Range("C65536").End(xlUp).Offset(0, 4)) 
            End With

    As I'm learning..could you please explain why I have to use sheet names before the range variables?
    For example, I thought I'd defined that TargetCell was on the Sheet "SUMMARY" in the line

    Set TargetCell =  ThisWorkbook.Sheets("SUMMARY").Cells(Rows.Count,  "C").End(xlUp).Offset(1, 0)


    Thanks again for your assistance, great to be able to access this pool of expertise.


    I'm using a for loop to copy a dynamic range from each worksheet (except the first 3 sheets in workbook) and paste it into the sheet("SUMMARY"). The loop must go in reverse, starting from the last sheet in the workbook (more sheets will be added).

    What I need help to:
    I get a Runtime Error 91 Object variable or With block not set, when I set the range I want to copy (SourceRng).

    Any help is appreciated.

    Re: Copy dynamic range from multiple sheets to summary sheet

    Hi junho,

    Thanks..that was the detail I missed! It now loops through the sheets.
    However, I can see that the 'for each' loops from left to right, whereas I need it to start with the last sheet and move to left.
    Having done some research I can see that a 'for each' loop cannot loop in reverse, so I'm working on getting it to loop through the sheets with a 'for next' loop.
    Might have to start another thread for help on that one, but will see what I can find first.
    Anyways, thanks for your help - it's a great way for me to learn more.

    As you can probably see from my code I'm a newbie who've been searching forums for help but now I'm stuck, so I hope you can point me in the right direction from here, wanting to learn!

    What I have:
    A workbook with daily draft sheets (named with today's date) and a summary sheet (and a hidden template sheet and a visible frontpage sheet).

    What I want (and can't get to work):
    Copy a range from the daily draft sheets to the Summary sheet.
    The range I want to copy is from C12 to the last used cell in column C and incl. columns C to G.
    This range (SourceRng) must be copied to the Summary sheet, in the cell one row below the last used cell in column C.
    Starting with the last sheet (lowest date) this range must be copied and then the next sheet etc., e.g. SourceRng on sheet "06-12-10" copied to Summary, then SourceRng on sheet "07-12-10" copied to Summary sheet etc.

    Couldn't attach the file because it is 1,05 MB.
    Any help is deeply appreciated.

    Re: Sum Several Workbooks

    Hi Dave
    Probably nothing wrong with those functions..I just don't know how to use them so that the data for each weekly status from every department is summed up when I click 'Update' in the weekly status file..
    I assume the Sum / SumIF must be inserted in the table in the Total workbook ? I know how to use the sum function for data that is already available, but how do I make sure that when a new round of weekly statuses are made that these are also added in a new table row in the Total workbook? I have tried to record a macro to see the code but it is quite difficult since I don't know how to perform the task 'manually', i.e. I can't get excel to sum up the data in the tables in the different weekly status workbooks into a table in the total file.

    Hope you can bear with my ignorance. I really hope someone can help me out.
    (Unfortunately I don't have much experience with Excel / VBA..but am eager to learn)
    Just to clarify - the code used in the weekly status is not my work, it's way out of my league.
    Best regards


    I've searched for other threads that could help me out, but I have not suceeded in finding a code that will help me out. I therefore turn to your help, hoping you can help me..

    I've got a workbook for every department which is used to make a weekly status. Each of these workbooks contains a sheet with the 'weekly statuses over time', i.e. a table showing the weekly status for the department for week 1, week 2, week 3 etc.
    I've attached the file to make it easier to understand what I'm talking about, see Weekly_Test.
    Currently I can only see the performance of each department but I would like to be able to see the total performance for all departments, i.e. have a 'Total workbook' where all the weekly statuses are summed up weekly.

    In the Weekly_Test file you can see the code which is used to generate the 'weekly status' sheets (also inserted at the end of this post). All workbooks for the departments are the same, apart from the file name which is the name of the department and this does not change.

    The weekly status is generated in the following way:
    In the sheet 'Indtastning' (data entry) you enter the data and the date. Then click 'Update'. The sheet is then copied to a new sheet with the date and all data is copied to the table in the 'his.status' sheet.
    Now, I would like to add some code so that when I click 'Update' the data which is copied to the table in the 'his.status' sheet is also copied to a similar table in the 'Total' workbook.
    Then when I make the weekly status for department 2, the data is also copied to the 'Total' workbook - but not in a separate table row, but added to the data from Department 1. Since the weekly status is done on the same weekday for all departments, the date should be used to tell the program where to copy the data to.

    To solve this I've looked at threads about copying data to a closed workbook, however it seems that it is much faster (and easier) to make a code where the file is opened without it being shown on the screen.

    My biggest challenge is how to tell the program to sum up the data from each department's weekly status in one table row, determined by the date.

    Urgent help is needed - and very very much appreciated!

    The code from the Weekly status: