1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: equivalent of "if(true, myMacro..."

Discussion in 'Business Applications' started by bobwitte, Jun 2, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. bobwitte

    bobwitte Thread Starter

    Joined:
    Jun 2, 2011
    Messages:
    7
    In XL 2003, I want to download transactions to one sheet [say, "New data"] and move each line [transaction] to a different sheet - determined by the first few characters of column
    D of the new data.

    Pardon my pseudo code:

    For every Row [R] on this sheet
    For every String in a row or column [doesn't matter which] of data/parameter given to this macro
    if Left$(Row(R)Column(D),Length of String(S))=String(S) then
    Cut this Row(R)
    ActivateSheet named String(S)
    Select A3
    Paste [new row/insert line] this Row, Row(R)
    GOTO "Next Row"
    next S (string)
    ActivateSheet named "Other"
    Select A3
    Paste this Row [see "Cut" above], Row(R)
    Next Row:
    next R (row)

    I have looked at recommended info on Events and the event I want to use is my running this macro but I get the feeling that this is not available to me. that I have to use some more general Event, like a sheet being activated or every time a cell is changed, which is very much not what I want.

    I'm pretty familiar with complex "if" statements, formulas, simple [copy keystroke] macros etc in XL, but have a lot of trouble understanding anything regarding object oriented programming, eg VBA.

    Please help.
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Hi

    I want to clarify 2 points.
    1) What do you mean by
    2) How did you plan to download the transactions? I mean, is it part of the macro, or the records will have already been downloaded and placed on sheet "New Data" when the macro is started?

    Jimmy
     
  3. bobwitte

    bobwitte Thread Starter

    Joined:
    Jun 2, 2011
    Messages:
    7
    I will have already downloaded the transactions. Each transcation is a line. For each line [transaction] I want to detect/observe what is in column C [say] and move that line to a workbook page w title that matches the column C value, for example "Deposit".

    From what I've read, this kind of reference is not allowed in a macro unless the macro is started/kicked off by some 'event'. In the real world [my world] the event that should start this macro is "I've decide to run this macro" but that is not in the list.

    So the first and simplest question is "What is a good 'event' to chose [from teh list] to kick off my maacro. I don'dt recall just where this list is today, but I'll find it again if I get enough clues as to how to proceed from this forum.

    The more involved question is "How do you write this kind of macro"? or "What commands are availabel for matching a cell value to a page name and that sort of thing"?

    I am quite familiar with Basic instructions and XL use & keystroke-generated macros. I am always brought to a halt with object oriented concepts, and VBA is one of those.
     
  4. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Well, I find it strange that you would ask about this "event". It seems obvious that if you want a macro to run when you want it to run, then you should simply start it manually at the exact time you want it to run. Maybe it's only obvious to me. Anyway, starting a macro can be achieved by pressing ALT+F8 and selecting the macro from the list, or assigning the macro to any shape. A commandbutton, for example. But I expect you already know this, since you said you were familiar with simple macros... I'm confused a little bit.

    Anyway, this macro below does more or less what you described in post #1.
    I choose a different algorithm than you did, because the code is more effective (i.e. simpler and faster) this way.
    Column "D" on the sheet called "New data" is scanned for existing worksheet names. If any is found, the entire row is copied to the next empty line of the corresponding sheet. Findings are then colored to yellow, so that you can see at the end, which rows were not copied to anywhere. Try it and tell me if it's okay.
    Code:
    Sub test()
        Dim wsNew As Worksheet, wsActual As Worksheet
        Dim SearchString As String, FirstAddress As String
        Dim c As Range, Target As Range
        
        Set wsNew = ThisWorkbook.Worksheets("New data")
        For Each wsActual In ThisWorkbook.Worksheets
            If wsActual.Name <> wsNew.Name Then
                SearchString = wsActual.Name & "*"
                With wsNew.Range("D:D")
                    Set c = .Find(what:=SearchString, LookIn:=xlValues, lookat:=xlWhole)
                    If Not c Is Nothing Then
                        FirstAddress = c.Address
                        Do
                            Set Target = wsActual.Range("A" & Rows.Count).End(xlUp).Offset(1)
                            c.EntireRow.Copy Destination:=Target
                            c.EntireRow.Interior.ColorIndex = 6
                            Set c = .FindNext(c)
                        Loop While Not c Is Nothing And c.Address <> FirstAddress
                    End If
                End With
            End If
        Next
    End Sub
    Jimmy
     
  5. bobwitte

    bobwitte Thread Starter

    Joined:
    Jun 2, 2011
    Messages:
    7
    Jimmy, if that macro can be run manually, then we can blow off the question of what 'event' to tie it to. This is the much-preferred situation. My reading elsewhere lead me to believe it couldn't be done, but your code looks like it would do it.


    Thank you for the code sample. It looks very much like what I'm looking for.

    But it also looks so foreign that I don't think I can tweak it the little bit that it still needs.

    I need to put "Check # 1234" and "Check # 2345" on the same worksheet. So I need the equivalent of c=Left$(6,c) where the worksheet names will be 6-character names. But what if the 6th character is a blank as is the case for "Check #..."? Will a string ending in blank equate to a sheet name of only 5 digits?

    And if I'm not wearing out my welcome, I'd rather MOVE/cut the lines and paste them on the target sheet so that New Data ends up empty. And I'd like to insert them at the top of the sheet [say, line 3] leaving space for headings.

    Total mismatches would all go to the sheet labelled "OTHER". As a fallback, I could move the 'others' to that sheet manually after the macro runs if they are the only lines left on New Data.

    Again thank you for your time on this question.

    Robert
     
  6. bobwitte

    bobwitte Thread Starter

    Joined:
    Jun 2, 2011
    Messages:
    7
    How can I insert code and keep the indentation? All the spaces I put int eh pseudo code in post #1 got lost so it is very difficult to read.
     
  7. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Robert,

    indentation of the posted code can be achieved by the CODE tags. Select your code in the post editor window, then click on the # button above the window.
    I've modified the code to move and insert the hits to their corresponding sheets, and also move total mismatches to sheet "OTHER".
    Code:
    Sub test()
        Dim wsNew As Worksheet, wsActual As Worksheet
        Dim SearchString As String, FirstAddress As String
        Dim c As Range, Target As Range
        
        Set wsNew = ThisWorkbook.Worksheets("New data")
        For Each wsActual In ThisWorkbook.Worksheets
            If wsActual.Name <> wsNew.Name Then
                SearchString = wsActual.Name & "*"
                With wsNew.Range("D:D")
                    Set c = .Find(what:=SearchString, LookIn:=xlValues, lookat:=xlWhole)
                    If Not c Is Nothing Then
                        Do
                            wsActual.Range("3:3").Insert
                            Set Target = wsActual.Range("3:3")
                            c.EntireRow.Cut Destination:=Target
                            Set c = .FindNext(c)
                        Loop Until c Is Nothing
                    End If
                End With
            End If
        Next
        wsNew.Range("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        wsNew.Range("D1").CurrentRegion.EntireRow.Cut
        ThisWorkbook.Worksheets("OTHER").Range("3:3").Insert shift:=xlDown
    End Sub 
    I'll give you a bit of explanation on how the code works.

    All lines beginning with Dim are variable declarations. I need worksheet-type, string-type and range-type variables, so I declared those. Variable declaration is not obligatory, but very much recommended. It can save you from a lot of trouble.
    Code:
    Set wsNew = ThisWorkbook.Worksheets("New data")
    this is assigning the worksheet called "New data" to the worksheet-type-variable called wsNew. I needed to refer to sheet "New data" several times throughout the code. It could have been done by the expression ThisWorkbook.Worksheets("New data"), but putting that sheet into a variable, and using that variable instead was much more convenient.
    Code:
    For Each wsActual In ThisWorkbook.Worksheets
    Next
    This is a loop, looping through all worksheets in the workbook. wsActual is the loop variable, and always it contains the current worksheet. In the loop, sheet "New data" is also having its turn. With the code lines
    Code:
    If wsActual.Name <> wsNew.Name Then
    ...
    End if
    sheet "New data" is excluded from further processing.
    And now comes the essence.
    A SearchString is made out of the worksheet names. If sheet name is, for example, "ABC" then the search string will be "ABC*", i.e. the sheet name plus a wildcard. Then column D of sheet "New data" is scanned for the search string. It will find everything that starts with "ABC". So it doesn't matter if the serach string (which is the worksheet name, actually) is 5 or 6, or 4 or 35 characters long.
    It's also important that the Find operation is done with lookat:=xlWhole switch, that is, search is aimed at full cell values. Otherwise, searching for ABC* would find ffgdABCdesrgh as well.

    So, during the full loop:
    Anything that starts with ABC goes to the ABC sheet.
    Anything that starts with EFG goes to the EFG sheet.
    etc., the same operation for all existing sheets, excluding "New data", but including OTHER, so anything that starts with OTHER goes to the OTHER sheet.

    And, finally, everything that remains, also goes to the OTHER sheet. This operation, however, is outside the loop.

    I didn't fully understood that "Check # 1234" and "Check # 2345" part in your post, but I think it is answered above.

    Jimmy
     
  8. bobwitte

    bobwitte Thread Starter

    Joined:
    Jun 2, 2011
    Messages:
    7
    Jimmy, Thanks for the tutorial. That star/wild card takes care of all concerns about what might be on a line after the first 5-6 characters. Much more elegant than Left$(6,x)!!

    I'll try this when I get back home [currently touring the North-West via 5th wheel].

    Where can I find books or tutorials that teach this stuff?

    Thank you again.
    Robert
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1000265