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
-----------
harry
[link=Payroll India]http://www.topsyssolutions.com[/link]
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. |
------------------------------------------