Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

bobwitte's Avatar
bobwitte bobwitte is offline
Computer Specs
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2011
Experience: xl - hi; VB low
02-Jun-2011, 06:24 PM #1
Solved: equivalent of "if(true, myMacro..."
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 [S] 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.

Last edited by bobwitte; 02-Jun-2011 at 06:26 PM.. Reason: I indenteed the pseudo code. Sorry it didn't stick
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
04-Jun-2011, 01:30 AM #2
Hi

I want to clarify 2 points.
1) What do you mean by
Quote:
and the event I want to use is my running this macro
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
bobwitte's Avatar
bobwitte bobwitte is offline
Computer Specs
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2011
Experience: xl - hi; VB low
10-Jun-2011, 01:24 PM #3
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.
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
11-Jun-2011, 05:43 PM #4
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
bobwitte's Avatar
bobwitte bobwitte is offline
Computer Specs
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2011
Experience: xl - hi; VB low
11-Jun-2011, 10:40 PM #5
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
bobwitte's Avatar
bobwitte bobwitte is offline
Computer Specs
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2011
Experience: xl - hi; VB low
11-Jun-2011, 10:52 PM #6
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.
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
12-Jun-2011, 02:38 AM #7
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
bobwitte's Avatar
bobwitte bobwitte is offline
Computer Specs
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2011
Experience: xl - hi; VB low
12-Jun-2011, 10:03 PM #8
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
excel 03, macro

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2