Copy in Macro based on present cursor position 1

[kdeans]

Excel 97
I need to do a copy and paste special from within a macro based on the current cell selected. That is to say that I need to take cells (?)$4?)$21 and copy them to another location in order to do a compare operation but the column number (the question mark in the above example) needs to depend on what cell I currently have selected. If I am in column Q (any cell in that column selected), I need to copy Q4 to Q21. If I am in column AD, I need to copy AD4 to AD21, etc.
I have tried a few variations of ActiveCell.Offset().Select but haven't found something to work. Any ideas?

 

[DaleWatson123321]

Hi kdeans,

I've created a test file which works, with the following code:
-------------
START OF CODE
-------------

Dim colm As String
Dim topcell As String
Dim botcell As String
Dim fromrange As String

Sub Set_FromRange()
colm = ActiveCell.Column
Range("colmletr&quot = colm
topcell = Range("addr1&quot
botcell = Range("addr2&quot
fromrange = topcell & ":" & botcell
Range(fromrange).Name = "from_range"
End Sub

-------------
END OF CODE
-------------

IMPORTANT NOTE: The above code requires that you include in your worksheet the following three "Range-Named" cells:

"addr1" - containing this formula: =ADDRESS(4,colmletr)

"addr2" - containing this formula: =ADDRESS(21,colmletr)

"colmletr" - (leave blank)

The code will place into "colmletr", the number which respresents the column on which the user's cursor is positioned.

These three (Range-Named) cells can be placed anywhere on the sheet (off to the side, out of harm's way).

Incidentally, I tried using
"Application.WorksheetFunction.Address" as a function in VBA, but it does NOT work. VBA permits the use of some Worksheet-based functions, but not all. (This would have avoided the need for the formulas in the worksheet.)

Anyway, this works. I hope it helps. Please let me know if you have any difficulty. I can email you the file if you prefer.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Hi kdeans,

Just reviewing the postings, and noticed you haven't responded to this one.

Did you try out the VBA code ? ...and did it work for your application ?

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

UPDATE - USEFUL CODE FOR GENERAL USE

For the benefit of other Tek-Tips members, "kdeans" has replied with the following email:

"The code you posted at Tek Tips worked perfectly and I
have incorporated it into other sheets as well as the one
that I had originally intended."

I hope this "general purpose" code can be used by others in situations where you need to: COPY A RANGE WHICH IS DEPENDENT ON THE COLUMN THE USER HAS THE CURSOR POSITIONED.

I have re-posted the same code, but now with "commenting" to further explain this (relatively simple) process.

=============
START OF CODE
=============

Dim colm As String
Dim topcell As String
Dim botcell As String
Dim fromrange As String

Sub Set_FromRange()

colm = ActiveCell.Column
' captures the number of the current column.

Range("colmletr&quot = colm
' places the column number into cell named "colmletr".

topcell = Range("addr1&quot
' "addr1" has formula: =ADDRESS(4,colmletr).
' this creates a cell address based on row 4, and the
' column on which the user currently has the cursor
' positioned.

botcell = Range("addr2&quot
' "addr2" has formula: =ADDRESS(21,colmletr).
' this creates a cell address based on row 21, and the
' column on which the user currently has the cursor
' positioned.

fromrange = topcell & ":" & botcell
' this concatenates the two addresses.

Range(fromrange).Name = "from_range"
' this creates a range name called "from_range",
' based on "fromrange" which now contains the
' concatenated cell addresses.

End Sub

===========
END OF CODE
===========

If anyone has developed a method of performing this routine entirely in code (WITHOUT having to use sheet-based formulas), please share it with Tek Tips users.

If you would like a copy of the actual file, please don't hesitate to ask - simply email me and I'll send the file by return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca