+ Reply to Thread
Results 1 to 13 of 13

Macro to insert formulas to certain cells respective of cell selected

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    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
    Attached Files Attached Files
    Last edited by rain4u; 04-21-2011 at 10:52 PM. Reason: solved

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,033

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

    Hey Rain,

    What does the attached have to do with your problem?

    Did you attach the correct file?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    Hi Marvin
    Yes it is the correct file. Please look at the sheet " Before". Please look cells E13 to E15 and E23 to E25. As you can see they are empty. As per my request I would like to have a macro that would place some formulas in them.

    Quote Originally Posted by rain4u View Post
    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.
    Quote Originally Posted by rain4u View Post
    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
    Quote Originally Posted by rain4u View Post
    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.

    Its quite hard to explain it any shorter and clearer way. Can you have a second look?

    Cheers
    Rain

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,033

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

    Try this in E14 and pull it down for the other blank cells,
    Please Login or Register  to view this content.
    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.

  5. #5
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    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:
    Please Login or Register  to view this content.
    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
    Last edited by rain4u; 04-21-2011 at 01:24 AM.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,033

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

    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.

  7. #7
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    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
    Quote Originally Posted by rain4u View Post
    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.

    Quote Originally Posted by rain4u View Post
    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
    Please Login or Register  to view this content.
    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
    Attached Files Attached Files
    Last edited by rain4u; 04-21-2011 at 03:19 PM.

  8. #8
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

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

    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

  9. #9
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

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

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

  10. #10
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    I do apologize

    Cheers
    Rain

  11. #11
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

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

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

  12. #12
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    Done!

    Cheers
    Rain

  13. #13
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

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

    I figured this out myself

    here is the code for Tuesday to Friday
    Please Login or Register  to view this content.
    Thank you Marvin for trying to help me

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1