Enter fixed data pattern bassed on user input

[hugo15]

I've been going round in circles with this for hours and have got no-where. Can anyone help? I 'm trying to get Excel to paste in a set pattern of data based on an input cell.

In row 1 I have months starting in cell B1. B1 = April 12, C1 = May 12, D1 = June 12, and so on

The user would enter the data in column A, starting in row 2

If the date in A2 = May 12 then Excel would enter 10 in cell C2, 20 in cell D2 and 30 in cell E2. If the date in A2 is changed to June 12, then the previous data would be cleared and Excel would enter 10 in E2, 20 in F2 and 30 in G2.

Date would be entered in A3, A4, A5 etc and I would want it to post the same fixed data depending on the date the user enters in teh relevant row.

I've tried IF statements and also some code but can't even get near it working. If anyone can help it would be greatly appreciated.

 

[hugo15]

No need to apologise for being blunt Rick, I've leant my lesson! I'll try to explain what I'm trying to do more clearly.

In row 1 I have months starting in cell B1. B1 = April 12, C1 = May 12, D1 = June 12, and so on
A2 is where the user would input the start date.

What I want it to do is based on the start date, go to that month and enter a fixed pattern of data. The maximum number of data points would be 36.
For example if the user entered a start date of June 12, Excel would go to cell D2 and paste in the 36 data points into D2 and the next 35 cell to the right of it The values of the 36 data points would be fixed and could be typed into the spreadsheet as standing data or entered into the macro code.

Hope this is a little clearer.

 

[Rick Rothstein]

Okay, I think I understand what you are looking for. See if this macro does what you want (adjust the comma delimited pattern that is assigned to the Pattern variable to suit your actual needs)...

Sub ApplyPatternFromDates()
  Dim X As Long, Z As Long, LastRow As Long, LastCol As Long
  Dim Pattern As String, PatArr As Variant, HeaderCell As Range
  Pattern = "10, 0, 0, 10, 0, 0, 30, 0, 0, 10, 10, 0, 0"
  PatArr = Split(Replace(Pattern, " ", ""), ",")
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For X = 2 To LastRow
    For Z = 2 To LastCol
      If Format(Cells(X, "A").Value, "mm yyyy") = Format(Cells(1, Z).Value, "mm yyyy") Then
        With Cells(X, Z).Resize(, UBound(PatArr))
          .Value = PatArr
          .Value = .Value
        End With
      End If
    Next
  Next
End Sub

 

[hugo15]

Thanks Rick. Not had chance to try it today. Will have a look next week.