Thread: Macro to insert formulas to certain cells respective of cell selected

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

Hey Rain,

What does the attached have to do with your problem?

Did you attach the correct file?

Try this in E14 and pull it down for the other blank cells,

=HLOOKUP(D13,$D$32:$X$33,2,FALSE)

Then copy it to the lower blank cells.

Is that what you want? A simple HLookup formula?

What does Monday, Tuesday... Etc have to do with it.

If you want to use across the columns make the D13 in the formula above a $D13.

Yes you are correct. It can be hlookup formulas in those cells. I didn't even think of that. But I need to have them formulas filled in a relative way.


You see there is information generated to cells D33 to X33 every day. Now we can use this information. For example. If its Monday we can only fill in E7 to E12 and E18 to E22. We don't have the information available for E13 to E17 and E23 to E27. Though we will have that information when Tuesday arrives. On Tuesday we will have info F7 to F12 and F18 to F22. But now we also have information for E13 to E17 and E23 to E27 as well so we can have this filled in for the previous day i.e. Monday. So it always works that way that you generate information for current day and some information for day before.

Now as far as it goes as coding for this exercise I would like to do it the relative way. The reason why it It needs to be relative is as I want to reduce the amount of lines.
I would like have two macros. I would then use these macros within other macros via Application.Run method.


As I'm rubbish with coding can you help me to formulate the following codes Please help me with these two macros and I will be happy man. now i will write it in general terms. Please change it so it will work on excel.


Sub hlookup()

ActiveCell.FormulaR1C1 = "=HLOOKUP(R7C4:R27C4,R32C4:R33C24,2,FALSE)"

auto fill from row 7 to row 27
Clear clear values active column, rows 13 to 17 and rows 23 to 27
End sub

and the second macro something along these lines

Sub previous_day_info()
Current active column
select -1 column (i.e. move one column left)

the following formula
"=HLOOKUP(R7C4:R27C4,R32C4:R33C24,2,FALSE)"
applied to cells on rows 13 to 17 and rows 23 to 27

So on Monday I could use something amazing like this:

Public Sub choose_weekday()
tryagain:
    ln1 = "1 Monday" & vbNewLine
    ln2 = "2 Tuesday" & vbNewLine
    ln3 = "3 Wednesday" & vbNewLine
    ln4 = "4 Thursday" & vbNewLine
    ln5 = "5 Friday" & vbNewLine
   msg = ln1 & ln2 & ln3 & ln4 & ln5
   resp = Application.InputBox(msg, "choose # to carry over to new week", , , , , , 1)
   If resp = False Then Exit Sub
   
   Select Case resp
    Case 1: Call Monday
    Case 2: Call Tuesday
    Case 3: Call Wednesday
    Case 4: Call Thursday
    Case 5: Call Friday
    Case Else
        pt = MsgBox("Invalid entry enter number 1 - 5", vbCritical)
        GoTo tryagain
   End Select
End Sub

Private Sub Monday()
    Range("E7").Select
    Application.Run "hlookup"
End Sub
Private Sub Tuesday()
    Range("F7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub
Private Sub Wednesday()
    Range("G7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub
Private Sub Thursday()
    Range("H7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub
Private Sub Friday()
    Range("I7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub

Of course I would add some other stuff there as well.










You only have the information for the current day So its updated once every day. E7 to E12 information

Hi rain4u
I've decided it is easier for you to learn how to code than for me to understand all the nuances to option traiding and trying to understand what you want. I was at a real streatch to see that a HLookup might do what you need. You calim that HLookup did solve your problem but you need it down in a relative way. I wonder if you put the $D in your formula to see if that didn't solve the question.

I'm off to help others with their problems.

Hlookup does not solve my problem. You are totally missing the point. Hlookup is an option that I overlooked "as an formula" but it doesn't solve the problem. My problem has never been the formula. The problem is placing the formula into certain cells without hard coding it into macro directly. This is getting frustrating. I'm spending more time writing the information than you spend reading them. I'm sorry but this is how it feels.

Just to give someone else a chance to help me I will do a quick recap only of the key elements.

from my first post

Originally Posted by rain4u

I have to tell you that I only need help with how macro chooses where to insert formula.

Crystal clear. Right. OK lets move on then.

Originally Posted by rain4u

Now as far as it goes as coding for this exercise I would like to do it the relative way. The reason why it It needs to be relative is as I want to reduce the amount of lines.
I would like have two macros. I would then use these macros within other macros via Application.Run method.


As I'm rubbish with coding can you help me to formulate the following codes Please help me with these two macros and I will be happy man. now i will write it in general terms. Please change it so it will work on excel.


Sub hlookup()

ActiveCell.FormulaR1C1 = "=HLOOKUP(R7C4:R27C4,R32C4:R33C24,2,FALSE)"

auto fill from row 7 to row 27
Clear clear values active column, rows 13 to 17 and rows 23 to 27
End sub

and the second macro something along these lines

Sub previous_day_info()
Current active column
select -1 column (i.e. move one column left)

the following formula
"=HLOOKUP(R7C4:R27C4,R32C4:R33C24,2,FALSE)"
applied to cells on rows 13 to 17 and rows 23 to 27

Now I could use these two macros above (that is if someone can help me to formulate them properly).

on Monday I could use the following code
Private Sub Monday()
Range("E7").Select
Application.Run "hlookup"
End Sub

On rest of the week I could use these formulas on their respective day

Private Sub Tuesday()
    Range("F7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub

Private Sub Wednesday()
    Range("G7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub

Private Sub Thursday()
    Range("H7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub

Private Sub Friday()
    Range("I7").Select
    Application.Run "hlookup"
    Application.Run "previous_day_info"
End Sub

I will post updated xls with the the code I have so far. its and very ugly code that takes more than 350 lines
For a what it does, its ridiculous. I bet the whole thing could be achieved with 30 lines.
Anyway run the macros one by one starting with
F2_tue
F3_wed
F4_thurs
F5_fr[/CODE]

Ignore F1_mon. Don't start with that. Its just is so much more clearer to undeerstand if you start Tuesday onwards. Also ignore #REF values. Its because of define names, they don't exist in the example file.



Can anyone please help me.

Cheers

Rain

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Also please do not quote unnecessarily, it clogs the forum - Thx

Thank you, but add your code tags please...