PDA

View Full Version : Storing row numbers



kurtwagner
10-12-2017, 11:53 PM
Hello! I have a sheet that looks like this:

20645

I need to do graphs through VBA with it, but for that I need to store the row numbers of the years. For example, if I want to find the row values of the years exactly below "Parameter 1" to put as the graph range, how should I do it? It needs to be considered that the number of years below each parameter will change with time, so I was thinking of using as first year the first cell under the desired "parameter" and then looking for the number of the row of the first cell under it with a year that equals B2.

jolivanes
10-13-2017, 01:18 PM
Since it is difficult to check if code works on a picture, I would say to attach a workbook on which people can try their handy work before posting it.

BTW, try to be consistent. All Parameters have a space between the word and the number but not Parameter5.

jolivanes
10-13-2017, 01:40 PM
This should be a start on getting the row numbers as requested.
For this to work, there need to be a space between "Parameter" and the number following it, like you have except with "Parameter5".

Sub Get_Row_Numbers()
Dim a As Long, TopRow As Long, LastRow As Long
a = InputBox("Which Parameter do want to graph?", "Enter Parameter number")
TopRow = Columns(1).Find("Parameter " & a).Row + 1
LastRow = Cells(TopRow, 3).End(xlDown).Row

MsgBox TopRow
MsgBox LastRow
End Sub

jolivanes
10-14-2017, 11:48 AM
You suggested to look for (find) the value of cell B2 but you would need error checking if it is like the 2nd picture where there is no 2017 further down

jolivanes
10-18-2017, 09:28 AM
Any luck?
It's always nice to get some feedback, one way or another.