Calling Sub difficulty

[Giselle]

Hello XLers

I'm quite new to Excel, and need some newbie help. Dave Peterson was very
helpful when I posed this a week ago, giving the code below. I arrived back
in town today, and I just can't get the second procedure to work.

Basically, my worksheet contains form buttons (actually 5). When clicked,
they will run Giselle that, in turn, calls Common.

MyQuestion: I keep getting error msgs at the Set lines. (Compile Error:
variable not defined. If I provide Dim statements, I still get Compile
Error: Object required.) I'm not even sure why I would need these lines???

Could someone show me how to get the 2nd procedure to work. (perhaps you
could even replace the " 'xxxxxxx common code goes here" line with a simple
task such as coloring the cells of R1 red and printing the text in R2 in
cell A1)

Dave's excellent code!

Option Explicit
Sub Giselle()
Dim R1 as String
Dim R2 as string

R1 = ""
R2 = ""

Select Case lcase(Application.Caller)
Case "button 1"
R1 = "D5:J25"
R2 = "Ford"
Case "button 2"
R1 = "J5:K25"
R2 = "Chrysler"
End Select

If R1 = "" then
beep
msgbox "Design error. See Giselle!
exit sub
End if

call Common(R1,R2)
End Sub


Sub Common(R1 as String, R2 as String)

with worksheets("Sheet99") 'or Activesheet ????
Set RangeToWorkOn = .Range(R1)
Set StringToWorkOn = .Range(R2)
end with

'xxxxxxxx common code goes in here

End Sub



THANKYOU very much for any assistance
Giselle

 

[Rick Hansen]

Hi Giselle,
You need to declare two Range variable to to Set them. Also you each
Range variable must have it' own individual name. I 've made changes to your
Subroutine Commom() (See code below)
Good Luck...

Rick


Sub Common(R1 as String, R2 as String)
Dim RangeToWorkOn_1 as Range
Dim RangeToWorkOn_2 as Range


with worksheets("Sheet99") 'or Activesheet ????
Set RangeToWorkOn_1 = .Range(R1)
Set StringToWorkOn_2 = .Range(R2)
end with

'xxxxxxxx common code goes in here

End Sub

 

[Bri]

hello again

I can't get the code to work. (keep getting error 1004 on the second Set
statement.) Also, as a newcomer to XL, I need just a bit more help on
syntax using these range variables, RangeToWorkOn_1 and
StringToWorkOn_2. ( Perhaps some coder out there could replace the "
'xxxxxxx common code goes here" line with a simple task such as coloring the
cells of R1 red and printing the text in R2 in cell A1.) I'd ge really
greatful for help with this syntax.


Option Explicit
Sub Giselle()
Dim R1 As String
Dim R2 As String

R1 = ""
R2 = ""

Select Case LCase(Application.Caller)
Case "button 1"
R1 = "D5:J25"
R2 = "Ford"
Case "button 2"
R1 = "J5:K25"
R2 = "Chrysler"
End Select

If R1 = "" Then
Beep
MsgBox "Design error. See Giselle!"
Exit Sub
End If

Call Common(R1, R2)
End Sub


Sub Common(R1 As String, R2 As String)

Dim RangeToWorkOn_1 As Range
Dim StringToWorkOn_2 As Range

With Worksheets("Sheet1") 'or Activesheet ????
Set RangeToWorkOn_1 = .Range(R1)
Set StringToWorkOn_2 = .Range(R2)
End With

'xxxxxxxx common code goes in here

End Sub

 

[JMB]

Set StringToWorkOn_2 = .Range(R2)

It looks like R2 will equal either "Ford" or "Chrysler". Are these named
ranges or are these values you are looking for? If they are not named ranges
on Sheet1, but rather values you are looking for, change StringToWorkOn_2 to
a string variable

Dim StringToWorkOn_2 As String

and change the Set statement

StringToWorkOn_2 = R2

 

[Dave Peterson]

There was a typo in that "excellent" code.

From the original thread:

Option Explicit
Sub Giselle()
Dim R1 as String
dim R2 as string
dim R3 as string

R1 = ""
R2 = ""
R3 = ""

Select Case lcase(Application.Caller)
Case "button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O"
Case "button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "U:V" '<--changed
End Select

if r1 = "" then
beep
msgbox "Design error. See Giselle!
exit sub
end if

call Common(r1,r2,r3)

End Sub

Sub Common(R1 as String, R2 as String,R3 as String)

Dim FirstRangeToWorkOn As Range
Dim StringToWorkOn as string
dim SecondRangeToWorkOn as range

with worksheets("Sheet99") 'or Activesheet ????
Set FirstRangeToWorkOn = .Range(R1)
StringToWorkOn = R2
Set SecondRangeToWorkOn = .Range(R3)
end with
.....

End Sub

I'm not sure if this adds anything to the corrections you've already gotten, but
it makes me feel better that it's fixed. <vbg>.

 

[Giselle]

All's well now. Thank you
Giselle