Assigning a column to a variable and using that variable in a formula

[eddyble]

I'm looking to assign a variable to the final column in a sheet (column changes based on data) and use this variable in a formula to reference the column.

Dim x As Range

'this should assign x as the range variable for the final column
Cells(3, Columns.Count).End(xlToLeft).Select 
ActiveCell.EntireColumn.Select
Set x = Selection

'inserting the following formula into some other cell

ActiveCell.FormulaR1C1 = "=LARGE(" & x & ",2)"

This returns a type mismatch . Ideally i want my x variable to represent some column range (e.g P:P).

Can anyone help me out?

 

[kevin9999]

I don't know how you would do that with the Formula-Large function-variable approach, however, if your aim is simply to put the second largest value (I note you use 2 as the second argument in your formula) then the following will do that.

Sub Column_Variable()
    Dim lc As Long
    lc = Cells.Find("*", , xlFormulas, , 2, 2).Column
    ActiveCell.Value = Application.Large(Columns(lc), 2)
End Sub

 

[eddyble]

I don't know how you would do that with the Formula-Large function-variable approach, however, if your aim is simply to put the second largest value (I note you use 2 as the second argument in your formula) then the following will do that.

Sub Column_Variable()
    Dim lc As Long
    lc = Cells.Find("*", , xlFormulas, , 2, 2).Column
    ActiveCell.Value = Application.Large(Columns(lc), 2)
End Sub

Appreciate the response, that would work - it's just that i need the range reference elsewhere as well for different formulas

 

[eddyble]

Okay, found a solution - but it's annoyingly awful (I apologise in advance to future readers)

x = Split(Cells(3, Columns.Count).End(xlToLeft).Address, "$")(1) 'this stores the letter of the column as x (let's say it's y for example)
x = x & ":" & x 'this converts x variable into y:y (basically a reference to column y)

ActiveCell.FormulaR1C1 = "=LARGE(" & x & ",2)" 'annoyingly, this doesn't work. This results in =LARGE(y:(y),2) For the life of me i cannot tell why the extra bracket around the second y appears.
'The x variable works exactly as you would expect until it enters a formula and blows up. I've tried all manner of things to no success.

Anyway... i ended up just assigning x to some donor cell and using the INDIRECT function to reference the range. Awful i know.

 

[kevin9999]

Okay, found a solution - but it's annoyingly awful (I apologise in advance to future readers)

x = Split(Cells(3, Columns.Count).End(xlToLeft).Address, "$")(1) 'this stores the letter of the column as x (let's say it's y for example)
x = x & ":" & x 'this converts x variable into y:y (basically a reference to column y)

ActiveCell.FormulaR1C1 = "=LARGE(" & x & ",2)" 'annoyingly, this doesn't work. This results in =LARGE(y:(y),2) For the life of me i cannot tell why the extra bracket around the second y appears.
'The x variable works exactly as you would expect until it enters a formula and blows up. I've tried all manner of things to no success.

Anyway... i ended up just assigning x to some donor cell and using the INDIRECT function to reference the range. Awful i know.

Fair enough. If you were interested (and I realise this doesn't fit your needs - but I did it anyway for fun) here's a UDF that works in any cell you type it. Just leave the argument empty - simply type =GetLarge2() in any cell.

Public Function GetLarge2()
    Application.Volatile
    Dim lc As Long, x
    lc = Cells.Find("*", , xlFormulas, , 2, 2).Column
    x = Application.Large(Columns(lc), 2)
    GetLarge2 = x
End Function

 

[Peter_SSs]

Get rid of the R1C1

ActiveCell.FormulaR1C1 = "=LARGE(" & x & ",2)"

.. or you could try this instead

Dim x As Long

x = Cells(3, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=LARGE(C" & x & ",2)"

 

[smozgur]

The code in the marked post fails (#4).

The problem in the original code is using the Select method to define the x range variable. So, the ActiveCell is not the actually intended cell that the result function is supposed to be entered via code.

The actual solution to this problem is how @Peter_SSs explained it as the alternative code at #6. Therefore, the solution post has been accordingly switched to help future readers.