On Nov 10, 10:07 am, EE <pras1...@gmail.com> wrote:
> On Nov 10, 2:55 am, Jay <J...@discussions.microsoft.com> wrote:
>
>
>
>
>
> > Hi Prasad -
>
> > Copy this procedure to the worksheet module of the Menu worksheet.
>
> > Note that it assumes that the dates in B1:BD1 of the Metrics sheet are
> > formatted identically to the way they appear in the Calculations sheet
> > reference column
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
>
> > 'Execute this procedure if a date is entered into C8
> > 'of the "Menu" worksheet
> > If Not Intersect(Target, Range("C8")) Is Nothing Then
>
> > Set wMetrics = Worksheets("Metrics")
> > Set wMenu = Worksheets("Menu")
> > Set metricsHeader = wMetrics.Range("B1:BD1")
>
> > 'Search for matching date and abort if none found
> > If metricsHeader.Find(wMenu.Range("C8").Value) Is Nothing Then
> > MsgBox "Date Not Found."
> > Exit Sub
> > End If
>
> > Set anchorCell = metricsHeader.Find(wMenu.Range("C8").Value)
> > c = anchorCell.Column
> > dateformat = anchorCell.NumberFormat
>
> > With Worksheets("metrics")
> > Set formulaCells = Application.Union(.Cells(2, c), _
> > .Cells(3, c), .Cells(4, c), .Cells(8, c), .Cells(9, c), _
> > .Cells(10, c), .Cells(15, c), .Cells(16, c), .Cells(21, c), _
> > .Cells(22, c), .Cells(27, c), .Cells(28, c), .Cells(33, c), _
> > .Cells(34, c), .Cells(35, c))
> > End With
>
> > 'Enter the formula into the 14 destination cells
> > formulaCells.Formula = "=vlookup(A2 & "" "" & text(" & _
> > anchorCell.Address & "," & Chr(34) & dateformat & Chr(34) & _
> > "),Calculations!D$3:$E$36,2,FALSE)"
>
> > Target.Select
> > MsgBox "Formulas updated for " & Target.Value & "."
>
> > End If
>
> > End Sub
> > ---------
> > Jay
>
> > "EE" wrote:
> > > Hi
>
> > > Thanks for the help.
>
> > > In a sheet called "Metrics", I have a matrix where row 1 is dates, So
> > > I have different dates from B1:BD1.
>
> > > Now I enter a date in one cell in a different sheet. I want a macro to
> > > copy a "dynamic" formula to different cells in the column where it
> > > finds a match of the date I entered.
>
> > > To give an example. In Sheet "Menu", in C8, I enter a date Jun 12,
> > > 2007. Now Jun 12, 2007 is in Cell G1 in "metrics" sheet . When I run
> > > the macro, I want the macro to copy a specified formula, say
> > > "VLOOKUP(""First Column Same Row""&"" ""&"First Row Same Column",
> > > Calculations!R3C4:R36C5,2,FALSE) into cells G2, G3, G4, G8, G9, G10,
> > > G15, G16, G21, G22, G27, G28, G33, G34,G35.
>
> > > SO the dynamic vlookup formula for G27 will be
>
> > > Vlookup(A27&" "G1, Calculations $D$3:$E$36,2,FALSE)
>
> > > for AB22, it will be
>
> > > Vlookup(A22&" "AB1, Calculations $D$3:$E$36,2,FALSE)
> > > and so on
>
> > > If June 12, 2007 was in column R (R1, then, the formula is pasted in
> > > Collumn R (with the same row numbers as above.)
>
> > > I hope I was clear. Thanks in advance for your help.
>
> > > Best
> > > Prasad- Hide quoted text -
>
> > - Show quoted text -
>
> Many Thanks Jay. I have pasted this is the sheet. But it gives me the
> "Date Not Found" message. I have checked the Date I selected from a
> validation list in Menu sheet exists in the metrics header in the same
> format.
>
> Also in the formula what we have as "A2" will become A3 for Row 3, A4
> for Row 4 and so on. I am not sure whether thats happening.
>
> Thanks a lot for your time. Appreciate it. In the new post I have made
> the problem statement more clear.- Hide quoted text -
>
> - Show quoted text -
Hi Jay
It is working. My dates in Metrics sheet was a formula (the first date
+ 7). That was why it gave me the message.
Also the formula changes by row (obviously A2 become A3 when you apste
it there.
There is one problem I see in the date format for the concatenated
look-up valkue. But I will try and figure that out.
Thanks
Prasad
Similar Threads
