Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula to cells on the basis of value in row 1
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula to cells on the basis of value in row 1
On Nov 10, 2:55 am, Jay 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula to cells on the basis of value in row 1
On Nov 10, 10:07 am, EE wrote:
On Nov 10, 2:55 am, Jay 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I align table cells on the basis of decimal point | Excel Discussion (Misc queries) | |||
i need a formula that calculates current age on a daily basis? | Excel Discussion (Misc queries) | |||
Create macro to copy data on a scheduled basis | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
can i colour a cell on basis of results of a formula e.g clour bl. | Excel Worksheet Functions |