ActiveCells

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Hiya, :)

How do i give the activecell in the active sheet a reference or store the value to use within the macro without having to 'go and get it' again?:confused:

Cheers in advance,

Daniel
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hi. Try like this

Code:
Dim mycell As Range
Set mycell = ActiveCell
 

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
71
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

X = Activecell.Value

Will this X 'reference' remain for ever more, can i change it by resetting the value then deleting it when im done. Or is it only valid when runnnig that particular macro?
 

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
71
Office Version
  1. 365
Platform
  1. Windows
how do i get this to work?

ActiveCell = X.Offset(3, 0).Select
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

If you mean that you want to select the cell 3 rows down

Code:
Dim X as Range
Set X = ActiveCell
X.Offset(3, 0).Select
 
Last edited:

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Right then chaps & chapesses, I'm struggling!

Can someone tell me what i'm doing wrong here: -

Sub SORT_into_BRANCHES()
'
' SORT_into_BRANCHES Macro
'
Dim X As Range
Set X = ActiveCell

Sheets("INFO").Select
Range("AM").AutoFilter Field:=1, Criteria1:=X
Range("A9").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("RELATIONSHIPS").Select
X.Offset(3, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
ActiveWorkbook.Worksheets("RELATIONSHIPS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RELATIONSHIPS").Sort.SortFields.Add Key:=Range( _
"H18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("RELATIONSHIPS").Sort
.SetRange Range("H17:H25")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub

I know its this final section where i'm trying to sort the pasted values in ascending order, but i cant get it right.
It needs to work for the actual highlighted selection that has just been pasted, then i want to go back to 'X', and offset to the next cell on the right, until there isnt a cell with a value to the right of 'X'.
Does this make sense?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,835
Messages
5,833,898
Members
430,241
Latest member
Matty Se

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
Top