macro-ize these manual key strokes

jbesr1230

Board Regular
Joined
Oct 16, 2004
Messages
96
macro-ize these manual key strokes

Greetings,

I am trying to make the series of keystrokes below into a looping macro. I’ve tried the macro recorder but it hasn’t helped.

Keystrokes:
“=”, left arrow 5 times, End down-arrow, Enter, Down arrow once. Keep repeating this sequence i.e. “=”, left arrow 5 times, End down-arrow, Enter, down arrow once, until the “down arrow once” step places the cursor in a cell with XXX. When this happens stop the macro.

Thanks.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Why don't we try to make this a little better--what is it exactly you are trying to do with this code?
 
Upvote 0
On a worksheet I have a dataset in columns B:F. The Data AutoFilter is active in Column D, displaying non-blanks.

For this sample set rows 13,14,15, 20,21, 23,24 & 52 are visible.

I would like to place a formula using a macro in selected cells in column G. See below for how this is done manually. You will see a repeating set of key strokes.

Manually, with cursor in cell G13, I hit “=”, left arrow 5 columns , so to cell B13, and then I do “end down” and return. In cell G13 is “=B14”. Row 14 is a visible row. The cursor is now at G 14. I arrow down 1 row to G15.

I hit “=”, left arrow 5 columns, (cell B15) hit “end down” and return. In cell G15 is “= B20”. Row 20 is a visible row. The cursor is now at cell G20. I arrow down 1 row to cell G21.

I hit “=”, left arrow 5 columns, (cell B21) hit “end down” and return. In cell G21 is “= B23”. The cursor in at cell G23. Row 23 is a visible row. I arrow down 1 row to G24.

I hit “=”, left arrow 5 columns, (cell B24) hit “end down” and return. In cell G24 is “= B52”. The cursor in at cell G52. I arrow down 1 row and so on and so on and so on.

I hope this is enough to see the repeating pattern. What I cannot do with the macro recorder is get the sequence of:
…………..5 columns to the left then EndDown.

if the cursor comes to cell in column G that contains XXX the macro will stop.

Does the help?

Thanks.
 
Upvote 0
Would this work for you?

Code:
Dim C As Range, rngB As Range
'loop through visible rows
For Each C In Columns("G:G").SpecialCells(xlVisible)
    'if value is not XXX
    If C.Value <> "XXX" Then
        '(skipping row 1, assuming they are headers)
        If C.Row <> 1 Then
            Set rngB = C.Offset(0, -5) 'offset 5 cells to left
            Set rngB = rngB.End(xlDown) '"end down" in column B
            C.Formula = "=" & rngB.Address(0, 0) 'put formula in cell
            Set C = Cells(rngB.Row, "G") 'set c to the same row so it loops correctly
        End If
    Else
        Exit Sub 'end if value in G = XXX
    End If
Next C
 
Upvote 0
Thank-you Von Pookie!! This has saved me lots of key punching. There are a couple of small items that would be nice enhancements if possible.
....1. Could you set the macro so it starts evaluating at row 19? It goes the very top of the column as it is written now.
....2. the macro is filling each visible row with data. Can it skip a visible row each time?
For instance we have visible rows 23,27,28,53,54,71,72,101,102. Starting with row 23 I would like the formula in rows 23, 28, 54, 72, 102.

Thanks so much & all the best.

j2011 Reporting\[FeeRevwFIN20110307.xls]FeeRevQu
 
Upvote 0
I'm not really sure what you want to do with item #2 there--if you just use Ctrl+End you're not just going down one row, you're going to end up wherever the data is. I can't control that.

As far as item #1, let's try specifying the range for it to look through, starting with G19:
Code:
Dim rngG As Range, EndRow As Long
Dim C As Range, rngB As Range
'find the XXX for the end row
On Error Resume Next
'the -1 is so the XXX will not be included
EndRow = Columns("G:G").SpecialCells(xlVisible).Find("XXX").Row - 1
On Error GoTo 0
If EndRow = 0 Then 'if value not found
    'show msgbox and end
    MsgBox "'XXX' not found. Ending macro."
    Exit Sub
End If
'set the range you want to go through
Set rngG = Range("G19:G" & EndRow)
'loop through visible rows
For Each C In rngG.SpecialCells(xlVisible)
    Set rngB = C.Offset(0, -5) 'offset 5 cells to left
    Set rngB = rngB.End(xlDown) '"end down" in column B
    C.Formula = "=" & rngB.Address(0, 0) 'put formula in cell
    Set C = Cells(rngB.Row, "G") 'set c to the same row so it loops correctly
Next C
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top