ActiveCells

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
69
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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
69
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
69
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
69
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,330
Messages
5,641,547
Members
417,220
Latest member
lam150498

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