Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Reference Variable Row Numbers in Macro

dragonfyre77dragonfyre77 Posts: 1Member
Hello,

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?

Comments

  • harrywhiteharrywhite Posts: 1Member
    I really appreciate your views on macro.
    -----------
    harry
    [link=Payroll India]http://www.topsyssolutions.com[/link]

  • DaiMitnickDaiMitnick Posts: 77Member
    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:

    [code]
    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
    Do
    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
    Else
    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

    NoneFound:
    MsgBox ("No values found for department " & strDept)
    End Sub
    [/code]

    HTH, Dai


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