Copy If in row below

[stacia]

When Department changes I need to insert the ADDRESS below the
Account-Institution Business Office. For instance if department = 883, go
to row below Account-Instittion business office and if = blank get address abc

123 Company
500 Thompson Rd.
Detroit, MI ending 02/13/10
----------------------------------------------------------------
Account-Institution Business Office:

DEPT EMP.NBR EMPLOYEE NAME ADDRESS

883 27903 ROBERSON, JOE abc
883 119245 MUSIL, TODD E abc

883 Total
-----------------------------------------------------------------
Account-Institution Business Office:

DEPT EMP.NBR EMPLOYEE NAME ADDRESS

887 277878 MUMTAZ, MALIKAH def
887 352122 WHITING, BOBBI JO def

887 Total
-------------------------------------------------------
Account-Institution Business Office:

DEPT EMP.NBR EMPLOYEE NAME ADDRESS

893 197805 HOEL, JEFFREY S ghi
893 202426 YORK, TODD A ghi

893 Total
----------------------------------------------------------
Account-Institution Business Office:

DEPT EMP.NBR EMPLOYEE NAME ADDRESS

934 111806 FELDMANN, Mike jkl
934 137215 JAMES, MARK jkl

934 Total

 

[Bernie Deitrick]

Stacia,

Try this.

Select the column (A?) with the Dept. numbers. Then use Edit / Go To...
Special Blanks OK, The active cell should be the first blank cell in
that column. Then type

=IF(

and press the up arrow key once, then type

="Account-Institution Business Office:",

and select the cell with the abc address three rows below the cell where
your are entering the formula, then finish typing

,"")

and press Ctrl-Enter.

Your final formula in the first cell should look like

=IF(A3="Account-Institution Business Office:",D7,"")

You can then copy column A and paste-special values to remove the formulas.

HTH,
Bernie
MS Excel MVP

 

[stacia]

Thanks, but the rows will be different each time I use this macro. I need to
do this for each page, without referencing actual lines as they will never be
consistant.
ideas?

 

[Bernie Deitrick]

It should work if you use the FormulaR1C1 property and the formula is in
R1C1 style - as long as the empty cell is just below the

Account-Institution Business Office:

and the address is a set number of rows down. Try recording the steps as
described, and post the resulting macro.

HTH,
Bernie
MS Excel MVP

 

[stacia]

Thanks again, but the address is never going to be a set number of rows. Any
other suggestions?

 

[stacia]

Why won't this work?

Sub b9vlookup()
Dim theRange As Range
Dim lastrow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastrow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastrow To firstRow Step -1
If InStr(1, Cells(x, 1), "Total") > 0 Then
Cells(x + 2).FormulaR1C1 = "=VLOOKUP(R[3]C,Address!R[-4]C:R[15]C[3],2)"


End If
Next
End Sub

 

[Bernie Deitrick]

Stacia,

Cells should be Cells(row,col) not just Cells(row)

Assuming you want the formula in column A (the , 1) part of Cells(), try

Cells(x + 2, 1).FormulaR1C1 = "=VLOOKUP(R[3]C,Address!R3C1:R22C4,2,FALSE)"

instead of what you had.

HTH,
Bernie
MS Excel MVP

Why won't this work?

Sub b9vlookup()
Dim theRange As Range
Dim lastrow&, firstRow&, x&
Set theRange = ActiveSheet.UsedRange
lastrow = theRange.Cells(theRange.Cells.Count).Row
firstRow = theRange.Cells(1).Row
For x = lastrow To firstRow Step -1
If InStr(1, Cells(x, 1), "Total") > 0 Then
Cells(x + 2).FormulaR1C1 = "=VLOOKUP(R[3]C,Address!R[-4]C:R[15]C[3],2)"


End If
Next
End Sub

--
Stacia

Thanks again, but the address is never going to be a set number of rows.
Any
other suggestions?