Reference Variable Row Numbers in Macro


I am trying to write some code that will utilize an input box to find a value. At that point I want to run a macro that will reference the cell rows of all values that equal the input box value. I have sorted this column in ascending order so that similiar value will be together.

I believe the code I'm looking will look similar to this but I don't know how prepare the range.

For a = 1st cell row # = InputBox to Last Cell row # = InputBox

So say I have Finance in rows 2-5, HR in rows 6-9 and Sales in rows 10-15. If I entered HR in the input I would want to reference cells 6-9.

e.g. For a = 6 to 9

Can someone please help me with this problem?


  • I really appreciate your views on macro.
    [link=Payroll India][/link]

  • Hi Dragonfyre, I don't understand what you mean by reference the cells, do you wish to do something with the values in those rows, or simply select that range?

    The example below assumes the department names are in column A, and values in column B, it would go through all the rows, total up the values in column B and at the end select the range of values in column B,if it finds no matches, will messagebox to say so. Just adapt to your needs:

    Sub Macro1()

    Dim strDept As String
    strDept = InputBox("Gimme a department name!")
    Dim iRow As Integer, SumTotal As Double, FirstRow As Integer, LastRow As Integer
    iRow = 2
    FirstRow = 99999
    LastRow = 0
    Dim boolFound As Boolean
    boolFound = False
    If Cells(iRow, 1).Value = strDept Then
    boolFound = True
    If iRow < FirstRow Then FirstRow = iRow
    If iRow > LastRow Then LastRow = iRow
    SumTotal = SumTotal + Cells(iRow, 2).Value
    If Cells(iRow, 1).Value = "" Then GoTo NoneFound
    End If
    iRow = iRow + 1
    Loop Until Cells(iRow, 1).Value <> strDept And boolFound = True

    'Do something here with either the total values, or select the range
    'To select the range in column B:
    Range("B" & FirstRow & ":B" & LastRow).Select
    Exit Sub

    MsgBox ("No values found for department " & strDept)
    End Sub

    HTH, Dai

    Do or do not, there is no try. |
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!