Copy If in row below

  • Thread starter Thread starter stacia
  • Start date Start date
S

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
 
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
 
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?
 
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
 
Thanks again, but the address is never going to be a set number of rows. Any
other suggestions?
 
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,

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




stacia said:
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


stacia said:
Thanks again, but the address is never going to be a set number of rows.
Any
other suggestions?
Click to expand...
Click to expand...
 
Back
Top