vlookup

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
 
S

stacia

PLEASE DISREGARD
--
Stacia


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top