Excel Query

[dbdg]

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.

[Umfriend]

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...

[dbdg]

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.

[Ali]

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.

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

[Umfriend]

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.

[dbdg]

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.

[Ali]

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

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

[dbdg]

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

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

[Ali]

looks like you have it sorted then. good stuff.

[dbdg]

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")

[Umfriend]

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.

[dbdg]

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!!

[Ali]

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:

http://www.experts-exchange.com/Programming/

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

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

http://www.cpearson.com/excel/ExcelPages.htm

http://www.vba-programmer.com/

http://groups.google.com/group/microsoft.public.excel.programming?hl=en

http://www.instantpages.ltd.uk/banner.html

http://www.informit.com/articles/article.asp?p=366892&seqNum=3&rl=1

Ali

PS, I missed your edit.

[dbdg]

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.

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.

[Ali]

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