Need More Elegant Solution - Simple Program
 

Being a very novice programmer I didn't know how to combine the
functions of all these command buttons (examples of two are below, they
are all identical except for the value they are looking for and the
range they copy to). I have about 55 command buttons which are all
executed by one button, very inefficient. Thanks for help in advance!


Private Sub CommandButton54_Click()
Dim r As Range
Set r = Cells.Find(What:="102", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B6")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Private Sub CommandButton55_Click()
Dim r As Range
Set r = Cells.Find(What:="103", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B7")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Need More Elegant Solution - Simple Program
 

Make a generic procedure called by each command button. The procedure will
take a number of arguments to define the ranges and such...

Private Sub CommandButton54_Click()
Call CopyStuff("102", Range("A129"), Range("B6")
End Sub

Private Sub CommandButton55_Click()
Call CopyStuff("103", Range("A129"), Range("B7")
End Sub

Sub CopyStuff(dim strWhat as string, rngAfter as Range, rngDestination as
Range)
Dim r As Range
Set r = Cells.Find(What:=strWhat , After:=rngAfter ,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If rngAfter.Value = "" Then
r.Offset(1).Resize(1, 4).Copy rngDestination
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub
--
HTH...

Jim Thomlinson


" wrote:

Being a very novice programmer I didn't know how to combine the
functions of all these command buttons (examples of two are below, they
are all identical except for the value they are looking for and the
range they copy to). I have about 55 command buttons which are all
executed by one button, very inefficient. Thanks for help in advance!


Private Sub CommandButton54_Click()
Dim r As Range
Set r = Cells.Find(What:="102", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B6")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Private Sub CommandButton55_Click()
Dim r As Range
Set r = Cells.Find(What:="103", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B7")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Need More Elegant Solution - Simple Program
 

Sorry...Missing some brackets in the command button events...

Private Sub CommandButton54_Click()
Call CopyStuff("102", Range("A129"), Range("B6"))
End Sub

Private Sub CommandButton55_Click()
Call CopyStuff("103", Range("A129"), Range("B7"))
End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Make a generic procedure called by each command button. The procedure will
take a number of arguments to define the ranges and such...

Private Sub CommandButton54_Click()
Call CopyStuff("102", Range("A129"), Range("B6")
End Sub

Private Sub CommandButton55_Click()
Call CopyStuff("103", Range("A129"), Range("B7")
End Sub

Sub CopyStuff(dim strWhat as string, rngAfter as Range, rngDestination as
Range)
Dim r As Range
Set r = Cells.Find(What:=strWhat , After:=rngAfter ,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If rngAfter.Value = "" Then
r.Offset(1).Resize(1, 4).Copy rngDestination
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub
--
HTH...

Jim Thomlinson


" wrote:

Being a very novice programmer I didn't know how to combine the
functions of all these command buttons (examples of two are below, they
are all identical except for the value they are looking for and the
range they copy to). I have about 55 command buttons which are all
executed by one button, very inefficient. Thanks for help in advance!


Private Sub CommandButton54_Click()
Dim r As Range
Set r = Cells.Find(What:="102", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B6")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Private Sub CommandButton55_Click()
Dim r As Range
Set r = Cells.Find(What:="103", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B7")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Need More Elegant Solution - Simple Program
 

Why not create an array of params and then have the method itterate through
the array?

"Jim Thomlinson" wrote:

Sorry...Missing some brackets in the command button events...

Private Sub CommandButton54_Click()
Call CopyStuff("102", Range("A129"), Range("B6"))
End Sub

Private Sub CommandButton55_Click()
Call CopyStuff("103", Range("A129"), Range("B7"))
End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Make a generic procedure called by each command button. The procedure will
take a number of arguments to define the ranges and such...

Private Sub CommandButton54_Click()
Call CopyStuff("102", Range("A129"), Range("B6")
End Sub

Private Sub CommandButton55_Click()
Call CopyStuff("103", Range("A129"), Range("B7")
End Sub

Sub CopyStuff(dim strWhat as string, rngAfter as Range, rngDestination as
Range)
Dim r As Range
Set r = Cells.Find(What:=strWhat , After:=rngAfter ,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If rngAfter.Value = "" Then
r.Offset(1).Resize(1, 4).Copy rngDestination
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub
--
HTH...

Jim Thomlinson


" wrote:

Being a very novice programmer I didn't know how to combine the
functions of all these command buttons (examples of two are below, they
are all identical except for the value they are looking for and the
range they copy to). I have about 55 command buttons which are all
executed by one button, very inefficient. Thanks for help in advance!


Private Sub CommandButton54_Click()
Dim r As Range
Set r = Cells.Find(What:="102", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B6")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Private Sub CommandButton55_Click()
Dim r As Range
Set r = Cells.Find(What:="103", After:=Range("A129"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If Range("A129") = "" Then
r.Offset(1).Resize(1, 4).Copy Range("B7")
Else
r.Offset(1).Resize(1, 4).Copy Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Need More Elegant Solution - Simple Program
 

Jim F wrote:
Why not create an array of params and then have the method itterate through
the array?

I like both ideas, especially an array though I don't know how to
proceed. The objective is to reduce it to one command button instead
of 50 or so which seems very redundant and also uses a lot of system
resources.