Dropping a line

[Dave Punky]

Hi all,

I'm trying to create a note generator so I actually have to do no work while I'm at work but I've reached a brick wall and not sure how to go ahead.

Basically I have a range (B5:B40) where my notes are going to be placed and I'm just going to have buttons with macros where I click and it puts the notes into that range. The problem I've got is I want it to check for previous content and if so, drop down to the next line. So basically if there's already stuff in B5, it puts it in B6.

Everything is based on the same sheet as well - what's the best way of doing this?

 

[repairman615]

Hello,

Alt + F11 ... to open the VBEditor
Keystrokes Alt - I - M ...insetr module

Paste in:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Drop()<br><br><SPAN style="color:#00007F">If</SPAN> Range("B5") = "" <SPAN style="color:#00007F">Then</SPAN><br>    Range("B5").Select<br><SPAN style="color:#00007F">ElseIf</SPAN> Range("B5").Offset(1, 0) = "" <SPAN style="color:#00007F">Then</SPAN><br>    Range("B5").Offset(1, 0).Select<br><SPAN style="color:#00007F">Else</SPAN><br>    Range("B5").End(xlDown).Offset(1, 0).Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>End <SPAN style="color:#00007F">Sub</SPAN></FONT>

Then you may attach this macro "Drop" to a button, this should select the next avail. cell...this is the line that mainly does it:
<font face=Courier New>    Range("B5").End(xlDown).Offset(1, 0).Select</FONT>
the rest handles the error if only 1 or two records exist like just B5 or B5 and B6. This does select the first blank going down so if you have gaps they will be selected. Search for xlUp to come from the bottom up.

 

[Dave Punky]

Hi repairman615,

Thank you for that post although I have a question - if I already have my value (so say I want to copy B51) and then I want to auto paste that in, how do I do that?

 

[repairman615]

Hi Dave Punky,

Here are two examples, see if the below is something you can work with. There could be more wrinkles to iron out to suit your needs. I was not sure if you always wanted cell B54 or if that would change.

Assign these to a button.

The Drop always uses cell B54
DropFromSelection will use whatever cell is selected

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Drop()<br><br>    <SPAN style="color:#007F00">'''The method below is "hard-coded" to always use cell B54 on the ActiveSheet to copy from.</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Range("B5") = "" <SPAN style="color:#00007F">Then</SPAN><br>        Range("B5").Value = Range("B54").Value<br>    <SPAN style="color:#00007F">ElseIf</SPAN> Range("B5").Offset(1, 0) = "" <SPAN style="color:#00007F">Then</SPAN><br>        Range("B5").Offset(1, 0).Value = Range("B54").Value<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Range("B5").End(xlDown).Offset(1, 0).Value = Range("B54").Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> DropFromSelection()<br><br>    <SPAN style="color:#007F00">'''The method below uses the ActiveCell to copy from.</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Range("B5") = "" <SPAN style="color:#00007F">Then</SPAN><br>        Range("B5").Value = ActiveCell.Value<br>    <SPAN style="color:#00007F">ElseIf</SPAN> Range("B5").Offset(1, 0) = "" <SPAN style="color:#00007F">Then</SPAN><br>        Range("B5").Offset(1, 0).Value = ActiveCell.Value<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Range("B5").End(xlDown).Offset(1, 0).Value = ActiveCell.Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>