Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Asstance need
Good Day,
I'm trying to write a macro and i have never used this before so i'm teaching my self as i go along This is what i have so far but the problem is with the "Reported to Payroll". The situation is the way i have it now is good if everyone works everyday and and is never absent, but we all know how that goes so therefore "Reported to Payroll" will keep moving around and thats no problem it's just that i need the 8 cells next to it and the values in each of those cells are always changing so i cant use the FIND command. The way i have it working now is it will find a person's name copy to the other spreadsheet and then it will find that persons "Report to Payroll" because it's underneath(its about 3 columns over and 35 down) their name and I have over 300 employees to do this for so is there a formula that i can use that will always find "Reported To Payroll" and the 8 cells next to no matter where it is on the spreadsheet (keep in mind that Reported To Payroll appears over 300 times on the spreadsheet) (this is how it looks when i dwnld the report) "Name" ... ... ... ... "Report to payroll" x x x x x x x x (this is what i have now) Cells.Find(What:="name", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Windows("TMSPhatomReport.xls").Activate Windows("TMSDL.XLS").Activate Application.CutCopyMode = False Selection.Copy Windows("TMSPhatomReport.xls").Activate ActiveSheet.Paste Range("B33").Select Windows("TMSDL.XLS").Activate Cells.Find(What:="REPORTED TO PAYROLL", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Windows("TMSPhatomReport.xls").Activate Windows("TMSDL.XLS").Activate Range("C974:K974").Select Application.CutCopyMode = False Selection.Copy Windows("TMSPhatomReport.xls").Activate ActiveSheet.Paste Range("A34").Select Windows("TMSDL.XLS").Activate Range("A974").Select Windows("TMSPhatomReport.xls").Activate Windows("TMSDL.XLS").Activate If anyone can help this would be greatly appreicated MartÃ*n Hinojosa -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Asstance need
Try something like this:
Sub FindPayRoll() Dim r As Range Dim fC As Range 'First Cell Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _ After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If r Is Nothing Then Exit Sub Set fC = r Do r.Resize(1,9).Copy ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1) Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _ After:=r, LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Loop Until r.Address = fC.Address End Sub -- Charles Chickering "A good example is twice the value of good advice." "Hinojosa via OfficeKB.com" wrote: Good Day, I'm trying to write a macro and i have never used this before so i'm teaching my self as i go along This is what i have so far but the problem is with the "Reported to Payroll". The situation is the way i have it now is good if everyone works everyday and and is never absent, but we all know how that goes so therefore "Reported to Payroll" will keep moving around and thats no problem it's just that i need the 8 cells next to it and the values in each of those cells are always changing so i cant use the FIND command. The way i have it working now is it will find a person's name copy to the other spreadsheet and then it will find that persons "Report to Payroll" because it's underneath(its about 3 columns over and 35 down) their name and I have over 300 employees to do this for so is there a formula that i can use that will always find "Reported To Payroll" and the 8 cells next to no matter where it is on the spreadsheet (keep in mind that Reported To Payroll appears over 300 times on the spreadsheet) (this is how it looks when i dwnld the report) "Name" ... ... ... ... "Report to payroll" x x x x x x x x (this is what i have now) Cells.Find(What:="name", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Windows("TMSPhatomReport.xls").Activate Windows("TMSDL.XLS").Activate Application.CutCopyMode = False Selection.Copy Windows("TMSPhatomReport.xls").Activate ActiveSheet.Paste Range("B33").Select Windows("TMSDL.XLS").Activate Cells.Find(What:="REPORTED TO PAYROLL", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Windows("TMSPhatomReport.xls").Activate Windows("TMSDL.XLS").Activate Range("C974:K974").Select Application.CutCopyMode = False Selection.Copy Windows("TMSPhatomReport.xls").Activate ActiveSheet.Paste Range("A34").Select Windows("TMSDL.XLS").Activate Range("A974").Select Windows("TMSPhatomReport.xls").Activate Windows("TMSDL.XLS").Activate If anyone can help this would be greatly appreicated MartÃ*n Hinojosa -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Asstance need
I'm sorry i'm new at this, what does r=to, and would i do this for each
person? Charles Chickering wrote: Try something like this: Sub FindPayRoll() Dim r As Range Dim fC As Range 'First Cell Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _ After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If r Is Nothing Then Exit Sub Set fC = r Do r.Resize(1,9).Copy ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1) Set r = ws1.Cells.Find(What:="REPORTED TO PAYROLL", _ After:=r, LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Loop Until r.Address = fC.Address End Sub Good Day, [quoted text clipped - 55 lines] MartÃ*n Hinojosa -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Asstance need
you'll have it by 0900
Charles Chickering wrote: Martin, Can you send a copy of the workbook and a more detailed description of what you want to happen to: ? I'm sorry i'm new at this, what does r=to, and would i do this for each person? [quoted text clipped - 26 lines] MartÃ*n Hinojosa -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200610/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|