CODEWITHSUNDEEP
excelvbarange
Shashank Naik
Shashank Naik

Reputation: 7

save active cell address in a variable vba

The following code is not storing the address of the selection in the variable sek, even when sek is declared as range. It is taking the value of the selected cell. I need to store the address of the cell as range not as string.

Range(zro(fm)).Select
Set sek = Selection

I tried to

set sek=Cells(application.address)

Could not get solution. I have written it like this

For fm = 1 To tmp
Range(zro(fm)).Select
Dim sek As Range: Set sek = Selection
Range(zro(fm + 1)).Select
Dim sel As Range: Set sel = Selection
    Sheets("extract").Cells((5 * a) + 1, fm).Value = Application.WorksheetFunction.max(Range("sek:sel"))
    Sheets("extract").Cells((5 * a) + 2, fm).Value = Application.WorksheetFunction.Min(Range("sek:sel"))
Next

Upvotes: 0

Views: 3134

Answers (2)

Amen Jlili
Amen Jlili

Reputation: 1934

Hope this helps.

 For fm = 1 To tmp
    Range(zro(fm)).Select
    Dim sek As Range: Set sek = Selection
    Range(zro(fm + 1)).Select 
    Dim sel As Range: Set sel = Selection
        Sheets("extract").Cells((5 * a) + 1, fm).Value = Application.WorksheetFunction.max(Range(sek,sel))
        Sheets("extract").Cells((5 * a) + 2, fm).Value = Application.WorksheetFunction.Min(Range(sek,sel))
    Next

Upvotes: 0

Paul Kelly
Paul Kelly

Reputation: 985

What you need is

Application.WorksheetFunction.Max(Range(sek.Address & ":" & sel.Address))

This will combine the ranges.

Also you don't need to activate the range to assign it. You can replace

Range(zro(fm)).Select
Set sek = Selection

with 

Set sek = Range(zro(fm))

Upvotes: 1

Related Questions