Select Case (click_event)?

kayakkid

New Member
Joined
Jan 3, 2005
Messages
26
Happy Hollidays!!

here is my code:
<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> findcolumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> prev <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> LC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Const</SPAN> R = 16
<SPAN style="color:#00007F">Set</SPAN> rng = Rows(R)

FC = 1
LC = Columns.Count
findcolumn = Int(Columns.Count / 2)

  <SPAN style="color:#00007F">Do</SPAN>
  prev = findcolumn
  <SPAN style="color:#00007F">If</SPAN> Cells(R, prev).Borders(xlEdgeTop).LineStyle <> xlContinuous <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'to much to the right</SPAN>
  LC = findcolumn
  findcolumn = Int((findcolumn + FC) / 2)
  <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">'to much to the left</SPAN>
  FC = findcolumn
  findcolumn = Int((LC + findcolumn) / 2)
  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
  <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> prev <> findcolumn
Cells(R, findcolumn + 1).Select
Range("ButtEnd").Copy ActiveCell
End <SPAN style="color:#00007F">Sub</SPAN></FONT>

This code was written by Eric Van Geit. (THANK YOU ERIC!!)

Basically what it does is finds the last cell with no border in row 16 (The Const R) and pastes the selected range there. My question is this:

My current project has about 20 different sections where this code will be used, each section having a different row (Const R). Each section will have a different button that will call the procedure. Is there a way to change the Const R to a given row depending on which button was clicked? For example, could I set it so that if ButtonA_Click event occurs that the Const R value is now set to 24?

My hope is that there is an easy way to get this done, and I can't seem to figure it out on my own. Any help would be appreciated.

Thank you all for your undying efforts to help the helpless.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

One way would be to give the code an argument which is filled by the calling code.

The code above doesn't have a name but assume that it is called Main(). Add an argument so it is and comment out the line
const r = 16

sub Main(R)
'const R = 16
...
end sub

now your buttonA_Click event would be along the lines of

sub ButtonA_Click()
call Main(24)
end sub


HTH

Tony
 

kayakkid

New Member
Joined
Jan 3, 2005
Messages
26
Thanks

Thanks Tony that works perfectly.

As payment, I give you 10,000 "Cool Guy" points.

:pray:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,092
Members
412,310
Latest member
mark884
Top