Be able to select next cell down every time button is clicked Excel VBA

Question

Below is the code that I have so far I am able to click the button and every time the button is clicked the cell selection is moved down the row by 1. What I need is to start the selection on F3 and select down until about F35 but when I range it doesn't select the cells one by one.

Here is my code:

Dim rng As Range
Dim row As Range
Dim cell As Range

    Set rng = Range("F2")
    rng.Select
    For Each row In rng.Rows
        For Each cell In row.Cells
        ActiveCell.Offset(1, 0).Select
        Next cell
        Range("G66") = ActiveCell
    Next row

You're trying to move the Active Cell? In other words, you run the macro with cell `F3` active, and when you run that, you simply want `F4` to be active? What are you doing with the active cell? It's best to avoid using [`.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Your loop doesn't really do anything, except select a cell. You can just skip it completely, since at the end you do `Range("G66") = ActiveCell`... — BruceWayne, Apr 06 '17 at 21:45
Well basically im looping through email address's so that every active cell it is on will show on cell G66. My goal is for this to click a button and click next with it moving down a cell within a range. — Kyle Marvin, Apr 06 '17 at 21:48
What's your ultimate goal? Just to show, briefly, an email address in `G66`? That should run in like 1 second, so all the email addresses are only visible for a *brief* second... — BruceWayne, Apr 06 '17 at 21:49
My ultimate goal is to show an email address when I click next in a certain cell (G66). — Kyle Marvin, Apr 06 '17 at 21:53
@YowE3K - [Ah ..well....](http://www.reactiongifs.com/r/lucille-portable.gif) hahaha — BruceWayne, Apr 06 '17 at 21:54
Where's the rest of the code? How's this being called? I just think this may be an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — BruceWayne, Apr 06 '17 at 21:54
Okay that is all of the code. Let me back up a bit. I have a list of people with email addresses in an excel file. My overall goal is to be able to open up an email window with the correct email address in the _From_ line and the correct subject and header also. For this I use a formula (=HYPERLINK("mailto:" & G66 & "?subject="& G63 & "&body=" & G64 & G65, "Linking text") So basically right now I have a button that selects each email address to G66 then I use my formula and go from there. Hope that helps.. sorry im pretty rusty to VBA. — Kyle Marvin, Apr 06 '17 at 22:00

score 1 · Accepted Answer · answered Apr 07 '17 at 06:32

1

if you have a Form button called Button1 then attach it a sub called Button1_Click() (or whatever, but be consistent with the name of the attached Sub) and place the following code in any module:

Option Explicit

Dim notFirst As Boolean
Dim rng As Range

Sub Button1_Click()
    If notFirst Then
        If rng.row = 35 Then
            MsgBox "Sorry: you've already reached last valid cell in column F"
            Exit Sub
        Else
            Set rng = rng.Offset(1)
        End If
    Else
        Set rng = Range("F3")
        notFirst = True
    End If

    Range("G66").Value = rng.Value
End Sub

if you have a ActiveX button called Button1 then write the same code as above in its sheet code pane

answered Apr 07 '17 at 06:32

user3598756

28,893
4
18
28

Thanks! It seems to kinda be working. Although after adding the code it only adds the first email address in G66. When i click the button again it doesnt go to the next email. it says notFirst is empty – Kyle Marvin Apr 07 '17 at 13:12
what email are you talking about? there's no email trace whatsoever in your question code – user3598756 Apr 07 '17 at 13:14
Im sorry, it shows the value (not email) in G66.When i click the button again it doesnt go to the next value. – Kyle Marvin Apr 07 '17 at 13:19
and where does it go? – user3598756 Apr 07 '17 at 13:20
it doesnt go anywhere. it lists F3 but it doesnt go to F4 or F5. – Kyle Marvin Apr 07 '17 at 13:21
my macro doesn't _activate_ cells (so you don't see them _going_ anywhere), it just progressively takes their values and put in G66 one after another at each button click till you reach the value in F35 and then it stops – user3598756 Apr 07 '17 at 13:26
okay good, that is exactly what I want. (im sorry i wasnt clear before) I click the button for the first time, and the first value is in G66 but if i click the button again the second value doesnt show in G66 only the first value. – Kyle Marvin Apr 07 '17 at 13:31
I've tested before that macro and it worked as it is. have you made some changes to it? – user3598756 Apr 07 '17 at 13:42
I have not made any changes. its not giving me errors which tells me its good. but for some reason its not going to the next value after the second button click. – Kyle Marvin Apr 07 '17 at 13:46
So you have that code _exactly_ as I wrote in my answer, haven't you?. And have you got any _event handler_ s that get triggered when Range("G66") gets changed? – user3598756 Apr 07 '17 at 13:48
oh frick man. Im an idiot. its working now... im so sorry. I added it in a sheet not a module. ill learn someday. thx! – Kyle Marvin Apr 07 '17 at 13:48

Be able to select next cell down every time button is clicked Excel VBA

1 Answers1