VBA Code to Find Cell Value in Range, then return offset value

[Chazzo]

Hello,

I'm struggling to find a vba solution to the problem described below and hoping one of the experts here can help.

In Sheet 1 I have a list of fiscal periods in column B beginning in cell B3, with respective beginning dates in column C spanning over 2 years of time.

In Sheet2 I have 9 fiscal periods displayed horizontally begining in cell B4 and these 9 periods could have a variable starting point i.e. could start at period 2 or 3 or 8 etc. but will be limited to 9 sequential periods.

My goal is to capture a range variable from Sheet1 as my "startcell", and then loop from the "startcell" moving downward returning the respective calendar dates which are 1 column to the right.

I wish to have the respective date values written to Sheet2 below the respective fiscal periods as displayed in the second image.

I would like to be able to do this without selecting or activating Sheet1 if possible as I planned to have Sheet1 hidden in the workbook.

Please let me know if you need more detail and I'll do my best to explain further... thank you in advance!

 

[gordsky]

With 9 periods how would you get the code to differentiate between start points. for example if you started on point 2 it is possible to get 9 periods from each so how would you identify which one to start at??

 

[6StringJazzer]

Any particular reason you need to use VBA? This could be done with formulas. In B5:

=INDEX(Sheet1!$C:$C,MATCH($B$4,Sheet1!$B:$B,0)+COLUMNS($B1:B1)-1)

and fill to the right

 

[gordsky]

Any particular reason you need to use VBA? This could be done with formulas. In B5:

=INDEX(Sheet1!$C:$C,MATCH($B$4,Sheet1!$B:$B,0)+COLUMNS($B1:B1)-1)

and fill to the right

would this identify which start point to use. For example if start point in Sht2 B4 was "2" then how would the formula decide which "2" on sheet1 to start with. ?

 

[BSALV]

Sub périods()
     StartDate = Date                                           'the day you want
     Set c = Sheets("sheet1").Range("B13:B100")                 'your range (they are in ascending order !!!!)
     mydates = c.Value2                                         'read all those dates into an array (they are in ascending order !!!!)

     r = Application.Match(CDbl(StartDate), mydates, 1)         'find a match (smaller or equal)
     If IsNumeric(r) Then                                       'found !
          If CDbl(StartDate) > mydates(r, 1) Then r = r + 1     'if it wasn't a perfect match, take the next period or not ????
          MsgBox "startpoint is " & c.Cells(r, 0).Address       'the row r and 1 column to the left

          '1st method = an array
          arr = Application.Transpose(c.Cells(r, 0).Resize(9, 2).Value2)     'read transposed to an array
          Sheets("sheet2").Range("B8").Resize(UBound(arr), UBound(arr, 2)).Value = arr

     '2nd method : straight
          Sheets("sheet2").Range("B12").Resize(2, 9).Value = Application.Transpose(c.Cells(r, 0).Resize(9, 2).Value)

     End If
End Sub

 

[Eric W]

This finds the first matching value:

Range("B5:J5").Value = WorksheetFunction.Transpose(Sheets("Sheet1").Range("A3:A28").Find(Range("B4")).Offset(, 1).Resize(9))

 

[Chazzo]

Gordsky - I should have also noted that B4:J4 in Sheet2 will be pre-populated before the desired code is executed. The variable part is not knowing which period value will be in B4.

I also failed to mention that the variables will be limited from 6-13 so there will be no duplicate starting points (very important note that I left out, my bad).

6 String Jazzer - that formula absolutely works (thank you), however i'm looking to add vba code into a larger project so that when data loads into the workbook, the dates are automatically filled in with date values vs formulas. I may be able to write this formula in code and then replace the range as values.

BSZLV - the code returned a starting point of $A$29 for some reason and because I do not fully understand your code, I'm not sure where the problem exists.

Eric W - this looks like a solve for me - much appreciated!!!

Thank you all for the feedback and time !!

 

[Eric W]

Glad we could help, thanks for the feedback!