Posts by sofusok

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

Hi again

With inspiration from this post : http://www.ozgrid.com/forum/showthread.php?t=29700 , I think I found the answer to my post.

This code does the required search and replacement:

Sub String_Replacer()
    Dim ws As Worksheet, wb As Workbook
    Dim fList As Variant, I As Integer
    Dim rng1 As Range
    Dim rng2 As Range
    Dim cel As Range
    Dim strMyChar As String, strMyReplace As String
     
    With ActiveWorkbook.Worksheets("Keywords")
        Set rng1 = .[a2:a4]
    End With
    With ActiveWorkbook.Worksheets("Data")
        Set rng2 = .[b2:b4]
    End With
       
             'loop down list of text needing replacing
            For Each cel In rng1.Cells
                strMyChar = cel.Value
                strMyReplace = cel.Offset(0, 1).Value
                 'replace text
                With rng2
                    .Replace What:=strMyChar, Replacement:=strMyReplace, _
                    SearchOrder:=xlByColumns, MatchCase:=True
                End With
                 'next word/text to relace
            Next cel
        
End Sub

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

Thank you very much jindon. It works perfectly. I will now dedicate my time to figuring out how it works...
Really appreciate your help and quick replies.

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 newbie..am 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.
E.g.
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.

Hi,

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

Option Explicit


Sub summary()
Dim sht As Worksheet
Dim i As Integer, SheetC As Integer
Dim TargetCell As Range 'cell just below last used cell in column C on sheet "SUMMARY"
Dim SourceRng As Range 'range starting in C12 to last used cell in column C and including columns C to G


With Application
.ScreenUpdating = False


SheetC = Sheets.Count


For i = SheetC To 4 Step -1
Set TargetCell = ThisWorkbook.Sheets("SUMMARY").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        With sht
'this is the line I have troubles with
        Set SourceRng = .Range("c12", .Range("C65536").End(xlUp).Offset(0, 4)) 
        End With
        SourceRng.Copy TargetCell
Next i


.CutCopyMode = False 'clear clipboard
.ScreenUpdating = True
End With


End Sub

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

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

Option Explicit


Sub summary()
Dim sht As Worksheet
Dim TargetCell As Range 'cell just below last used cell in column C on sheet "SUMMARY"
Dim SourceRng As Range 'range starting in C12 to last used cell in column C and including columns C to G


With Application
.ScreenUpdating = False


For Each sht In ActiveWorkbook.Worksheets


    Select Case sht.Name
        Case "FRONTPAGE", "SUMMARY", "Template"
           'Skip these sheets by doing nothing
        Case Else
      'can't get this part to work..
      Set SourceRng = Range("c12", Range("C65536").End(xlUp).Offset(0, 4))
      Set TargetCell = ThisWorkbook.Sheets("SUMMARY").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
            SourceRng.Copy TargetCell
    End Select
Next sht


.CutCopyMode = False 'clear clipboard
.ScreenUpdating = True
End With


End Sub

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

Hello

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:

Dim indtastArk
Dim arkDato
Public Sub Opdater()
    arkDato = Cells(1, 1)
    
    OpretDatoArk
'    nulStilIndtastning
    overførTilStatus
End Sub
Private Sub OpretDatoArk()
    Sheets("Indtastning").Activate
    With ActiveSheet
        .Cells.Select
        Selection.Copy
    End With
    
    ActiveWorkbook.Sheets.Add Before:=Sheets(4)
    Sheets(4).Name = arkDato
    
    Sheets(4).Activate
    ActiveSheet.Cells(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
       
    Application.CutCopyMode = False
    
Rem ændring af farve for indtastningsfelter
    For Each cc In ActiveSheet.Range("A3:G26").Cells
        If cc.Interior.ColorIndex = 6 Then
            cc.Interior.ColorIndex = 20
        End If
    Next cc
    
    ActiveSheet.Cells(1, 1).Select
End Sub
Private Sub nulStilIndtastning()
    Set indtastArk = ActiveWorkbook.Sheets("Indtastning")
    
    For Each cc In indtastArk.Range("A3:G29").Cells
        If cc.Interior.ColorIndex = 6 Then
            cc.ClearContents
        End If
    Next cc
End Sub
Private Sub CommandButton1_Click()
    Opdater
End Sub
Private Sub overførTilStatus()
Dim næsteRæk, statusArk, aktuelleArk
    Set statusArk = ThisWorkbook.Sheets(2)
    Set aktuelleArk = ThisWorkbook.Sheets(4)
    
Rem Find første ledige række
    With statusArk
        For ræk = 3 To 65000
            If .Cells(ræk, 1) = "" Then
                næsteRæk = ræk
                Exit For
            End If
        Next ræk
        
        .Cells(næsteRæk, 1) = arkDato                       'A - kol
        .Cells(næsteRæk, 2) = aktuelleArk.Range("E4")       'B
        .Cells(næsteRæk, 3) = aktuelleArk.Range("B4")       'C
        .Cells(næsteRæk, 4) = aktuelleArk.Range("D3")       'D
        .Cells(næsteRæk, 5) = aktuelleArk.Range("D4")       'E
        .Cells(næsteRæk, 6) = aktuelleArk.Range("B12")      'F
        .Cells(næsteRæk, 7) = aktuelleArk.Range("B15")      'G
        .Cells(næsteRæk, 8) = "'" & (aktuelleArk.Range("C18") & _
            "/" & aktuelleArk.Range("D18"))                 'H
        .Cells(næsteRæk, 9) = aktuelleArk.Range("B21")      'I
        .Cells(næsteRæk, 10) = "'" & (aktuelleArk.Range("D21") & _
            "/" & aktuelleArk.Range("E21"))                 'J
        .Cells(næsteRæk, 11) = aktuelleArk.Range("B24")     'K
        .Cells(næsteRæk, 12) = "'" & (aktuelleArk.Range("B27") & _
            "/" & aktuelleArk.Range("C27"))                 'L
        .Cells(næsteRæk, 13) = aktuelleArk.Range("E1")      'M
        .Cells(næsteRæk, 14) = aktuelleArk.Range("F3")      'N
     End With
End Sub