S
stacia
I need help with this macro - does not work. What am I doing wrong? I need
help with this macro - does not work. I listed an example of before and one
as after to show you the results I want. Additional note: everytime i use
this macro, the amount of rows will never be the same.
Sub B9SHOP()
'
' SHOP Macro
' Macro recorded 2/25/2010 by hintzSA
'
'
Dim MyRange As Range
Dim MyCell As Range
Dim Endrow As Integer
Endrow = Range("A65536").End(xlUp).Row
Set MyRange = Range("A" & Endrow)
MyRange.Select
For Each MyCell In MyRange
If MyCell.value = "Total" Then
Cells.Find(What:="Inmate Account-Institution Business Office:",
After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
ActiveWindow.SmallScroll down:=4
ActiveCell.FormulaR1C1 =
"=VLOOKUP(Payroll!R[3]C,Address!R2C1:R21C4,2,FALSE)"
End If
Next
End Sub
BEFORE:
BCE Pay Period 05 ending 02/27/10
Account-Institution Business Office: Shop Report
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
883 27903 ROBERSON, JOE Joe's x x x
883 119245 MUSIL, TODD E Joe's x x x
883 Total $-
------------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
887 277878 MUMTAZ, MALIKAH dick's ink x x x
887 352122 WHITING, BOBBI JO dick's ink x x x
887 Total $-
------------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
893 197805 HOEL, JEFFREY S RGCI x x x
893 202426 YORK, TODD A RGCI x x x
893 299561 OATES, RICHARD RGCI x x x
Grand Total $- 0
AFTER
BCE Pay Period 05 ending 02/27/10
Account-Institution Business Office: Shop Report
JOE'S FURNITURE - LACROSSE 883
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
883 27903 ROBERSON, JOE Joe's x x x
883 119245 MUSIL, TODD E Joe's x x x
883 Total $-
-----------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
DICK AND JANE'S PAPER & INK - MADISON WISCONSIN 887
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
887 277878 MUMTAZ, MALIKAH dick's ink x x x
887 352122 WHITING, BOBBI JO dick's ink x x x
887 Total
------------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
REDGRANITE GRAND CAPITAL INC - REDGRANITE WISC 893
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
893 197805 HOEL, JEFFREY S RGCI x x x
893 202426 YORK, TODD A RGCI x x x
893 299561 OATES, RICHARD RGCI x x x
Grand Total $- 2689
help with this macro - does not work. I listed an example of before and one
as after to show you the results I want. Additional note: everytime i use
this macro, the amount of rows will never be the same.
Sub B9SHOP()
'
' SHOP Macro
' Macro recorded 2/25/2010 by hintzSA
'
'
Dim MyRange As Range
Dim MyCell As Range
Dim Endrow As Integer
Endrow = Range("A65536").End(xlUp).Row
Set MyRange = Range("A" & Endrow)
MyRange.Select
For Each MyCell In MyRange
If MyCell.value = "Total" Then
Cells.Find(What:="Inmate Account-Institution Business Office:",
After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
ActiveWindow.SmallScroll down:=4
ActiveCell.FormulaR1C1 =
"=VLOOKUP(Payroll!R[3]C,Address!R2C1:R21C4,2,FALSE)"
End If
Next
End Sub
BEFORE:
BCE Pay Period 05 ending 02/27/10
Account-Institution Business Office: Shop Report
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
883 27903 ROBERSON, JOE Joe's x x x
883 119245 MUSIL, TODD E Joe's x x x
883 Total $-
------------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
887 277878 MUMTAZ, MALIKAH dick's ink x x x
887 352122 WHITING, BOBBI JO dick's ink x x x
887 Total $-
------------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
893 197805 HOEL, JEFFREY S RGCI x x x
893 202426 YORK, TODD A RGCI x x x
893 299561 OATES, RICHARD RGCI x x x
Grand Total $- 0
AFTER
BCE Pay Period 05 ending 02/27/10
Account-Institution Business Office: Shop Report
JOE'S FURNITURE - LACROSSE 883
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
883 27903 ROBERSON, JOE Joe's x x x
883 119245 MUSIL, TODD E Joe's x x x
883 Total $-
-----------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
DICK AND JANE'S PAPER & INK - MADISON WISCONSIN 887
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
887 277878 MUMTAZ, MALIKAH dick's ink x x x
887 352122 WHITING, BOBBI JO dick's ink x x x
887 Total
------------------------------------------------------------------------------------------------
Account-Institution Business Office: Shop Report
REDGRANITE GRAND CAPITAL INC - REDGRANITE WISC 893
DEPT EMP.NBR EMPLOYEE NAME ADDRESS EARN. RATE CNT
893 197805 HOEL, JEFFREY S RGCI x x x
893 202426 YORK, TODD A RGCI x x x
893 299561 OATES, RICHARD RGCI x x x
Grand Total $- 2689