use current cell address as beginning of macro
 

Hi,

How can I use the active cell address as a part of a macro.

I want to use the column heading (which is a part of a file name) to
copy certain fields from the corresonding file into the column where
the active cell is at.

This is what I've got:

Sub test()

Sht = "1) Other Income"

With Worksheets(Sht)

FR = 14 'FirstRow
LR = 25 'LastRow
COL = "W" 'Column
Cno = 11
Cnm = Cno + 1

.Range(COL & FR & ":" & COL & LR).Formula = "=+'J:\Year End\Year
End 2008\Disclosure Packs\[" & Range(COL & Cnm).Value & " - " &
Range(COL & Cno).Value & " FY08 REPORTING PACK.xls]" & Sht & "'!F" &
FR

End With

End Sub

Cheers,

Harold

use current cell address as beginning of macro
 

Hi

ACell=activecell.Address
AColumn=Activecell.column

Regards,
Per

"mohavv" skrev i meddelelsen
...
Hi,

How can I use the active cell address as a part of a macro.

I want to use the column heading (which is a part of a file name) to
copy certain fields from the corresonding file into the column where
the active cell is at.

This is what I've got:

Sub test()

Sht = "1) Other Income"

With Worksheets(Sht)

FR = 14 'FirstRow
LR = 25 'LastRow
COL = "W" 'Column
Cno = 11
Cnm = Cno + 1

.Range(COL & FR & ":" & COL & LR).Formula = "=+'J:\Year End\Year
End 2008\Disclosure Packs\[" & Range(COL & Cnm).Value & " - " &
Range(COL & Cno).Value & " FY08 REPORTING PACK.xls]" & Sht & "'!F" &
FR

End With

End Sub

Cheers,

Harold

use current cell address as beginning of macro
 

On Apr 18, 3:47*am, mohavv wrote:
Hi,

How can I use the active cell address as a part of a macro.

I want to use the column heading (which is a part of a file name) to
copy certain fields from the corresonding file into the column where
the active cell is at.

This is what I've got:

Sub test()

Sht = "1) Other Income"

With Worksheets(Sht)

* * FR = 14 * * 'FirstRow
* * LR = 25 * * 'LastRow
* * COL = "W" * 'Column
* * Cno = 11
* * Cnm = Cno + 1

* * .Range(COL & FR & ":" & COL & LR).Formula = "=+'J:\Year End\Year
End 2008\Disclosure Packs\[" & Range(COL & Cnm).Value & " - " &
Range(COL & Cno).Value & " FY08 *REPORTING PACK.xls]" & Sht & "'!F" &
FR

End With

End Sub

Cheers,

Harold

Dim slctn As What ever it is. Range, Double, Single, Long, String.
Since you ahd mentioned words, you may want to call it a String.
Try recording the macro from where you want it started. This will
give you an idea on what to do.