variable sub call

Posted by Duane Kennerson on December 11, 2001 1:07 PM

Is there a way to put a variable in a sub call. For ex:
x = range("a1") 'this makes x = 5

I have a sub called "do5"

then I have another sub that uses that variable to determine what sub to run. It might look something like:

If blah blah blah then
do & x
end if

and it would run sub do5()

Is this possible???

Thanks in advance,
Duane

Posted by Rick E on December 11, 2001 1:37 PM

Use Select Case statement

Here is an example select case statement:

Sub tester()

' code to assign x

Select Case x
Case 1
do1
Case 3
do3
Case 5
do5
ElseCase
MsgBox "x not a sub"
End Select

End Sub

Hope that helps, Rick E

Posted by Duane Kennerson on December 11, 2001 1:43 PM

Re: Use Select Case statement

Sort of, that would definatley save me from a huge if then statement. The problem is I have 200 "do" statements. I was trying to use a variable to avoid writing a huge procedure (I guess I'm just lazy). I'm going to use your case statement but if anybody knows about using variables in the sub call, I would like to know.
Thanks Rick!
Duane

Posted by Bariloche on December 11, 2001 6:21 PM

200 Do statements?

Duane,

I have to ask this. Why do you have 200 "Do" statements? Just curious. Describe your program, maybe there's a better solution.

thanks

Posted by Bruno on December 11, 2001 11:23 PM

Re: 200 Do statements? Duane, there must be an easier way

Duane, there must be a way to avoid a serie of 200 (!) case statements...
Paste some code will you ?

Posted by Duane Kennerson on December 12, 2001 2:38 PM

You asked for it.....

You asked for it...

Sub getthelocation()
'find the location on the database according to the id number

Dim x As Integer

Sheets("Filter").Select
x = Range("as2")
Sheets("Database").Select
Select Case x
Case 1
do1
Case 2
do2

End Select

End Sub
Sub do1()

Dim y As Integer
Dim yCount As Integer

y = Range("l3")

Sheets("Filter").Select
Range("ax2:ay10").Select
Selection.Copy
Sheets("Database").Select
Range("g" & y).Select
ActiveSheet.Paste
Sheets("Filter").Select
Range("bg2:bh10").Select
Selection.Copy
Sheets("Database").Select
Range("j" & y).Select
ActiveSheet.Paste
Sheets("Filter").Select
Range("bc2:bc10").Select
Selection.Copy
Sheets("Database").Select
Range("i" & y).Select
ActiveSheet.Paste
Sheets("Filter").Select
Range("at2:at10").Select
Selection.Copy
Sheets("Database").Select
Range("f" & y).Select
ActiveSheet.Paste

End Sub
Sub do2()

Dim y As Integer
Dim yCount As Integer

y = Range("l55")

End Sub

Okie dokie, so here is what is going on. I obviously only included 2 of the "Do" procedures. Here is a brief (ha ha) explanation of what is going on.

Sub getthelocation : this looks at range as2 on a sheet named filter to get a number to assign to x. It then looks at a sheet called database and based on the value of x, it runs the correct procedure or "Do" procedure. Simple enough? "x" is a number assigned to a certain employee. There are 200 employees so x could = 1 to 200. The filter sheet contains information on "x" employee and now its going to move that information to the database sheet which is collecting all the information on all 200 employees. Each employee has a designated area on the database sheet for their information.

So once excel goes to the database sheet, it runs the correct "Do" procedure based on the value of x. The only difference between all of the "Do" statements is where is starts. In "Do1" it starts at range("L3"). Thats because the employee assined the "x" variable has his/her information stored in this location on the database sheet.

Its really just a simple complex (huh?) copy and paste procedure. It just needs to make sure the information gets pasted in the right place.

Did I confuse you more???

Your thoughts on what I'm doing wrong would be greatly appreciated. There probably is a much simpilar way to do this, I just don't know how.

Thanks in advance,
Duane

Posted by Bariloche on December 12, 2001 7:40 PM

Yes we did :-)

Duane,

I'll try to think about, but no guarantees.

At the risk of seeming to add complexity to your life, have you considered using Access for this task instead? I realize that that means learning new, and rather foreign, concepts (not being funny here, the concepts behind databases can be difficult to grasp at first). The reason I mention it (again) is because the kind of thing that you're doing here is what database applications are designed to do. So a lot of the rigamarole that you need to go through with Excel to make it emulate a database app is already part of a database app like Access.

Just something to consider. I'll try to think on what you're doing with the macro, but like I said, no guarantees. (If you don't hear back from me or anyone else, just repost at the top of the page.)

take care

You asked for it... Sub getthelocation() 'find the location on the database according to the id number Dim x As Integer Sheets("Filter").Select x = Range("as2") Sheets("Database").Select Select Case x Case 1 do1 Case 2 do2 End Select End Sub Sub do1() Dim y As Integer Dim yCount As Integer y = Range("l3") Sheets("Filter").Select Range("ax2:ay10").Select Selection.Copy Sheets("Database").Select Range("g" & y).Select ActiveSheet.Paste Sheets("Filter").Select Range("bg2:bh10").Select Selection.Copy Sheets("Database").Select Range("j" & y).Select ActiveSheet.Paste Sheets("Filter").Select Range("bc2:bc10").Select Selection.Copy Sheets("Database").Select Range("i" & y).Select ActiveSheet.Paste Sheets("Filter").Select Range("at2:at10").Select Selection.Copy Sheets("Database").Select Range("f" & y).Select ActiveSheet.Paste End Sub Sub do2() Dim y As Integer Dim yCount As Integer y = Range("l55") Sheets("Filter").Select Range("ax2:ay10").Select Selection.Copy Sheets("Database").Select Range("g" & y).Select ActiveSheet.Paste Sheets("Filter").Select Range("bg2:bh10").Select Selection.Copy Sheets("Database").Select Range("j" & y).Select ActiveSheet.Paste Sheets("Filter").Select Range("bc2:bc10").Select Selection.Copy Sheets("Database").Select Range("i" & y).Select ActiveSheet.Paste Sheets("Filter").Select Range("at2:at10").Select Selection.Copy Sheets("Database").Select Range("f" & y).Select ActiveSheet.Paste End Sub Okie dokie, so here is what is going on. I obviously only included 2 of the "Do" procedures. Here is a brief (ha ha) explanation of what is going on. Sub getthelocation : this looks at range as2 on a sheet named filter to get a number to assign to x. It then looks at a sheet called database and based on the value of x, it runs the correct procedure or "Do" procedure. Simple enough? "x" is a number assigned to a certain employee. There are 200 employees so x could = 1 to 200. The filter sheet contains information on "x" employee and now its going to move that information to the database sheet which is collecting all the information on all 200 employees. Each employee has a designated area on the database sheet for their information. So once excel goes to the database sheet, it runs the correct "Do" procedure based on the value of x. The only difference between all of the "Do" statements is where is starts. In "Do1" it starts at range("L3"). Thats because the employee assined the "x" variable has his/her information stored in this location on the database sheet. Its really just a simple complex (huh?) copy and paste procedure. It just needs to make sure the information gets pasted in the right place. Did I confuse you more??? Your thoughts on what I'm doing wrong would be greatly appreciated. There probably is a much simpilar way to do this, I just don't know how. Thanks in advance,

Posted by Bariloche on December 13, 2001 6:13 AM

Duane, try this

Print out this code and the snippet that you posted and red-line your snippet to get to what I have here. That will help you see what I've done.

A few comments on your snippet first:

1) You have "y" dimensioned as Integer but then try to assign it a String value ("L3"), this should have caused your code to not perform as intended.

2) You should use uppercase letters for columns; its easier to read, IMO.

3) Looks like you want to paste some data into cell "JL3" in Do1; that cell doesn't exist. In my code I changed "J" to "H" to get it to work.

Now the code:

Sub GetTheLocation()
'find the location on the database according to the id number

Dim x As Integer
Dim y as String

'Application.Screenupdating = False 'Uncomment this to stop re-painting of the screen during execution

x = Sheets("Filter").Range("AS2").Value

If x = 1 Then y = "L3" Else y = "L" & (x * 10)

Sheets("Database").Select
Sheets("Filter").Range("AX2:AY10").Copy
Sheets("Database").Range("G" & y).Select
ActiveSheet.Paste
Sheets("Filter").Range("BG2:BH10").Copy
Sheets("Database").Range("H" & y).Select
ActiveSheet.Paste
Sheets("Filter").Range("BC2:BC10").Copy
Sheets("Database").Range("I" & y).Select
ActiveSheet.Paste
Sheets("Filter").Range("AT2:AT10").Copy
Sheets("Database").Range("F" & y).Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Application.Screenupdating = True 'Uncomment this to re-paint the screen after execution

End Sub

Not sure if you can implement this, but I think you need something at least similar.

I'd sugest that you set up a dummy workbook, name the sheets "Filter" and "Database", on the Filter sheet color the areas of data that you are copying (AX2:AY10, etc) different colors, put a value in AS2 (I'd start with 1, :-) ) and then step through my code so you can see how it executes.

Some variation on this is probably what you need to do. Don't know what latitude you have to change the format of the "Database" sheet, but that might be necessary.

Hopefully someone else will post a solution so you can have a choice.

good luck

ps: I'd also recommend John Walkenbach's book on programming Excel2000, if you don't already have it.

Posted by Duane Kennerson on December 13, 2001 1:34 PM

Re: Duane, try this

Lets say the 12 column in my work sheet is "L" the fomula is as follows: =VLOOKUP(x99,ApprovalData,12,FALSE) Lets say I name the "L" column "Widget_range", What would the formula look like. Is it possible?

=VLOOKUP(x99,ApprovalData,Column(Widget_range),FALSE)

Posted by Duane Kennerson on December 13, 2001 2:18 PM

Re: More thoughts

A few comments on your snippet first: 1) You have "y" dimensioned as Integer but then try to assign it a String value ("L3"), this should have caused your code to not perform as intended. 2) You should use uppercase letters for columns; its easier to read, IMO. 3) Looks like you want to paste some data into cell "JL3" in Do1; that cell doesn't exist. In my code I changed "J" to "H" to get it to work. Now the code: Sub GetTheLocation() 'find the location on the database according to the id number Dim x As Integer Dim y as String 'Application.Screenupdating = False 'Uncomment this to stop re-painting of the screen during execution x = Sheets("Filter").Range("AS2").Value If x = 1 Then y = "L3" Else y = "L" & (x * 10) Sheets("Database").Select Sheets("Filter").Range("AX2:AY10").Copy Sheets("Database").Range("G" & y).Select ActiveSheet.Paste Sheets("Filter").Range("BG2:BH10").Copy Sheets("Database").Range("H" & y).Select ActiveSheet.Paste Sheets("Filter").Range("BC2:BC10").Copy Sheets("Database").Range("I" & y).Select ActiveSheet.Paste Sheets("Filter").Range("AT2:AT10").Copy Sheets("Database").Range("F" & y).Select ActiveSheet.Paste Application.CutCopyMode = False 'Application.Screenupdating = True 'Uncomment this to re-paint the screen after execution End Sub Not sure if you can implement this, but I think you need something at least similar. I'd sugest that you set up a dummy workbook, name the sheets "Filter" and "Database", on the Filter sheet color the areas of data that you are copying (AX2:AY10, etc) different colors, put a value in AS2 (I'd start with 1, :-) ) and then step through my code so you can see how it executes. Some variation on this is probably what you need to do. Don't know what latitude you have to change the format of the "Database" sheet, but that might be necessary. Hopefully someone else will post a solution so you can have a choice. good luck

ps: I'd also recommend John Walkenbach's book on programming Excel2000, if you don't already have it.

I tried the code in another book. I think I see where you are going.
Correct me if I'm wrong, I'm assigning "y" as an integer in "Do1" because the value in range "L3" on the Database sheet is a number. Am I wrong with that? I'm not sure. What'happening is that in each employees container (area where there information is stored) on the Database sheet has information from prior days of work. When I cut and paste from the filter sheet,it goes to the employees container and then it looks at the value in "Y" and goes to that row number in the container so that is does not paste the information over the infomation that is already in there. Does this make sense. Is there a better way?
I have a lot of flexiblity on my database sheet. I have currently set it up to run vertically instead on horizontally (which is what I think your code was doing). If I did it horizontally, I should have enough room for all 200 employees. In fact, now that I think about it, I would probably prefer to set it up that way.

Any thoughts? And again, thanks for your help, I really like the way the code is set up, it seems more compact than what I was writing. I'm such and ametuer!

Thanks,
Duane

Posted by Duane Kennerson on December 13, 2001 4:23 PM

Problem Solved, thanks Bariloche

Sub getthelocation()
'find the location on the database according to the id number

Dim x As Integer
Dim y As Integer
Dim yCount As Integer

Sheets("Filter").Select
x = Range("as2")
Sheets("Database").Select

If x = 1 Then y = 3 Else y = (x * 52) - 49
y = Range("l" & y)

End Sub

Thanks to your inspiration, I was able to figure out how to make it work without all of those do procedures. The little piece of code you wrote inspired me to think differently on how to approach this. Thanks for your help, it really paid off.

Duane

Posted by Bariloche on December 13, 2001 6:34 PM

You're welcome Duane

Glad to be of assistance. You'll get the hang of it eventually. Writing code does require that you think about problems differently. Remember, you're trying to get a piece of sand to do some amazing things -- it needs all the help it can get. :-))

take care