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. Lookup table is on
sheet 2 for OFFICE and SHOP

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 ADDRESS
883 Joe's
883 Joe's

883 Total $- 0

----------------------------------------------------------
Account-Institution Business Office: Shop Report


DEPT ADDRESS

887 dick's ink
887 dick's ink

887 Total $- 0

----------------------------------------------------------
Account-Institution Business Office: Shop Report

DEPT ADDRESS
893 RGCI
893 RGCI
893 RGCI

Grand Total $- 0


AFTER

BCE Pay Period 05 ending 02/27/10

Account-Institution Business Office: Shop Report
JOE'S FURNITURE - LACROSSE 883

DEPT ADDRESS

883 Joe's
883 Joe's
883 Total $- 0

---------------------------------------------------------------
Account-Institution Business Office: Shop Report
DICK AND JANE'S PAPER & INK 887

DEPT ADDRESS

887 dick's ink
887 dick's ink
887 Total

-----------------------------------------------------------------
Account-Institution Business Office: Shop Report
REDGRANITE GRAND CAPITAL INC 893

DEPT ADDRESS

893 RGCI
893 RGCI
893 RGCI
Grand Total $- 2689
 
J

JLatham

Stacia,
I think you're not doing what you want to do in setting your range and then
selecting it. And selecting really isn't required, but we'll leave that
aside.

Endrow = Range("A65536").End(xlUp).Row
will give you the last used row in column A on the active sheet. But you
can rewrite it to be more 'generic':
Endrow = Range("A" & Rows.Count).End(xlUp).Row

But that's not the real problem, it's the next line where you Set MyRange:
Set MyRange= Range("A" & Endrow)
is only going to set MyRange to reference ONE CELL: the one in column A on
the row determined in the previous line of code.

I think what you want to do is this:
Set MyRange = Range("A1:A" & Endrow)
That would get MyRange referencing a column of cells from A1 (change if you
want to start at another row) on down to the last row found previously. So
the result might be a reference to A1:A943 on time, and after more data is
entered it might be A1:A1043 the next.

Also, you can probably delete the
ActiveWindow.SmallScroll down:=4
, or not but it looks like a left over artifact from a recorded macro?


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. Lookup table is on
sheet 2 for OFFICE and SHOP

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 ADDRESS
883 Joe's
883 Joe's

883 Total $- 0

----------------------------------------------------------
Account-Institution Business Office: Shop Report


DEPT ADDRESS

887 dick's ink
887 dick's ink

887 Total $- 0

----------------------------------------------------------
Account-Institution Business Office: Shop Report

DEPT ADDRESS
893 RGCI
893 RGCI
893 RGCI

Grand Total $- 0


AFTER

BCE Pay Period 05 ending 02/27/10

Account-Institution Business Office: Shop Report
JOE'S FURNITURE - LACROSSE 883

DEPT ADDRESS

883 Joe's
883 Joe's
883 Total $- 0

---------------------------------------------------------------
Account-Institution Business Office: Shop Report
DICK AND JANE'S PAPER & INK 887

DEPT ADDRESS

887 dick's ink
887 dick's ink
887 Total

-----------------------------------------------------------------
Account-Institution Business Office: Shop Report
REDGRANITE GRAND CAPITAL INC 893

DEPT ADDRESS

893 RGCI
893 RGCI
893 RGCI
Grand Total $- 2689
Click to expand...
 
S

stacia

you are correct, that was my attempt at a recorded macro. I have tried this
so many times I forgot which was the last I tried.

the following is a macro i tried to do, but at that time only concentrated
on the account business office. I will still need the dept number under the
"SHOP"

sub shop()
Dim theRange As Range
Dim lastrow&, firstrow&, x&
Worksheets("Payroll").Active
ActiveSheet.UsedRange.Select
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
Worksheets("Payroll").Active
With ActiveCell.Cells (x + 2, 1)
FormulaR1C1 =
"=VLOOKUP (Data!R[-5]C:R[410]C[6],Address!R2C1:R21C24,2,FALSE)"
End if
Next
End Sub
--
Stacia


JLatham said:
Stacia,
I think you're not doing what you want to do in setting your range and then
selecting it. And selecting really isn't required, but we'll leave that
aside.

Endrow = Range("A65536").End(xlUp).Row
will give you the last used row in column A on the active sheet. But you
can rewrite it to be more 'generic':
Endrow = Range("A" & Rows.Count).End(xlUp).Row

But that's not the real problem, it's the next line where you Set MyRange:
Set MyRange= Range("A" & Endrow)
is only going to set MyRange to reference ONE CELL: the one in column A on
the row determined in the previous line of code.

I think what you want to do is this:
Set MyRange = Range("A1:A" & Endrow)
That would get MyRange referencing a column of cells from A1 (change if you
want to start at another row) on down to the last row found previously. So
the result might be a reference to A1:A943 on time, and after more data is
entered it might be A1:A1043 the next.

Also, you can probably delete the
ActiveWindow.SmallScroll down:=4
, or not but it looks like a left over artifact from a recorded macro?


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. Lookup table is on
sheet 2 for OFFICE and SHOP

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 ADDRESS
883 Joe's
883 Joe's

883 Total $- 0

----------------------------------------------------------
Account-Institution Business Office: Shop Report


DEPT ADDRESS

887 dick's ink
887 dick's ink

887 Total $- 0

----------------------------------------------------------
Account-Institution Business Office: Shop Report

DEPT ADDRESS
893 RGCI
893 RGCI
893 RGCI

Grand Total $- 0


AFTER

BCE Pay Period 05 ending 02/27/10

Account-Institution Business Office: Shop Report
JOE'S FURNITURE - LACROSSE 883

DEPT ADDRESS

883 Joe's
883 Joe's
883 Total $- 0

---------------------------------------------------------------
Account-Institution Business Office: Shop Report
DICK AND JANE'S PAPER & INK 887

DEPT ADDRESS

887 dick's ink
887 dick's ink
887 Total

-----------------------------------------------------------------
Account-Institution Business Office: Shop Report
REDGRANITE GRAND CAPITAL INC 893

DEPT ADDRESS

893 RGCI
893 RGCI
893 RGCI
Grand Total $- 2689
Click to expand...
Click to expand...
 

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

vlookup 1
VBLOOKUP BY DEPT 3
Copy If in row below 6
HELP If range equals 2
Copy and Paste based on criteria 1
Copy and Paste based on criteria 1
Code lagging with LastRow 2
Problems totalling subreport data on main report (A-2002) 2

Top