CODEWITHSUNDEEP
excelvba
Student1001
Student1001

Reputation: 111

Finding a particular value in an excel file and its relevant column

I am extremely new in VBA and I would like to find the minimum value of a row and return its column number by creating a command button. For example, in a row: 3 2 2 4 5 6 7 I need to return the column number 2, even though 2 appears twice.

my code currently looks like this: 

Private Sub CommandButton1_Click()
Dim myrange As range
Dim answer As Double
Dim f As Range
Dim column1 As Integer

Set myrange = Worksheets("Solver VBA").range("C94:T94")
answer = Application.WorksheetFunction.Min(myrange)
MsgBox answer

Set f = Worksheets("Solver VBA").Cells.Find(answer).Column
MsgBox f
End Sub

I keep getting the runtime error '91' and yellow highlight on the 3rd last line.

Edit: I have tried these steps as suggested and it works (but it gives me column number 3 instead of 2, which is fine in my case as well):

Private Sub CommandButton1_Click()

Dim myrange As range
Set myrange = Worksheets("Solver VBA").range("C94:T94")

Dim answer As Double
answer = Application.WorksheetFunction.Min(myrange)
MsgBox answer
Private Sub CommandButton1_Click()

Dim column1 As Integer
column1 = Application.Match(answer, myrange, 0) + 2
MsgBox column1

End Sub

Upvotes: 2

Views: 93

Answers (3)

Dirk Reichel
Dirk Reichel

Reputation: 7979

I suggest doing it like that (keeping all your variables):

Private Sub CommandButton1_Click()

  Dim myrange As Range
  Set myrange = Worksheets("Solver VBA").Range("C94:T94")

  Dim answer As Double
  answer = Application.Min(myrange)

  Dim column1 As Integer
  column1 = Application.Match(answer, myrange, 0) + 2

  Dim f As Range
  Set f = Application.Index(myrange.EntireRow, 1, column1)

End Sub

if you need just the range (the cell containing the value) you also can directly use something like that:

Set f = Worksheets("Solver VBA").Range("B94").Offset(, Application.Match(Application.Min(Worksheets("Solver VBA").Range("C94:T94")), Worksheets("Solver VBA").Range("C94:T94"), 0))

or like in my comment to just get the column number:

column1 = Application.Match(Application.Min(Worksheets("Solver VBA").Range("C94:T94")), Worksheets("Solver VBA").Range("C94:T94"), 0) + 2

Should be self explaining. But if you still have any questions, just ask ;)

Upvotes: 2

Dan Donoghue
Dan Donoghue

Reputation: 6186

Change

MsgBox f

to

MsgBox f.address

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Consider

  1. Dimming f as Long
  2. Dropping the Set
  3. Using .Column
  4. Keeping the MsgBox

You see with .Columns you get a Range object. With .Column you get a Long.

Upvotes: 1

Related Questions