Hi folks
I need a macro that applies some formulas to certain cells all being relative which cell is selected by the user as a start up point. Firstly let me introduce the layout of the table as we use it as a reference.
Mon ----- Tue ------ Wed ------ Thu ----- Fri
E6 -------- F6 -------- G6 -------- H6 -------- I6
E7 -------- F7 -------- G7 -------- H7 -------- I7
E8 -------- F8 -------- G8 -------- H8 -------- I8
E9 -------- F9 -------- G9 -------- H9 -------- I9
E10 ------ F10 ------ G10 ------ H10 ------ I10
E11 ------ F11 ------ G11 ------ H11 ------ I11
E12 ------ F12 ------ G12 ------ H12 ------ I12
E13 ------ F13 ------ G13 ------ H13 ------ I13
E14 ------ F14 ------ G14 ------ H14 ------ I14
E15 ------ F15 ------ G15 ------ H15 ------ I15
E16 ------ F16 ------ G16 ------ H16 ------ I16
E17 ------ F17 ------ G17 ------ H17 ------ I17
E18 ------ F18 ------ G18 ------ H18 ------ I18
E19 ------ F19 ------ G19 ------ H19 ------ I19
E20 ------ F20 ------ G20 ------ H20 ------ I20
E21 ------ F21 ------ G21 ------ H21 ------ I21
E22 ------ F22 ------ G22 ------ H22 ------ I22
E23 ------ F23 ------ G23 ------ H23 ------ I23
E24 ------ F24 ------ G24 ------ H24 ------ I24
E25 ------ F25 ------ G25 ------ H25 ------ I25
E26 ------ F26 ------ G26 ------ H26 ------ I26
E27 ------ F27 ------ G27 ------ H27 ------ I27
Before you think OMG this is going to be lot of work, I have to tell you that I only need help with how macro chooses where to insert formula. I we can create a one macro that will help me on Tuesday, Wednesday, Thursday and Friday
for Mondays purpose I can have have a separate static macro that will not depend on which cell is selected by the user.
OK lets start. So the user should click and select a cell from the range of F6 to I6 before running the macro. Lets do Tuesday as an example. So the user would select cell F6
Now the macro should do the following:
1 ) go 7 cells down and 1 cell left and choose that cell which would effectively be E13 and place formula =$J$33 to that cell
2) then go down 1 cell which would effectively be E14 and place formula =$K$33 to that cell
3) then go down 1 cell which would effectively be E15 and place formula =$L$33 to that cell
4) then go down 8 cells which would effectively be E23 and place formula =$S$33 to that cell
5) then go down 1 cell which would effectively be E24 and place formula =$T$33 to that cell
6) then go down 1 cell which would effectively be E25 and place formula =$U$33 to that cell
The formulas that go into these cells are always the same regardless what weekday it is.
So it would update the table like below Remember user would have to select would select cell F6 as a startup point as its Tuesday and those updates are for Monday.
Mon ----- Tue ------ Wed ------ Thu ----- Fri
E6 -------- F6 -------- G6 -------- H6 -------- I6
E7 -------- F7 -------- G7 -------- H7 -------- I7
E8 -------- F8 -------- G8 -------- H8 -------- I8
E9 -------- F9 -------- G9 -------- H9 -------- I9
E10 ------ F10 ------ G10 ------ H10 ------ I10
E11 ------ F11 ------ G11 ------ H11 ------ I11
E12 ------ F12 ------ G12 ------ H12 ------ I12
E13 ------ F13 ------ G13 ------ H13 ------ I13
E14 ------ F14 ------ G14 ------ H14 ------ I14
E15 ------ F15 ------ G15 ------ H15 ------ I15
E16 ------ F16 ------ G16 ------ H16 ------ I16
E17 ------ F17 ------ G17 ------ H17 ------ I17
E18 ------ F18 ------ G18 ------ H18 ------ I18
E19 ------ F19 ------ G19 ------ H19 ------ I19
E20 ------ F20 ------ G20 ------ H20 ------ I20
E21 ------ F21 ------ G21 ------ H21 ------ I21
E22 ------ F22 ------ G22 ------ H22 ------ I22
E23 ------ F23 ------ G23 ------ H23 ------ I23
E24 ------ F24 ------ G24 ------ H24 ------ I24
E25 ------ F25 ------ G25 ------ H25 ------ I25
E26 ------ F26 ------ G26 ------ H26 ------ I26
E27 ------ F27 ------ G27 ------ H27 ------ I27
On Wednesday user would have to select would select cell G6. as a startup point as its Wednesday and those updates are for Tuesday.
Mon ----- Tue ------ Wed ------ Thu ----- Fri
E6 -------- F6 -------- G6 -------- H6 -------- I6
E7 -------- F7 -------- G7 -------- H7 -------- I7
E8 -------- F8 -------- G8 -------- H8 -------- I8
E9 -------- F9 -------- G9 -------- H9 -------- I9
E10 ------ F10 ------ G10 ------ H10 ------ I10
E11 ------ F11 ------ G11 ------ H11 ------ I11
E12 ------ F12 ------ G12 ------ H12 ------ I12
E13 ------ F13 ------ G13 ------ H13 ------ I13
E14 ------ F14 ------ G14 ------ H14 ------ I14
E15 ------ F15 ------ G15 ------ H15 ------ I15
E16 ------ F16 ------ G16 ------ H16 ------ I16
E17 ------ F17 ------ G17 ------ H17 ------ I17
E18 ------ F18 ------ G18 ------ H18 ------ I18
E19 ------ F19 ------ G19 ------ H19 ------ I19
E20 ------ F20 ------ G20 ------ H20 ------ I20
E21 ------ F21 ------ G21 ------ H21 ------ I21
E22 ------ F22 ------ G22 ------ H22 ------ I22
E23 ------ F23 ------ G23 ------ H23 ------ I23
E24 ------ F24 ------ G24 ------ H24 ------ I24
E25 ------ F25 ------ G25 ------ H25 ------ I25
E26 ------ F26 ------ G26 ------ H26 ------ I26
E27 ------ F27 ------ G27 ------ H27 ------ I27
I have attached spreadsheet with "before" and "after" sheets to show how the result should look if it would be Tuesday and user has selected cell F6 prior running the macro.
Any help is very appreciated.
Cheers
Rain
Bookmarks