Record macro using Ctrl+arrow key doesn't work

Question

I am trying to record a macro using the Ctrl+ down arrow to move the next cell within a coluumn that contains data.  I am doing this because the range between data populated cells is variable, in other words, the next cell that contains data may be 5 rows down or it may be 15 rows down.

I am also using relative values when I begin the macro recording. 

The problem I am having is that when I run the macro instead of moving down to the next cell populated with data, it will move down the exact number of cells down that was captured in the recording.

Any ideas on how I accomplish what I have described here?

Thanks,

Dan

Answer 1

It's a small matter to change that macro so that it uses a formula to reference the last cell in column H within the data block.

Sub mcr_BottomH_to_TopE()

Dim rw As Long

If Selection.Column <> 5 Then

MsgBox "Select the top value in column E to start."

Exit Sub

End If

rw = Selection.Row

Do While rw < ActiveSheet.UsedRange.Rows.Count

Cells(rw, 5).Formula = Chr(61) & Cells(rw + 1, 8).End(xlDown).Address

rw = Cells(rw + 1, 8).End(xlDown).Offset(1, -3).End(xlDown).Row

Loop

End Sub

Chr(61) is just a fancy way of describing an equals sign (i.e. ASCII 0×061). .Address by default returns the cell's absolute address like $H$1. If you prefer a relative address (e.g. H1) then change that to .Address(0,0).

Answer 2

When you record your macro, click the Use relative references on when you require commands like Ctrl+▲ or Ctrl+▼ in order to get a relative position subject to the results of a Find or other operation which provides a dynamic location. You can click this option on and off during the recording process as needed.

Answer 3

Jeeped,

I fully understand what you are saying about recording a sub vs. writing the proper sub code. That is why I am reading everything I can get my hands on to teach myself how to work directly with VBA.

Here is the code that the macro recorder produced when I recorded the series of keystrokes shown below the macro code.

------------ macro code begin ------------

Sub Macro3()

'

' Macro3 Macro

' This macro will replace the text in the first line of each data block, in column E

' with a link to the total miles cell at the end of each data block, in column H.

' After replacing the text, the macro will move the cursor to the first line of the

' next data block where the macro will be re-run (Ctrl+Shift+L). This will continue

' until the last data block in the file has been edited.

'

' Keyboard Shortcut: Ctrl+Shift+L

'

ActiveCell.FormulaR1C1 = "=R[7]C[3]"

Selection.End(xlDown).Select

End Sub

------------ macro code end ------------

------------ macro keystrokes begin ------------

1. I place the cursor in cell on the first line of a data block in column E, I type "="
2. I then move the cursor to the right three cells using the „„„ key.
3. I then move the cursor to the last line of the data block by pressing Ctrl+▼two times.
4. I then press ENTER to complete the link function
5. After pressing ENTER the cursor is moved back to the original cell where I typed "="
6. I then position the cursor on the first line of the next data block directly below that data block that was just edited by pressing Ctrl+▼ once.
7. I then re-execute the macro by typing Ctrl+Shift+L.

------------ macro keystrokes end ------------

I want to repeat this for each data block until the last data block at the end of the file has been edited in this way. However if a macro can be written to make this edit on every data block in the file without having to re-run the macro for each data block that would be even better.

As I described in my original post, the number of rows for each data block is variable and unlike the cursor movement in step number 2 above which is always three cells to the right, I can't just specify a set number of rows to move down. To get around this I attempted to record the Ctrl+▼ key combination because, as you know, this key combination moves the cursor to the next cell in the column containing data. However, when played back, the macro moves the cursor down the exact number of rows that Ctrl+▼moved the cursor when the macro was recorded, even when relative references is turned on.

Thanks again for your help,

Dan

Answer 4

Post your recorded macro here with an step-by-step explanation of what you are trying to accomplish and we might be able to rewrite it into a proper VBA sub that will serve your purposes. While recording macros is a powerful tool, a properly written VBA sub is much better.

Answer 5

Jeeped,

Thanks for the response. I misstated in my original description when I stated that I am using relative values. I meant to say I am using relative references when I begin, and throughout the macro recording process.

In spite of having relative references turned on when I record, the macro is still not moving the cursor to the next cell containing data instead it will move the cursor down the exact number of cells that was recorded.

Thanks,

Dan