1

Can someone help me to be able to use an index number instead of a letter range?

See code below:

Function Integer
    Dim value As Integer
noob231454
  • 27
  • 1
  • 1
  • 5
  • 1
    I want to say because Columns(2) refers to a Column, not the cells inside the column. – Matthew Apr 16 '16 at 02:25
  • Hmmm.... Then how to you refer to the cells inside the column? I do have a For Each loop.... But it looks like it doesn't work so well – noob231454 Apr 16 '16 at 02:33
  • Why do you need to use columns? Why can't you just use the range? – Matthew Apr 16 '16 at 02:36
  • give this a go, I'm not sure if this will work as intended... `Worksheets("Sheet1").Columns(1)` https://msdn.microsoft.com/en-us/library/office/ff197266.aspx – Matthew Apr 16 '16 at 02:40
  • Hmm... Nope... Thanks for trying – noob231454 Apr 16 '16 at 02:45
  • If I could remember VBA better then maybe I could help more... But I see no reason why you can't just use a range and have the user insert the range you wish them to check for. – Matthew Apr 16 '16 at 02:46
  • Just a requirement for a homework assignment. User has to put in a column number into a formula. Kinda ridiculous – noob231454 Apr 16 '16 at 02:47
  • take a look at this... This should help you.. http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa – Matthew Apr 16 '16 at 02:50
  • For starters, see [UDF returns the same value everywhere](http://stackoverflow.com/questions/35910683/udf-returns-the-same-value-everywhere/35956748#35956748). You might also want to truncate that column to the intersect with the worksheet's UsedRange. –  Apr 16 '16 at 02:58

1 Answers1

3

You do not need to loop :)

Is this what you want? 

Sub Sample()
    Dim Rng As Range

    Set Rng = Columns(2)

    '~~> This will give you the row number of the max value
    '~~> =MATCH(MAX(B:B),B:B,0)
    MsgBox Evaluate("MATCH(MAX(" & Rng.Address & ")," & Rng.Address & ",0)")

    '~~> OR
    MyVariable = Evaluate("MATCH(MAX(" & Rng.Address & ")," & Rng.Address & ",0)")
    MsgBox MyVariable 
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250