Find value, offset 1 row then copy value to a named range cell.

[Upex]

Hi All, and thanks for looking at this for me. I feel that its something simple that I'm missing, but here goes.

I have a worksheet "Minimum Standard Definitions" with a list of 'period & year' in range a19:a96 which uses the named range "Periods".

on sheet "Navigation" I have 13 named ranges (NP1 - NP13) which show a years worth of periods. The first of these ranges (NP1) links to a period and year that the user chooses at startup (from a userform combobox called "cmbBoxPeriod").

What I'm after is code that will find the value of "cmbBoxPeriod" in the range "Periods", offset (1,0), copy that value into NP2, offset(2,0) and copy into NP3, offset(3,0) and copy into NP4 ..... up till NP13.

I thought about using hlookup, vlookup etc, but I need it to offset 1 row a time within a vertical range.

Otherwise, does anyone know how to better feature a rolling 13 period calendar? ie, so my 13 period document can start at period 12 and then go 13, 1 , 2 etc rather than 12, 13, 14 (we have 13 periods a year).

Many thanks for any suggestions.

Kind regards,

Upex

 

[Upex]

Have used this code and it seems to work. Any suggestions to improve would be well received.

Sub Find_Periods()
Dim P1P As Range
Set P1P = Range("Np1")
Application.ScreenUpdating = False
Sheets("Minimum Standard Definitions").Activate
Cells.Find(What:=P1P, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("Np2").value = ActiveCell.Offset(1, 0).value
Range("Np3").value = ActiveCell.Offset(2, 0).value
Range("Np4").value = ActiveCell.Offset(3, 0).value
Range("Np5").value = ActiveCell.Offset(4, 0).value
Range("Np6").value = ActiveCell.Offset(5, 0).value
Range("Np7").value = ActiveCell.Offset(6, 0).value
Range("Np8").value = ActiveCell.Offset(7, 0).value
Range("Np9").value = ActiveCell.Offset(8, 0).value
Range("Np10").value = ActiveCell.Offset(9, 0).value
Range("Np11").value = ActiveCell.Offset(10, 0).value
Range("Np12").value = ActiveCell.Offset(11, 0).value
Range("Np13").value = ActiveCell.Offset(12, 0).value
Sheets("navigation").Activate
Application.ScreenUpdating = True
End Sub