Calling Sub difficulty

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

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




"Giselle" <[email protected]> wrote in message
news:%23d%[email protected]...
> 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
>
>
>

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



"Rick Hansen" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
>
>
> "Giselle" <[email protected]> wrote in message
> news:%23d%[email protected]...
>> 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
>>
>>
>>
>
>

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




"Bri" wrote:

> 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
>
>
>
> "Rick Hansen" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
> >
> >
> > "Giselle" <[email protected]> wrote in message
> > news:%23d%[email protected]...
> >> 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
> >>
> >>
> >>
> >
> >
>
>
>

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:P"
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 wrote:
>
> 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

--

Dave Peterson

All's well now. Thank you
Giselle