put formula in cell via vba

[jbesr1230]

Greetings:

I have a worksheet that has a pivot table and the data filter is "on" and
showing only non blanks in the pivot table. I would like a macro that puts a formula in
the current cell which is outside the pivot table area. The formula is: = 3
columns left then End Down (xlDown). This cell is in the pivot table area.
The formula is placed in the cell where the cursor first is and the cursor moves
down 2 visible rows and repeats the : “= 3 columns left then End Down (xlDown)” - i.e 1 visible cell
between each "formula cell". This repeats until the cursor, when it is moving
down one row, lands on cell with "xxx".

Thank you.

 

[VoG]

Hello. If you cannot describe this better I doubt you'll get an answer. Do you just want to find "xxx" in a range then insert a value some number of rows and columns offset from it?

 

[jbesr1230]

Here is the reworded request. Hopefully this is more clear.

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 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.

Thanks