Select Case (click_event)?

[kayakkid]

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.

 

[acw]

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]

Thanks

Thanks Tony that works perfectly.

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

ray: