Copy formula to cells on the basis of value in row 1

[EE]

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

 

[Jay]

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

 

[EE]

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
















- 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.

 

[EE]

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