Create Variable from a date

[Partjob]

I need to create a variable from a date so that I can specify where data should go (Column Number) and pass this to a cell reference. The date it self comes from a sheet in the same workbook it is a different sheet that I am working on though. lets pretend its in A1 on sheet1 I will be able to adjust to suit, and I am working on Sheet2. The date in the cell is in a normal format dd/mm/yyyy for us Brits. Jan 08 is my first column and this would be 1, Feb 08 would be 2 and so on for ever all on sheet2. The columns don't have the days specified just the month and year.

I would then have a referance to work from. For example if the date was 15/03/2008 this would start in column 3. If this is not possible I have no idea if it is or it is, Excel can do stranger things. Then this might be an alternative, after checking the date it matches to the column by checking the month and year along that row, column by column. In this case the dates (Month and Year) are all in row 2.

Just so you have an overview of the aim. I have some data that has been retrieved from an external workbook. I already have the variable for the row it needs to go in. The paste column start is determned by the date in the cell mentioned at the begining of the post. I just need a way of matching the date to the relevant column.

Maybe I am over complicating the issue, not unusual, any guidance, assistance or help would be greatly appreciated.

Regards Partjob

 

[DonkeyOte]

Partjob, as a born Colcestrian I feel obliged to assist you.

when you say a variable -- do you mean in a formula or in VBA ?

Have you looked at using the MATCH function ?
eg assuming date to lookup is in Sheet1 A1 and months are in sheet2 row 1 (you say they are Jan08,Feb08 but I'm presuming they are in date format but presented in MMM-YY fashion -- and are dated 1/1/08, 1/2/08 etc... ?)

This would return column number in Sheet2 of month specified in Sheet1 A1

=MATCH(Sheet1!A1,Sheet2!1:1,1)

But without more info it's hard to give precise answer.

 

[Partjob]

lasw10
You are correct the cells do have the 1st of each month in the cell and they are just formatted to mmm/yy.
I think this has to be VBA. I already have code to get data from a different work this is then to be pasted in the these columns. I have a variable set to get the row. At the moment the data just get put in to the Jan 08 column because I do not know how to set the column with the constants I have.

Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim Fnm As String
Dim Myrow As Integer
Dim MyWbk As String
StartCol = 19
EndCol = Sheet1.Cells(Target.Row, 19).Value
CopRan = Chr(StartCol + 64) & 379 & ":" & Chr(EndCol + 64 + 18) & 379
MyWbk = ThisWorkbook.Name
Fnm = Sheet2.Cells(Target.Row, 1).Text
If Dir(Fnm) = "" Then MsgBox "You have made an error try again", vbInformation, "David Masters"
If Target.Column > 1 Or Dir(Fnm) = "" Then
    Exit Sub
Else
    Workbooks.Open Filename:=Fnm 'place name of workbook in cell
    With ActiveWorkbook
    'Worksheets("Outline Activity Schedule").Range(Cells(379, 19), Cells(379, 25)).Copy
    Sheets("Outline Activity Schedule").Range(CopRan).Copy
       Workbooks(MyWbk).Sheets("Prediction").Cells(Target.Row, 7).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
End If
End Sub

CharlesH helped me a lot with this, but I breakdown the bits of code and get one bit right and then move to the next problem.
This is line I need the variable

Workbooks(MyWbk).Sheets("Prediction").Cells(Target.Row, 7).PasteSpecial Paste:=xlPasteValues

At the moment the data goes in at column 7 (Jan 08).
I need to set a variable for this.
I have a date that is in Sheet1 Column19 (S) all rows are Target row.
I was hopethe code could read this date and match it to a column or convert it to number so I can use it as a variable.
You say you were born here I read between the lines that you are not here anymore.
thanks for taking the time to help
Regards David (Partjob)

 

[DonkeyOte]

David,

would this work for you ?

Dim pastecol As Integer

endcol = Sheet1.Cells(Target.Row, 19).Value
pastecol = 7 + DateDiff("m", DateSerial(2008, 1, 1), CDate(endcol))

Then use pastecol variable instead of 7 in your paste routine.

I am presuming from your post endcol holds the date which determines column to paste result into.

Cheers,

....

I'm only about 10 miles or so from Colchester thesedays having recently ended a decade or so spent in London.

 

[Partjob]

Thanks for this I thought it would be a lot more complicated than that. For the record the EndCol Variable is the number of months that the costs are spread over. This alowed me to just copy the required number of cells and no more. I adjusted the datediff to suit. I did make an error the date was in R column not S.

Dim Pastecol As Integer

Strdt = Sheet1.Cells(Target.Row, 18).Value
Pastecol = 7 + DateDiff("m", DateSerial(2008, 1, 1), Strdt)
Workbooks(MyWbk).Sheets("Prediction").Cells(Target.Row, Pastecol).PasteSpecial Paste:=xlPasteValues

Just for others that my need a solution to a similar problem.

Thanks and Regards
David