Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with Offset
I am trying to get this to work I have my data laid out like this Emp # Name '''''''''' Dept# '''''Shift'''Hire Date'''''''''Jobcode''''''Jobtitle 121 '''''John Doe'''''' 2525 '''''''''' A''''''07/08/06'''''''' 56254 ''''''''''' Job1 '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' 25648 ''''''''''''Job2 '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' 45465 ''''''''''''Job3 I am searching the jobcode column for a jobcode say 45465 I want to copy that jobcode and job title as well as the employee data to a new worksheet list I can select what I need using this code This code almost works Worksheets("Data Entry").Activate ActiveCell.Offset(rowOffset:=-2, columnOffset:=-5).Activate ActiveCell.Range("A1:E1").Select it Only Works if I find the 3rd code down 45465 But if I search for the second or the first codes the offset continues to move up. How can I make the offset find the employee data each time as well as the jobcode and job title? Each employee is listed like the above. Please forgive the '''''''' it's the only way I can get the layout to stay right Thanks Charles -- mrdata ------------------------------------------------------------------------ mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899 View this thread: http://www.excelforum.com/showthread...hreadid=560096 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with Offset
Try this...
Worksheets("Data Entry").Activate ActiveCell.Offset(0, -5).End(xlUp).Select -- HTH... Jim Thomlinson "mrdata" wrote: I am trying to get this to work I have my data laid out like this Emp # Name '''''''''' Dept# '''''Shift'''Hire Date'''''''''Jobcode''''''Jobtitle 121 '''''John Doe'''''' 2525 '''''''''' A''''''07/08/06'''''''' 56254 ''''''''''' Job1 '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' 25648 ''''''''''''Job2 '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' 45465 ''''''''''''Job3 I am searching the jobcode column for a jobcode say 45465 I want to copy that jobcode and job title as well as the employee data to a new worksheet list I can select what I need using this code This code almost works Worksheets("Data Entry").Activate ActiveCell.Offset(rowOffset:=-2, columnOffset:=-5).Activate ActiveCell.Range("A1:E1").Select it Only Works if I find the 3rd code down 45465 But if I search for the second or the first codes the offset continues to move up. How can I make the offset find the employee data each time as well as the jobcode and job title? Each employee is listed like the above. Please forgive the '''''''' it's the only way I can get the layout to stay right Thanks Charles -- mrdata ------------------------------------------------------------------------ mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899 View this thread: http://www.excelforum.com/showthread...hreadid=560096 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with Offset
It works for any rows below the first set of rows. The first three rows under the header row it selects the header ro instead. Also I need it to select Column B,C,D,E of the Employee Data as well. Getting there good job! Thanks Charle -- mrdat ----------------------------------------------------------------------- mrdata's Profile: http://www.excelforum.com/member.php...fo&userid=1789 View this thread: http://www.excelforum.com/showthread.php?threadid=56009 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with Offset
Now how do I make it copy the data after it finds it. It will need to search the jobcode column. Emp # Name '''''''''' Dept# '''''Shift'''Hire Date'''''''''Jobcode''''''Jobtitle 121 '''''John Doe'''''' 2525 '''''''''' A''''''07/08/06'''''''' 56254 ''''''''''' Job1 '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' 25648 ''''''''''''Job2 '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''' 45465 ''''''''''''Job3 And copy the Jobcode ,Job Title and the employee data for each employee it finds matching the search. How can I merge this ActiveCell.Offset(0, -5).End(xlUp).Select To work with the following code? This code only copys the row data adjacent to the search string (Job Code) Thus I get Blank feilds up to the jobcode and job title which are copied just fine. The only way around this is to have the persons name listed three times one for each jobcode. I would like to have the data laid out as above instead. On Error GoTo Fixit: Dim WS As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim Str As String Dim sqyds On Error Resume Next Application.DisplayAlerts = False Sheets("Prebid Job List").Delete Application.DisplayAlerts = True On Error GoTo 0 Dim Message, Title, Default, Mydate Message = "Enter a Job Code For the Open Position Report ie:830134" ' Set prompt. Title = "Open Position Pre-Bid Search" ' Set title. Sname = InputBox(Message, Title) Set WS = Sheets("Data Entry") Set rng = WS.Range("A4:G4").CurrentRegion Str = "Prebid Job List" 'Close AutoFilter first WS.AutoFilterMode = False rng.AutoFilter Field:=6, Criteria1:=Str rng.AutoFilter Field:=6, Criteria1:=Sname Set WSNew = Worksheets.Add WS.AutoFilter.Range.Copy With WSNew.Range("A6") ..PasteSpecial Paste:=8 ..PasteSpecial xlPasteValues ..PasteSpecial xlPasteFormats Application.CutCopyMode = False ..Select End With WS.AutoFilterMode = False -- mrdata ------------------------------------------------------------------------ mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899 View this thread: http://www.excelforum.com/showthread...hreadid=560096 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM & OFFSET | Excel Worksheet Functions | |||
Offset Q | Excel Worksheet Functions | |||
Do I really need OFFSET? | Charts and Charting in Excel | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming |