Reference Variable Row Numbers in Macro

dragonfyre77

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?

harrywhite

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

DaiMitnick

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. |
------------------------------------------