+ Reply to Thread
Results 1 to 11 of 11

VBA to enter various formulas every 10 rows and show dynamic formulas not values

  1. #1
    Registered User
    Join Date
    03-29-2013
    Location
    new jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Smile VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Hi,

    I'm trying to multiple macros in one file that will insert a formula and show not just the result but the formula when double clicking on the cells.
    Once I have the formulas I want the macro to copy the formulas and paste it onto other lines (erery 10 lines)

    I have added the file to explain better.

    The formulas should appear between T6 : CQ(last row)
    In my file I have entered the formulas I want in the first set of cells and highlighted each formula in a different color.

    For example:
    * I want cell T12 to show =T6-T7 then I want to copy this formula in cell T22 as = T16-T17 and so on until the last row. (My example only has a few row but I will add many more lines later on)

    * I want cell U12 to show: =U6-U7+SUM(U8:U10). Then I want to copy this formula to each cell to the right (sort of like dragging a formula to the right), and then I want each row below to show the same combination ( The first cell T12 as =T6-T7 and all other cells as =U6-U7+SUM(U8:U10). (row 22, row32, row42 and so on)

    Finally I want to enter a formula (the formula already in the first set of numbers - highlighted) in the first cell of each other row (Ex: T13, T14, T15 and U6) and then copy that formula across the row. Then copy in each 10 lines down until the end.

    It's hard to read but when you open the file the question is much easier to follow.


    Thank you so much for the help!
    Attached Files Attached Files

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Hi faby2203

    Welcome to the Forum!

    I don't know...you tell me...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jaslake; 03-30-2013 at 02:45 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    03-29-2013
    Location
    new jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    HI jaslake, the coding works wonderful in the file ...Thank you!

    I just have one final question. The file I uploaded is not the original file of course. If any of the values between columns A and M change or the headers between columns N and Q change the macro should still work right?
    I tried running it in the original file but I got an error Run-time error 91. Object variable or with block variable not set. The debug points at LR and LC.

    Thanks

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Hi faby2203

    Regarding this
    If any of the values between columns A and M change or the headers between columns N and Q change the macro should still work right?
    Correct, these Columns are not integral to the Code. The Structure from Columns S and CQ MUST remain the same...the Headings can change but the Data required MUST remain the same.

    Regarding this
    Run-time error 91. Object variable or with block variable not set. The debug points at LR and LC.
    As you can see in the Code LC has been commented out...it's not used in the Code. However, LR is used in the Code a couple of times.

    I can't duplicate this error
    Run-time error 91. Object variable or with block variable not set
    so you'll need to show me which line of Code throws the error message.

  5. #5
    Registered User
    Join Date
    03-29-2013
    Location
    new jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    I figured out what's causing the error. Thank you for all the help Jaslake =)

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    You're welcome...glad I could help.

  7. #7
    Registered User
    Join Date
    03-29-2013
    Location
    new jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Hi Jaslake,

    I'm hoping you can help me take this file a step further.
    I'd like to add a conditional formatting based on the values of variable cells compared to the values of other cells.

    This shoud be true for every line with WOS. I'm using my previous file, for example row 13, 23 and so on)
    I want each value on that row starting from column T to be compared to cells R6 and R12.
    The formula should say if cell< R6 = background color should be red, cell> R6 and less than R6*0.5 = background color should be orange.
    if R6*0.5>cell and < R12 = background color should be green, if cell> R12 = background color should be blue.

    Thia set must repeat on every set below and therefore the constants R6 and R12 will chnage to R6 and R22 and so on.

    I started by adding the following to this part of the macro but I'm getting an error;

    Case "Inventory coverage (WOS)"
    For Each cel In .Range("S2:S" & LR).SpecialCells(xlCellTypeVisible)
    .Range("T" & cel.Row).Formula = "=T" & cel.Row - 1 & "/AVERAGE(U" & cel.Row - 6 & ":AF" & cel.Row - 6 & ")"
    .Range("T" & cel.Row).AutoFill Destination:=.Range(.Cells(cel.Row, "T"), .Cells(cel.Row, "CQ"))
    .Range("T" & cel.Row).FormatConditions.Delete
    .Range("T" & cel.Row).FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, formula1:= "=IF(cell< (R" &cel.Row - 3 & "),.Interior.Color = rgbred, 0)" )


    Next cel

    Please let me know if I'm in the right track.

    Thanks!

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Hi faby2203

    Turn on the Macro Recorder and Record the Steps you intend for this line of Code
    Please Login or Register  to view this content.
    Post the recorded Macro and I'll try to convert it for you.

  9. #9
    Registered User
    Join Date
    03-29-2013
    Location
    new jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Hi Jaslake,

    This is the recording for the first line. I want this for every ten lines. I'm attaching the excel file with the macro.

    Thank you so much!!


    Sub Conditional_Formatting()
    '
    ' Conditional_Formatting Macro
    '

    '
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
    Formula1:="=$R$10"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=$R$10", Formula2:="=$R$10+(0.5*$R$10)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=$R$10+(0.5*$R$10)", Formula2:="=$R$12"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 5287936
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=$R$12"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 12611584
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    End Sub
    Attached Files Attached Files

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Hi faby2203

    You really need to use Code Tags ANY time you post Code on the Forum...see the Forum Rules.

    Now, I don't know...see if this Code does as you require...I'd have to guess it doesn't need to be this verbose but, if it works, what the Hey.

    I get Colors (I'm Color Blind so they might not be the Colors you want and they may not be where you want). Play with it...let me know.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-29-2013
    Location
    new jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to enter various formulas every 10 rows and show dynamic formulas not values

    Sorry I'm still pretty new to this. I will try it and let you know. thank you!

+ 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