Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need More Elegant Solution - Simple Program
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 29
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 29
Default 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.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Elegant solution for two comparisons Joe Murphy[_2_] Excel Discussion (Misc queries) 9 January 25th 08 10:42 PM
Is there an elegant solution to this table? [email protected] Excel Discussion (Misc queries) 3 July 9th 06 01:31 PM
Working Hours (formula & graph) - any elegant solution? markx Excel Worksheet Functions 1 March 29th 06 02:02 PM
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution... Bonnie Excel Discussion (Misc queries) 5 January 20th 06 09:53 PM
elegant programmed solution to local minima with constraints Bill[_32_] Excel Programming 0 July 28th 05 10:04 PM


All times are GMT +1. The time now is 05:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement