Announcement

Collapse
No announcement yet.

Excel Query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Query

    In a cell I have a formula like the below

    =IF('BRN - Reading'!$P40="No",'BRN - Reading'!R44-'BRN - Reading'!R39,'BRN - Reading'!R44-(('BRN - Reading'!$Q40/12)*('BRN - Reading'!$K40)*(1+0)))

    The cell below contains;

    =IF('BRN - Reading'!$P46="No",'BRN - Reading'!R50-'BRN - Reading'!R45,'BRN - Reading'!R50-(('BRN - Reading'!$Q46/12)*('BRN - Reading'!$K46)*(1+0)))

    All I need to do is copy the formula down a few times but add 6 to the cell references each time, anyone know how I can do this (other then manually), Excel just like to add on 1.

    I can create a few manually and then select a few cells to copy and paste, Excel will keep the difference of 6 between them but not the cells outside the selection.


  • #2
    You have no idea how often I have wanted to do something like that. Aside from generating formulas with VBA, I have only found two workarounds, both have their pitfalls:
    1. Use the OFFSET function, e.g. (assuming your formula is in the B column, you can spare the A column and the first is at row 40): "=IF(OFFSET('BRN - Reading'!$P$40,(A40-1)*6,0,1,1)="No",OFFSET('BRN - Reading'!R$44,(A40-1)*6,0,1,1)-OFFSET('BRN - Reading'!R$39,(A40-1)*6),OFFSET('BRN - Reading'!R$44,(A40-1)*6,0,1,1)-((OFFSET('BRN - Reading'!$Q$40,(A40-1)*6,0,1,1)/12)*(OFFSET('BRN - Reading'!$K$40,(A40-1)*6,0,1,1))*(1+0)))" - Which is terrible but works and the use of OFFSET can be very helpfull in larger structured workbooks.
    2. Copy the row and paste it six rows down and again and again, then cut and paste from below upwards. Works, terribel to do though.

    On the other hand, I make fewer mistakes with either of these solutions than by manually changing 6 references in each formula in god knows how many formulas...your call...

    If you ever do find a way to do it, please tell me...
    Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
    [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

    Comment


    • #3
      Cheers Umf, glad I'm not the only one suffering.

      I was think about number 2 but I would go mad by the end of the day, thousands of cells to do this for.

      1 is a very good idea but unfortunatly I don't have any spare columns at the moment, 12 colums next to each other with the same type of formulas in them.

      I've been looking around and I'm going to give INDIRECT() a go, if that doesn't work I'll go with your first suggestion.

      INDIRECT() isn't the best option but it looks like it will be more manageble, for this spreadsheet anyway.

      Comment


      • #4
        try this: This is very quick and ugly. Change the 5 in the for loop to the number of formulas you need. You can either copy n cut from the immediate window, or let me know and I can make it insert into your activeworksheet. Let me know what column you want though.

        Code:
        Sub dbdq()
        ' PURPOSE:
        '   To add 6 to each row for the following formula
        '   =IF('BRN - Reading'!$P40="No",'BRN - Reading'!R44-'BRN - Reading'!R39,'BRN - Reading'!R44-(('BRN - Reading'!$Q40/12)*('BRN - Reading'!$K40)*(1+0)))
        '   =IF('BRN - Reading'!$P46="No",'BRN - Reading'!R50-'BRN - Reading'!R45,'BRN - Reading'!R50-(('BRN - Reading'!$Q46/12)*('BRN - Reading'!$K46)*(1+0)))
        
        Dim strFormula As String
        Dim intP40, intR44, intR39, intQ40, intK40 As Integer
        Dim i As Integer
            intP40 = 40
            intR44 = 44
            intR39 = 39
            intQ40 = 40
            intK40 = 40
            
            For i = 0 To 5
                strFormula = "=IF('BRN - Reading'!$P" & intP40 _
                    & "=""No"",'BRN - Reading'!R" & intR44 _
                    & "-'BRN - Reading'!R" & intR39 _
                    & ",'BRN - Reading'!R" & intR44 _
                    & "-(('BRN - Reading'!$Q" & intQ40 _
                    & "/12)*('BRN - Reading'!$K" & intK40 _
                    & ")*(1+0)))"
                Debug.Print strFormula
                intP40 = intP40 + 6
                intR44 = intR44 + 6
                intR39 = intR39 + 6
                intQ40 = intQ40 + 6
                intK40 = intK40 + 6
            Next i
            
        End Sub

        Comment


        • #5
          Yep, that's the VBA route alright. If it's about thousands of formulas, that'd be the way to go. I.d not print them to the debug window but put a for next loop in them and use something like

          InitialiseFormulas - As in post above
          With range("OneRowAboveAndOneColumnBeforeWhereYouWantTh em")

          For i = 1 to HowManyFormulasToPutRowsIn
          For j = 1 to HowManyFormulasPerRow

          .offset(i,j).formula = what's printed in debug window

          Next j
          update formulas for next row
          Next i
          End With

          Edit 2: I had not read the text of Ali. Go his route indeed.
          Last edited by Umfriend; 31 August 2005, 04:34.
          Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
          [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

          Comment


          • #6
            Ali

            That is absoluletly stunning, I should really start to learn VBA.

            If you could tell me how to modify it to write the formula to the current cell in excel that would be cool otherwise I'll just copy and paste seeing as all the hardwork is already done.

            Comment


            • #7
              A two line modification below (and added some comments).

              Select the first cell you want the formula in. Then paste the code below into your VBA 'ThisworkBook' or similar (F11 to open VBE). Now make sure your cursor is inside the sub, and hit F5

              What 'should' happen is that the formula will insert into the cell you have selected, then the cell below it, etc until it falls out of the loop (5 times at the moment).

              Good luck.

              Ali

              Code:
              Sub dbdq()
              ' PURPOSE:
              '   To add 6 to each row for the following formula
              '   =IF('BRN - Reading'!$P40="No",'BRN - Reading'!R44-'BRN - Reading'!R39,'BRN - Reading'!R44-(('BRN - Reading'!$Q40/12)*('BRN - Reading'!$K40)*(1+0)))
              '   =IF('BRN - Reading'!$P46="No",'BRN - Reading'!R50-'BRN - Reading'!R45,'BRN - Reading'!R50-(('BRN - Reading'!$Q46/12)*('BRN - Reading'!$K46)*(1+0)))
              '   Starts at the selected cell, and moves down a set number of times
              
              Dim strFormula As String
              Dim intP40, intR44, intR39, intQ40, intK40 As Integer
              Dim i As Integer
                  ' set up integers for first location
                  intP40 = 40
                  intR44 = 44
                  intR39 = 39
                  intQ40 = 40
                  intK40 = 40
                  
                  ' change the 5 to however many formulas you want below
                  For i = 0 To 5
                      ' build the formula
                      strFormula = "=IF('BRN - Reading'!$P" & intP40 _
                          & "=""No"",'BRN - Reading'!R" & intR44 _
                          & "-'BRN - Reading'!R" & intR39 _
                          & ",'BRN - Reading'!R" & intR44 _
                          & "-(('BRN - Reading'!$Q" & intQ40 _
                          & "/12)*('BRN - Reading'!$K" & intK40 _
                          & ")*(1+0)))"
                      
                      ' insert the formula in the active cell
                      ActiveCell.Formula = strFormula
                      
                      ' move down one row for next loop
                      ActiveCell.Offset(1, 0).Select
                      
                      ' Setup integers for next loop
                      intP40 = intP40 + 6
                      intR44 = intR44 + 6
                      intR39 = intR39 + 6
                      intQ40 = intQ40 + 6
                      intK40 = intK40 + 6
                  Next i
                  
              End Sub

              Comment


              • #8
                You are such a lifesaver, this is brilliant,

                It works fantastically, I've modified it a little, need to refer to other col later on

                Code:
                Sub dbdq()
                Dim strFormula As String
                Dim strSheet As String
                	strSheet = "BRN_Reading"
                Dim int_1, int_2, int_3 As Integer
                	int_1 = 10
                	int_2 = 14
                	int_3 = 9
                	
                Dim col_1, col2, col_3 As String
                	col_1 = "R"
                	col_2 = "Q"
                	col_3 = "K"
                	
                Dim i As Integer
                Dim j As Integer
                	j = 3
                	For i = 0 To j
                		strFormula = "=IF(" & strSheet & "!$P" & int_1 _
                			& "=""No""," & strSheet & "!" & col_1 & int_2 _
                			& "-" & strSheet & "!" & col_1 & int_3 _
                			& "," & strSheet & "!" & col_1 & int_2 _
                			& "-((" & strSheet & "!$" & col_2 & int_1 _
                			& "/12)*(" & strSheet & "!$" & col_3 & int_1 _
                			& ")*(1+0)))"
                			
                		' insert the formula in the active cell
                		ActiveCell.Formula = strFormula
                		
                		' move down one row for next loop
                		ActiveCell.Offset(1, 0).Select
                		' Set up for next cell
                		int_1 = int_1 + 6
                		int_2 = int_2 + 6
                		int_3 = int_3 + 6
                	Next i
                	
                End Sub
                Edit: wrong code
                Last edited by dbdg; 31 August 2005, 06:12. Reason: wrong code

                Comment


                • #9
                  looks like you have it sorted then. good stuff.

                  Comment


                  • #10
                    Ali

                    Rather the having J and the column variables in the macro I don't suppose you know how to get excel to bring up a prompt and allow me to enter the values I want to use.

                    Do you know of any good tutorials?

                    Edit: Worked it out, well sort of, Application.InputBox("Enter Column Reference 1")
                    Last edited by dbdg; 31 August 2005, 06:17.

                    Comment


                    • #11
                      IMO, if you already have some programming experience, the Excel help files on VBA are pretty darn good. More so with the older versions though. Most VBA books I've seen focus on user interface programming mainly.
                      Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
                      [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

                      Comment


                      • #12
                        I've added in an if statement for a different version of the formula now and everything appears to to be going ok, the manual was very helpful.

                        I've got a pretty decent understanding of php and am just starting to explore oop more thoroughly so we'll see how I go.

                        Again, cheers for all you help guys!!

                        Comment


                        • #13
                          sorry, been out in the sun.

                          To get a variable from the user, use something like this:

                          strReturnValue = inputbox("Enter something here")

                          for tutorials, the only thing to do is look at code and try to understand it (in my honest opinion). Try places like:
                          Get answers to your Programming questions from our top industry experts by using Experts Exchange's platform to find the best Programming help & solutions. Click to learn more!


                          Be aware that they have pop-ups now though (bastards).

                          for general Excel stuff, Ill copy a few in from my bookmarks:






                          Ali

                          PS, I missed your edit.

                          Comment


                          • #14
                            Cheers for the links Ali,

                            I beefed it up even more, I figured since it can do a variable number of rows with a nested if I could do 12 columns at the same time.

                            Code:
                            Sub budForm()
                             
                            'Formula
                            Dim strFormula As String
                             
                            'Worksheet
                            Dim strSheet As String
                            strSheet = Application.InputBox("Enter Worksheet Name")
                             
                            'Initial Row References
                            Dim int_1, int_2, int_3 As Integer
                            int_1 = 10
                            int_2 = 14
                            int_3 = 9
                             
                            'Initial Column References
                            Dim col_1, col2, col_3 As String
                            col_1 = "R"
                             
                            'Number of Rows
                            Dim i As Integer
                            Dim j As Integer
                            j = Application.InputBox("Enter Number of Rows")
                            j = j - 1
                             
                            'Twelve Columns
                            Dim r As Integer
                             
                            For i = 0 To j
                             
                            For r = 0 To 11
                             
                            If r = 0 Then
                            col_1 = "R"
                            ElseIf r = 1 Then
                            col_1 = "S"
                            ElseIf r = 2 Then
                            col_1 = "T"
                            ElseIf r = 3 Then
                            col_1 = "U"
                            ElseIf r = 4 Then
                            col_1 = "V"
                            ElseIf r = 5 Then
                            col_1 = "W"
                            ElseIf r = 6 Then
                            col_1 = "X"
                            ElseIf r = 7 Then
                            col_1 = "Y"
                            ElseIf r = 8 Then
                            col_1 = "Z"
                            ElseIf r = 9 Then
                            col_1 = "AA"
                            ElseIf r = 10 Then
                            col_1 = "AB"
                            ElseIf r = 11 Then
                            col_1 = "AC"
                            End If
                             
                            strFormula = "=IF(" & strSheet & "!$P" & int_1 _
                            & "=""No""," & strSheet & "!" & col_1 & int_2 _
                            & "-" & strSheet & "!" & col_1 & int_3 _
                            & "," & strSheet & "!" & col_1 & int_2 _
                            & "-((" & strSheet & "!$Q" & int_1 _
                            & "/12)*(" & strSheet & "!$Q" & int_1 _
                            & ")*(1+0)))"
                             
                            ' insert the formula in the active cell
                            ActiveCell.Formula = strFormula
                             
                            ' move down one row for next loop
                            ActiveCell.Offset(0, 1).Select
                             
                            Next r
                             
                            ActiveCell.Offset(1, -12).Select
                            int_1 = int_1 + 6
                            int_2 = int_2 + 6
                            int_3 = int_3 + 6
                             
                            Next i
                             
                            End Sub
                            I'd normally use a switch statement (php) rather then a stupidly long elseif but I don't know how to do that in VBA yet.

                            Comment


                            • #15
                              switch statement in VBA:

                              select case r
                              case 1
                              'do something
                              case 2
                              ' do something else
                              case else
                              ' if it falls over
                              end select

                              Not checked in code. off home for the day.

                              Ali

                              Comment

                              Working...
                              X