Solved: Sub arguments (simplifying code)

[nomaxtech]

Hi everyone,

I've been working on this for days and must have visited a hundred websites with no luck. I've seen examples that might help but they weren't explained enough to understand. Usually the code was accompanied by "Just paste this in there."

Here's my deal:

I want to have a master price list in a seperate file editable by only me. I'm going to ditrubte Purchase orders, Service Records, and Job Costing sheets around the office so people can just click a button on the sheet and retrieve the most up-to-date product list and pricing from my master file. I have that solved so far.

Now, some of these people my want only alarm equipment, some of them may want only video equipment, and some of them may want both of those and some others. In my master sheet I have a column solely dedicated to the category of the equipment for this purpose. If they check Alarm and click the button, then only get the alarm list. If they check CCTV, they only get the video stuff. Etc...

I'll use a job costing sheet, for example. I have a Sub that runs as many times as there are checked checkboxes. Because it's the same code that run over and over again (with just the category changed), I tried to put it into it's own Sub and then call it as many times as I need it purely for simplifying the code.

Here's my example: (it's all indented and neatly organized in Excel)


Sub Retrieve_Equipment()
'**********
'Retrieve Equipment List from Master Cost Sheet
'**********

'**********
'Determine if specified box is Checked, continue routine if it is
'**********
If (Range("Q6") = "True") Then

Copy_and_Paste

End If

End Sub

'----------------------------------------------------------------

Sub Copy_and_Paste()
Set JC = Sheets("Job Costing")
Set TC = Sheets("True Cost")
FR = 2
LR = LastRow()

'**********
'Starting at row 2, perform routine until cells in column A are empty
'**********
Do Until IsEmpty(TC.Range("A" & FR))

'**********
'Perform routine for any rows whose value is equal to Cat in colum A of specified sheet
'**********
If TC.Range(("A" & FR) = "Alarm") Then

'**********
'Start operation row after the last row with data (first empty row)
'**********
LR = LR + 1

'**********
'Call cell format routine
'**********
Merge_and_Format

'**********
'Copy rows and specified columns to first empty row on destination sheet
' from current row on source sheet then add formula "=A(current row)*J(current row)"
'**********
JC.Rows(LR).Columns("C").Value = TC.Rows(FR).Columns("C").Value
JC.Rows(LR).Columns("E").Value = TC.Rows(FR).Columns("D").Value
JC.Rows(LR).Columns("J").Value = TC.Rows(FR).Columns("E").Value
JC.Rows(LR).Columns("L").Formula = "=A" & LR & "*J" & LR

End If

'**********
'Go to next row on source sheet
'**********
FR = FR + 1

Loop

End Sub



So....if you can follow it, the Copy_and_Paste Sub is looking for everything in the Alarm category right now. I'd have to write the same Sub over and over looking at different categories this way. I'd like to be able to change what it's looking for whenever it's called from inside the Retrieve_Equip Sub. I can't figure out those darn arguments though...(or even if that's how to get this done) Here's what I was to be able to do:


Sub Retrieve_Equipment()
'**********
'Retrieve Equipment List from Master Cost Sheet
'**********

'**********
'Determine if specified box is Checked, continue routine if it is
'**********
If (Range("Q6") = "True") Then

Copy_and_Paste ("Alarm")

End If

If (Range("Q7") = "True") Then

Copy_and_Paste ("CCTV")

End If

If (Range("Q8") = "True") Then

Copy_and_Paste ("Wire")

End If

End Sub

'----------------------------------------------------------------

Sub Copy_and_Paste(**VARIABLE**)
Set JC = Sheets("Job Costing")
Set TC = Sheets("True Cost")
FR = 2
LR = LastRow()

'**********
'Starting at row 2, perform routine until cells in column A are empty
'**********
Do Until IsEmpty(TC.Range("A" & FR))

'**********
'Perform routine for any rows whose value is equal to Cat in colum A of specified sheet
'**********
If TC.Range(("A" & FR) = "**VARIABLE**") Then

'**********
'Start operation row after the last row with data (first empty row)
'**********
LR = LR + 1

'**********
'Call cell format routine
'**********
Merge_and_Format

'**********
'Copy rows and specified columns to first empty row on destination sheet
' from current row on source sheet then add formula "=A(current row)*J(current row)"
'**********
JC.Rows(LR).Columns("C").Value = TC.Rows(FR).Columns("C").Value
JC.Rows(LR).Columns("E").Value = TC.Rows(FR).Columns("D").Value
JC.Rows(LR).Columns("J").Value = TC.Rows(FR).Columns("E").Value
JC.Rows(LR).Columns("L").Formula = "=A" & LR & "*J" & LR

End If

'**********
'Go to next row on source sheet
'**********
FR = FR + 1

Loop

End Sub



Any ideas? Thanks in advance!

[Bob Phillips]

Sub Retrieve_Equipment()
'**********
'Retrieve Equipment List from Master Cost Sheet
'**********

'**********
'Determine if specified box is Checked, continue routine if it is
'**********

Select Case True

Range("Q6").Value: Call Copy_and_Paste ("Alarm")

Range("Q7").Value: Call Copy_and_Paste ("CCTV")

Range("Q8").Value: Call Copy_and_Paste ("Wire")

End Select

End Sub

'----------------------------------------------------------------

Sub Copy_and_Paste(ByVal equip As String)
Set JC = Sheets("Job Costing")
Set TC = Sheets("True Cost")
FR = 2
LR = Lastrow()

'**********
'Starting at row 2, perform routine until cells in column A are empty
'**********
Do Until IsEmpty(TC.Range("A" & FR))

'**********
'Perform routine for any rows whose value is equal to Cat in colum A of specified sheet
'**********
If TC.Range("A" & FR) = equip Then

'**********
'Start operation row after the last row with data (first empty row)
'**********
LR = LR + 1

'**********
'Call cell format routine
'**********
Merge_and_Format

'**********
'Copy rows and specified columns to first empty row on destination sheet
' from current row on source sheet then add formula "=A(current row)*J(current row)"
'**********
JC.Rows(LR).Columns("C").Value = TC.Rows(FR).Columns("C").Value
JC.Rows(LR).Columns("E").Value = TC.Rows(FR).Columns("D").Value
JC.Rows(LR).Columns("J").Value = TC.Rows(FR).Columns("E").Value
JC.Rows(LR).Columns("L").Formula = "=A" & LR & "*J" & LR

End If

'**********
'Go to next row on source sheet
'**********
FR = FR + 1
Loop

End Sub

[nomaxtech]

That does exactly what I need, thnk you! Thanks for refining the IF statements as well. However, with those in place, I get this error:

Compile Error:

Statements and labels are not valid between select case and first case.

I've never used a Select Case so I don't even know where to begin. I've looked around on Google with no luck. Thanks again for clearing up the Sub arguments.

[nomaxtech]

So I found something online about Select Case.

Your code was


Select Case True

Range("Q6").Value: Call Copy_and_Paste ("Alarm")

Range("Q7").Value: Call Copy_and_Paste ("CCTV")

Range("Q8").Value: Call Copy_and_Paste ("Wire")

End Select



But it looks like you can check only one cell or range for one or any of multiple values. Like


Select Case Range("A1").Value

Case 100, 150, 200, 350, 400

Range("B1").Value = Range("A1").Value

Case Else

Range("B1").Value = 0

End Select


If I changed a few things to make it do what I need, like


Select Case Range("Q6:Q8").Value:

Case True

Call Copy_and_Paste ("Alarm")

Case True

Call Copy_and_Paste ("CCTV")

Case True

Call Copy_and_Paste ("Wire")

End Select


If the second example is the only way, how would it be able to differentiate my checkboxes? Say I only had CCTV checked, it would still function, but it wouldn't know which one to call, it would just call all of them. Am I close??

[nomaxtech]

Nevermind, I got it! I had to insert a couple of missing Cases.


Select Case True

Case Range("Q3").Value:

Call Copy_and_Paste("Burlgar Alarm")

Case Range("Q4").Value:

Call Copy_and_Paste("CCTV")

Case Range("Q5").Value:

Call Copy_and_Paste("Relays & Modules")

Case Range("Q3").Value:

Call Copy_and_Paste("Wire")

Case Range("Q4").Value:

Call Copy_and_Paste("Audio")

Case Range("Q5").Value:

Call Copy_and_Paste("Access Control")

End Select


Thank you so much!

[nomaxtech]

The first part of my question is solved, the arguments, but I just found a problem with the Select Case. It's not bad code or anything, it works, but I don't think I can use it. It appears that once a Case condition is met, it skips the rest. I need it to function for one OR multiple conditions. (a series of check boxes)

Example:

If Alarm AND Wire are checked, I would need it to Copy_and_Paste for both Alam and Wire.

Should I go back to IF statements?

[Bob Phillips]

You are correct. If you want all conditions to be checked you need to use If as you did originally.