Codes needed to update wages

Hi,

Column A of Sheet1 holds the identity numbers of about 200 casual members of 
our staffs. Column F shows their current weekly wages. I need a simple macro 
that allows me to update the weekly wages for some of these members by 4.5% 
from a list of their identity numbers shown in Column A of Sheet2. Any help 
is much appreciated.

TIA
Tom


0
Tom
4/5/2010 1:20:02 AM
excel.programming 6508 articles. 2 followers. Follow

8 Replies
783 Views

Similar Articles

[PageSpeed] 57

Here one shot at it:

Sub payBoost()
   Dim lr As Long, sh As Worksheet, rng As Range
   Dim fRng As Range
   Set sh = ActiveSheet
   lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
   Set rng = sh.Range("A2:A" & lr)
   Set c = rng.Find(InputBox("Enter an ID number.", "ID NUMBER"), _
    LookIn:=xlValues)
        If Not c Is Nothing Then
           Set fRng = Range("F" & c.Row)
           fRng = fRng.Value + (fRng.Value * 0.045)
        End If
End Sub

This will ask the user to input an identification number.  It will then find 
that number in column A and add 4.5% to the amount shown in column F and 
post the new value to column F.



"Tom" <tclimb@hotmail.com> wrote in message 
news:6Vaun.17296$pv.5154@news-server.bigpond.net.au...
> Hi,
>
> Column A of Sheet1 holds the identity numbers of about 200 casual members 
> of our staffs. Column F shows their current weekly wages. I need a simple 
> macro that allows me to update the weekly wages for some of these members 
> by 4.5% from a list of their identity numbers shown in Column A of Sheet2. 
> Any help is much appreciated.
>
> TIA
> Tom
>
> 


0
JLGWhiz
4/5/2010 2:01:32 AM
That was quick. It does what you intended it to do. However it
does not avoid the hard work of having to enter those numbers
one at a time. I was looking for a way for the program to
sequentially read the numbers in Sheet2, finds its corresponding
number in Sheet1 and then carry out the update. This way it saves
a lot of work. See if you can figure out a way for the program to do
just that. Thank you for your eforts.

"JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message 
news:uRnXrPG1KHA.364@TK2MSFTNGP02.phx.gbl...
> Here one shot at it:
>
> Sub payBoost()
>   Dim lr As Long, sh As Worksheet, rng As Range
>   Dim fRng As Range
>   Set sh = ActiveSheet
>   lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
>   Set rng = sh.Range("A2:A" & lr)
>   Set c = rng.Find(InputBox("Enter an ID number.", "ID NUMBER"), _
>    LookIn:=xlValues)
>        If Not c Is Nothing Then
>           Set fRng = Range("F" & c.Row)
>           fRng = fRng.Value + (fRng.Value * 0.045)
>        End If
> End Sub
>
> This will ask the user to input an identification number.  It will then 
> find that number in column A and add 4.5% to the amount shown in column F 
> and post the new value to column F.
>
>
>
> "Tom" <tclimb@hotmail.com> wrote in message 
> news:6Vaun.17296$pv.5154@news-server.bigpond.net.au...
>> Hi,
>>
>> Column A of Sheet1 holds the identity numbers of about 200 casual members 
>> of our staffs. Column F shows their current weekly wages. I need a simple 
>> macro that allows me to update the weekly wages for some of these members 
>> by 4.5% from a list of their identity numbers shown in Column A of 
>> Sheet2. Any help is much appreciated.
>>
>> TIA
>> Tom
>>
>>
>
> 


0
Tom
4/5/2010 3:07:06 AM
Tom,
In defense of JLGWhiz, you did say "...allows me to update the weekly wages 
for some of these members by..."  And that's what his code does.  Had you 
initially requested code to update them all, I'm certain he would have 
provided exactly that.  I'm certain that he overlooked, as I did, the 
at-the-end of the post reference to identity numbers on sheet2.

So, try this code in a copy of your workbook and see if it does what you 
want or not.  You'll need to change the Const values at the beginning of it 
after you do the copy to match worksheet names and column IDs in your 
workbook before running it.

Sub UpdateWages()
  'alter Const values as needed for your workbook
  Const wageSheetName = "SheetWithWages" ' sheet1?
  Const firstWGIDRow = 2 ' first row w/employee id
  Const wsIDColumn = "A"
  Const wswagecolumn = "F"
  Const amtOfRaise = 0.045 ' 4.5%
  Const updateListSheetName = "RaiseListSheet" ' sheet2?
  Const lsIDColumn = "A"
  
  Dim wgWS As Worksheet
  Dim wgIdList As Range
  Dim anywgID As Range
  Dim lsWS As Worksheet
  Dim lsIDList As Range
  Dim anylsID As Range
  
  Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
  Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
   & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
  Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
  Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
  For Each anywgID In wgIdList
    
    Set anylsID = lsIDList.Find(What:=anywgID, _
     LookIn:=xlFormulas, _
     LookAt:=xlPart, SearchOrder:=xlByRows, _
     SearchDirection:=xlNext, MatchCase:=False, _
     SearchFormat:=False)
    If Not anylsID Is Nothing Then
      'found a match
      wgWS.Range(wswagecolumn & anywgID.Row) = _
       wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
    End If
  
  Next
  Set wgIdList = Nothing
  Set lsIDList = Nothing
  Set wgWS = Nothing
  Set lsWS = Nothing
End Sub





"Tom" wrote:

> That was quick. It does what you intended it to do. However it
> does not avoid the hard work of having to enter those numbers
> one at a time. I was looking for a way for the program to
> sequentially read the numbers in Sheet2, finds its corresponding
> number in Sheet1 and then carry out the update. This way it saves
> a lot of work. See if you can figure out a way for the program to do
> just that. Thank you for your eforts.
> 
> "JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message 
> news:uRnXrPG1KHA.364@TK2MSFTNGP02.phx.gbl...
> > Here one shot at it:
> >
> > Sub payBoost()
> >   Dim lr As Long, sh As Worksheet, rng As Range
> >   Dim fRng As Range
> >   Set sh = ActiveSheet
> >   lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
> >   Set rng = sh.Range("A2:A" & lr)
> >   Set c = rng.Find(InputBox("Enter an ID number.", "ID NUMBER"), _
> >    LookIn:=xlValues)
> >        If Not c Is Nothing Then
> >           Set fRng = Range("F" & c.Row)
> >           fRng = fRng.Value + (fRng.Value * 0.045)
> >        End If
> > End Sub
> >
> > This will ask the user to input an identification number.  It will then 
> > find that number in column A and add 4.5% to the amount shown in column F 
> > and post the new value to column F.
> >
> >
> >
> > "Tom" <tclimb@hotmail.com> wrote in message 
> > news:6Vaun.17296$pv.5154@news-server.bigpond.net.au...
> >> Hi,
> >>
> >> Column A of Sheet1 holds the identity numbers of about 200 casual members 
> >> of our staffs. Column F shows their current weekly wages. I need a simple 
> >> macro that allows me to update the weekly wages for some of these members 
> >> by 4.5% from a list of their identity numbers shown in Column A of 
> >> Sheet2. Any help is much appreciated.
> >>
> >> TIA
> >> Tom
> >>
> >>
> >
> > 
> 
> 
> .
> 
0
Utf
4/5/2010 4:28:01 AM
Wow! You are very methodical and it performs exactly what I hope it would 
do. Thank you very much.
I have a slightly similar request. This time getting back some data. Much 
obliged if you can help with this task as outlined below:

Read a list of column names from two open workbooks - MyWorkbook1 in Sheet1, 
starting from Column2 Row 3, then find the same name in Myworkbook2. Copy a 
set of 5 row values next to its right, then paste it back in MyWorkbook1 
next to the right and stop at the end of the list. Skip if a name is not 
found.

"JLatham" <JLatham@discussions.microsoft.com> wrote in message 
news:482D56F9-DCDC-45BA-963C-FCA4D67EA9A8@microsoft.com...
> Tom,
> In defense of JLGWhiz, you did say "...allows me to update the weekly 
> wages
> for some of these members by..."  And that's what his code does.  Had you
> initially requested code to update them all, I'm certain he would have
> provided exactly that.  I'm certain that he overlooked, as I did, the
> at-the-end of the post reference to identity numbers on sheet2.
>
> So, try this code in a copy of your workbook and see if it does what you
> want or not.  You'll need to change the Const values at the beginning of 
> it
> after you do the copy to match worksheet names and column IDs in your
> workbook before running it.
>
> Sub UpdateWages()
>  'alter Const values as needed for your workbook
>  Const wageSheetName = "SheetWithWages" ' sheet1?
>  Const firstWGIDRow = 2 ' first row w/employee id
>  Const wsIDColumn = "A"
>  Const wswagecolumn = "F"
>  Const amtOfRaise = 0.045 ' 4.5%
>  Const updateListSheetName = "RaiseListSheet" ' sheet2?
>  Const lsIDColumn = "A"
>
>  Dim wgWS As Worksheet
>  Dim wgIdList As Range
>  Dim anywgID As Range
>  Dim lsWS As Worksheet
>  Dim lsIDList As Range
>  Dim anylsID As Range
>
>  Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
>  Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
>   & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
>  Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
>  Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
>  For Each anywgID In wgIdList
>
>    Set anylsID = lsIDList.Find(What:=anywgID, _
>     LookIn:=xlFormulas, _
>     LookAt:=xlPart, SearchOrder:=xlByRows, _
>     SearchDirection:=xlNext, MatchCase:=False, _
>     SearchFormat:=False)
>    If Not anylsID Is Nothing Then
>      'found a match
>      wgWS.Range(wswagecolumn & anywgID.Row) = _
>       wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
>    End If
>
>  Next
>  Set wgIdList = Nothing
>  Set lsIDList = Nothing
>  Set wgWS = Nothing
>  Set lsWS = Nothing
> End Sub
>
>


0
Tom
4/5/2010 7:51:11 AM
I'm a little confused by "Copy a set of 5 rows next to its right..."

Let's say we are looking at the first name in MyWorkbook 1 (at B3) and have 
found a match in MyWorkbook2 (at what cell?) then what rows/address range 
gets copied, AND will new rows need to be inserted into MyWorkbook1 to paste 
the information into.

Just to make sure: colums go up and down the sheet, rows go across it.

So if you can explain it something like this:
Match name in column B of workbook1 to name in column ?? of workbook2, then 
copy 5 rows from workbook2 to workbook1, inserting new rows as needed.  
Example:
Cell in workbook 1 B3, matches ??## in workbook2, copy ??## to ??##+4 into 
workbook1 starting at ??#.

I need the column IDs for ?? and the row numbers for ## and #.

"Tom" wrote:

> Wow! You are very methodical and it performs exactly what I hope it would 
> do. Thank you very much.
> I have a slightly similar request. This time getting back some data. Much 
> obliged if you can help with this task as outlined below:
> 
> Read a list of column names from two open workbooks - MyWorkbook1 in Sheet1, 
> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy a 
> set of 5 row values next to its right, then paste it back in MyWorkbook1 
> next to the right and stop at the end of the list. Skip if a name is not 
> found.
> 
> "JLatham" <JLatham@discussions.microsoft.com> wrote in message 
> news:482D56F9-DCDC-45BA-963C-FCA4D67EA9A8@microsoft.com...
> > Tom,
> > In defense of JLGWhiz, you did say "...allows me to update the weekly 
> > wages
> > for some of these members by..."  And that's what his code does.  Had you
> > initially requested code to update them all, I'm certain he would have
> > provided exactly that.  I'm certain that he overlooked, as I did, the
> > at-the-end of the post reference to identity numbers on sheet2.
> >
> > So, try this code in a copy of your workbook and see if it does what you
> > want or not.  You'll need to change the Const values at the beginning of 
> > it
> > after you do the copy to match worksheet names and column IDs in your
> > workbook before running it.
> >
> > Sub UpdateWages()
> >  'alter Const values as needed for your workbook
> >  Const wageSheetName = "SheetWithWages" ' sheet1?
> >  Const firstWGIDRow = 2 ' first row w/employee id
> >  Const wsIDColumn = "A"
> >  Const wswagecolumn = "F"
> >  Const amtOfRaise = 0.045 ' 4.5%
> >  Const updateListSheetName = "RaiseListSheet" ' sheet2?
> >  Const lsIDColumn = "A"
> >
> >  Dim wgWS As Worksheet
> >  Dim wgIdList As Range
> >  Dim anywgID As Range
> >  Dim lsWS As Worksheet
> >  Dim lsIDList As Range
> >  Dim anylsID As Range
> >
> >  Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
> >  Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
> >   & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
> >  Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
> >  Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
> >  For Each anywgID In wgIdList
> >
> >    Set anylsID = lsIDList.Find(What:=anywgID, _
> >     LookIn:=xlFormulas, _
> >     LookAt:=xlPart, SearchOrder:=xlByRows, _
> >     SearchDirection:=xlNext, MatchCase:=False, _
> >     SearchFormat:=False)
> >    If Not anylsID Is Nothing Then
> >      'found a match
> >      wgWS.Range(wswagecolumn & anywgID.Row) = _
> >       wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
> >    End If
> >
> >  Next
> >  Set wgIdList = Nothing
> >  Set lsIDList = Nothing
> >  Set wgWS = Nothing
> >  Set lsWS = Nothing
> > End Sub
> >
> >
> 
> 
> .
> 
0
Utf
4/5/2010 12:11:01 PM
At the risk of getting way ahead of myself, I wrote the following code based 
on what I am guessing you really want, and my idea of that is:
match names on 2 sheets in 2 different workbooks, and when a match is found, 
then copy 5 COLUMNS next to the match in the second workbook into the first 
one.

So if you find a match in 2nd workbook at B33 (name in first WB at B4) then 
copy C33:G33 from 2nd workbook into C4:G4 of the first one.  The various 
column IDs are definable in the code.  

Here's that code (note that it prompts you for the second workbook, so that 
one should not be open when you run the macro).  I've tried to keep the lines 
short so that the system here doesn't mess things up.  Check after you copy 
the code for any red lines in your code, that just means that whatever is red 
probably should be at the end of the line above it.


Sub CopyFrom2ndWorkbook()
  'change these Const values as required
  'name of the worksheet in this workbook
  'to copy data into, is also the sheet
  'with the source list of names
  Const destinationSheetName = "Sheet1"
  'first row with names in it
  Const destSheet1stNameRow = 2
  'column with the names in it
  Const destSheetNamesCol = "B"
  '1st column to copy information into
  Const destSheet1stCopyCol = "C"
  'last column to copy information into
  Const destSheetLastCopyCol = "G"
  
  'information about worksheet in the other
  'workbook (one that will be opened and copied from)
  Const sourceSheetName = "2ksPublicAssistance (3)"
  Const srcSheetNamesCol = "B"
  'first row with names in it
  Const srcSheet1stNameRow = 2
  'first column to copy from
  Const srcSheet1stCopyCol = "C"
  'last column to copy from
  Const srcSheetLastCopyCol = "G"
  
  Dim srcWB As Workbook ' will be copy from workbook
  Dim srcWS As Worksheet ' will be copy from sheet
  Dim srcNamesList As Range
  Dim anySrcName As Range
  Dim srcCopyRange As Range
  Dim srcWBName As String
  
  Dim destWS As Worksheet ' sheet in this workbook
  Dim destNamesList As Range
  Dim anyDestName As Range
  Dim destCopyRange As Range
  
  'prompt user to open the other workbook
  srcWBName = Application.GetOpenFilename
  If UCase(Trim(srcWBName)) = "FALSE" Then
    'user cancelled the get filename operation
    Exit Sub
  End If
  Application.ScreenUpdating = False
  'open w/o updating links and as Read Only
  Application.DisplayAlerts = False
  Workbooks.Open srcWBName, False, True
  Application.DisplayAlerts = True
  'opened book becomes active
  Set srcWB = ActiveWorkbook
  'back to this workbook
  ThisWorkbook.Activate
  Set srcWS = srcWB.Worksheets(sourceSheetName)
  Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
   & ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)
  
  Set destWS = ThisWorkbook.Worksheets(destinationSheetName)
  Set destNamesList = destWS.Range(destSheetNamesCol & destSheet1stNameRow _
   & ":" & destWS.Range(destSheetNamesCol & Rows.Count).End(xlUp).Address)
  
  'note that in VB, case is important: Bill does not = BILL
  For Each anyDestName In destNamesList
    For Each anySrcName In srcNamesList
      If anySrcName = anyDestName Then
        'have a match
        'NOTE: number of columns in each range must be same
        'not their addresses, but total number of columns, as
        'C#:G# = 5 columns
        Set srcCopyRange = srcWS.Range(srcSheet1stCopyCol _
         & anySrcName.Row _
         & ":" & srcSheetLastCopyCol & anySrcName.Row)
        Set destCopyRange = destWS.Range(destSheet1stCopyCol & 
anyDestName.Row _
         & ":" & destSheetLastCopyCol & anyDestName.Row)
        destCopyRange.Value = srcCopyRange.Value
        'we can quit now that we found the match
        Exit For ' exit the anySrcName loop
      End If
    Next
  Next
  'housekeeping
  Set destNamesList = Nothing
  Set srcNamesList = Nothing
  Set srcWS = Nothing
  Set destWS = Nothing
  'close the other workbook, do not save changes
  Application.DisplayAlerts = False
  srcWB.Close False
  Application.DisplayAlerts = True
  Set srcWB = Nothing
  MsgBox "Copy from:" & vbCrLf & srcWBName & vbCrLf & "Completed", _
   vbOKOnly + vbInformation, "Task Finished"
End Sub



"Tom" wrote:

> Wow! You are very methodical and it performs exactly what I hope it would 
> do. Thank you very much.
> I have a slightly similar request. This time getting back some data. Much 
> obliged if you can help with this task as outlined below:
> 
> Read a list of column names from two open workbooks - MyWorkbook1 in Sheet1, 
> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy a 
> set of 5 row values next to its right, then paste it back in MyWorkbook1 
> next to the right and stop at the end of the list. Skip if a name is not 
> found.
> 
> "JLatham" <JLatham@discussions.microsoft.com> wrote in message 
> news:482D56F9-DCDC-45BA-963C-FCA4D67EA9A8@microsoft.com...
> > Tom,
> > In defense of JLGWhiz, you did say "...allows me to update the weekly 
> > wages
> > for some of these members by..."  And that's what his code does.  Had you
> > initially requested code to update them all, I'm certain he would have
> > provided exactly that.  I'm certain that he overlooked, as I did, the
> > at-the-end of the post reference to identity numbers on sheet2.
> >
> > So, try this code in a copy of your workbook and see if it does what you
> > want or not.  You'll need to change the Const values at the beginning of 
> > it
> > after you do the copy to match worksheet names and column IDs in your
> > workbook before running it.
> >
> > Sub UpdateWages()
> >  'alter Const values as needed for your workbook
> >  Const wageSheetName = "SheetWithWages" ' sheet1?
> >  Const firstWGIDRow = 2 ' first row w/employee id
> >  Const wsIDColumn = "A"
> >  Const wswagecolumn = "F"
> >  Const amtOfRaise = 0.045 ' 4.5%
> >  Const updateListSheetName = "RaiseListSheet" ' sheet2?
> >  Const lsIDColumn = "A"
> >
> >  Dim wgWS As Worksheet
> >  Dim wgIdList As Range
> >  Dim anywgID As Range
> >  Dim lsWS As Worksheet
> >  Dim lsIDList As Range
> >  Dim anylsID As Range
> >
> >  Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
> >  Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
> >   & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
> >  Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
> >  Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
> >  For Each anywgID In wgIdList
> >
> >    Set anylsID = lsIDList.Find(What:=anywgID, _
> >     LookIn:=xlFormulas, _
> >     LookAt:=xlPart, SearchOrder:=xlByRows, _
> >     SearchDirection:=xlNext, MatchCase:=False, _
> >     SearchFormat:=False)
> >    If Not anylsID Is Nothing Then
> >      'found a match
> >      wgWS.Range(wswagecolumn & anywgID.Row) = _
> >       wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
> >    End If
> >
> >  Next
> >  Set wgIdList = Nothing
> >  Set lsIDList = Nothing
> >  Set wgWS = Nothing
> >  Set lsWS = Nothing
> > End Sub
> >
> >
> 
> 
> .
> 
0
Utf
4/5/2010 2:32:02 PM
"JLatham" <JLatham@discussions.microsoft.com> wrote in message 
news:8B325DBE-6E34-41CF-B146-42BA3F00D0E9@microsoft.com...
> I'm a little confused by "Copy a set of 5 rows next to its right..."

No. Copy a set of 5 row values next to its right, i.e. next 5 cells to its 
right along that row.
These are the next 5 pieces of information for that particular person like, 
Address, Post Code, Phone No., Mobile No. and Starting Date.

>
> Let's say we are looking at the first name in MyWorkbook 1 (at B3) and 
> have
> found a match in MyWorkbook2 (at what cell?) then what rows/address range
> gets copied, AND will new rows need to be inserted into MyWorkbook1 to 
> paste
> the information into.
>
> Just to make sure: colums go up and down the sheet, rows go across it.
>
> So if you can explain it something like this:
> Match name in column B of workbook1 to name in column ?? of workbook2, 
> then
> copy 5 rows from workbook2 to workbook1, inserting new rows as needed.
> Example:
> Cell in workbook 1 B3, matches ??## in workbook2, copy ??## to ??##+4 into
> workbook1 starting at ??#.
>
> I need the column IDs for ?? and the row numbers for ## and #.
>
> "Tom" wrote:
>
>> Wow! You are very methodical and it performs exactly what I hope it would
>> do. Thank you very much.
>> I have a slightly similar request. This time getting back some data. Much
>> obliged if you can help with this task as outlined below:
>>
>> Read a list of column names from two open workbooks - MyWorkbook1 in 
>> Sheet1,
>> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy 
>> a
>> set of 5 row values next to its right, then paste it back in MyWorkbook1
>> next to the right and stop at the end of the list. Skip if a name is not
>> found.
>>
>> "JLatham" <JLatham@discussions.microsoft.com> wrote in message
>> news:482D56F9-DCDC-45BA-963C-FCA4D67EA9A8@microsoft.com...
>> > Tom,
>> > In defense of JLGWhiz, you did say "...allows me to update the weekly
>> > wages
>> > for some of these members by..."  And that's what his code does.  Had 
>> > you
>> > initially requested code to update them all, I'm certain he would have
>> > provided exactly that.  I'm certain that he overlooked, as I did, the
>> > at-the-end of the post reference to identity numbers on sheet2.
>> >
>> > So, try this code in a copy of your workbook and see if it does what 
>> > you
>> > want or not.  You'll need to change the Const values at the beginning 
>> > of
>> > it
>> > after you do the copy to match worksheet names and column IDs in your
>> > workbook before running it.
>> >
>> > Sub UpdateWages()
>> >  'alter Const values as needed for your workbook
>> >  Const wageSheetName = "SheetWithWages" ' sheet1?
>> >  Const firstWGIDRow = 2 ' first row w/employee id
>> >  Const wsIDColumn = "A"
>> >  Const wswagecolumn = "F"
>> >  Const amtOfRaise = 0.045 ' 4.5%
>> >  Const updateListSheetName = "RaiseListSheet" ' sheet2?
>> >  Const lsIDColumn = "A"
>> >
>> >  Dim wgWS As Worksheet
>> >  Dim wgIdList As Range
>> >  Dim anywgID As Range
>> >  Dim lsWS As Worksheet
>> >  Dim lsIDList As Range
>> >  Dim anylsID As Range
>> >
>> >  Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
>> >  Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
>> >   & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
>> >  Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
>> >  Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
>> >  For Each anywgID In wgIdList
>> >
>> >    Set anylsID = lsIDList.Find(What:=anywgID, _
>> >     LookIn:=xlFormulas, _
>> >     LookAt:=xlPart, SearchOrder:=xlByRows, _
>> >     SearchDirection:=xlNext, MatchCase:=False, _
>> >     SearchFormat:=False)
>> >    If Not anylsID Is Nothing Then
>> >      'found a match
>> >      wgWS.Range(wswagecolumn & anywgID.Row) = _
>> >       wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
>> >    End If
>> >
>> >  Next
>> >  Set wgIdList = Nothing
>> >  Set lsIDList = Nothing
>> >  Set wgWS = Nothing
>> >  Set lsWS = Nothing
>> > End Sub
>> >
>> >
>>
>>
>> .
>> 


0
Tom
4/5/2010 9:35:45 PM
This time round I'm the one that is confused. I mentioned that the workbooks 
are, MyWorkbook1 in Sheet1 starting cursor position Column2 Row 3 
(destination) and MyWorkbook2 (source) - no other parameters are needed for 
the latter. Both are open. Using those information it should shorten the 
codes somewhat.
Also, after a source name is found, can you do an offset like, 
activecell.offset(0, 1).range("A1:A5").select to copy the 5 cells on the 
right, bring it back to MyWorkbook1, do another offset, activecell.offset(0, 
1).select and paste?Then reposition the cursor if needed.
The first line that stumped me is:

Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
   & ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)

What are the items that I have to replace? I never learned VBA but know how 
to use Excel's automatic macro procedure. So, if you can modify some of the 
lines it would be a great help. Thanks!

"JLatham" <JLatham@discussions.microsoft.com> wrote in message 
news:DA4EB501-5C00-45C7-924D-F21DA07B4636@microsoft.com...
> At the risk of getting way ahead of myself, I wrote the following code 
> based
> on what I am guessing you really want, and my idea of that is:
> match names on 2 sheets in 2 different workbooks, and when a match is 
> found,
> then copy 5 COLUMNS next to the match in the second workbook into the 
> first
> one.
>
> So if you find a match in 2nd workbook at B33 (name in first WB at B4) 
> then
> copy C33:G33 from 2nd workbook into C4:G4 of the first one.  The various
> column IDs are definable in the code.
>
> Here's that code (note that it prompts you for the second workbook, so 
> that
> one should not be open when you run the macro).  I've tried to keep the 
> lines
> short so that the system here doesn't mess things up.  Check after you 
> copy
> the code for any red lines in your code, that just means that whatever is 
> red
> probably should be at the end of the line above it.
>
>
> Sub CopyFrom2ndWorkbook()
>  'change these Const values as required
>  'name of the worksheet in this workbook
>  'to copy data into, is also the sheet
>  'with the source list of names
>  Const destinationSheetName = "Sheet1"
>  'first row with names in it
>  Const destSheet1stNameRow = 2
>  'column with the names in it
>  Const destSheetNamesCol = "B"
>  '1st column to copy information into
>  Const destSheet1stCopyCol = "C"
>  'last column to copy information into
>  Const destSheetLastCopyCol = "G"
>
>  'information about worksheet in the other
>  'workbook (one that will be opened and copied from)
>  Const sourceSheetName = "2ksPublicAssistance (3)"
>  Const srcSheetNamesCol = "B"
>  'first row with names in it
>  Const srcSheet1stNameRow = 2
>  'first column to copy from
>  Const srcSheet1stCopyCol = "C"
>  'last column to copy from
>  Const srcSheetLastCopyCol = "G"
>
>  Dim srcWB As Workbook ' will be copy from workbook
>  Dim srcWS As Worksheet ' will be copy from sheet
>  Dim srcNamesList As Range
>  Dim anySrcName As Range
>  Dim srcCopyRange As Range
>  Dim srcWBName As String
>
>  Dim destWS As Worksheet ' sheet in this workbook
>  Dim destNamesList As Range
>  Dim anyDestName As Range
>  Dim destCopyRange As Range
>
>  'prompt user to open the other workbook
>  srcWBName = Application.GetOpenFilename
>  If UCase(Trim(srcWBName)) = "FALSE" Then
>    'user cancelled the get filename operation
>    Exit Sub
>  End If
>  Application.ScreenUpdating = False
>  'open w/o updating links and as Read Only
>  Application.DisplayAlerts = False
>  Workbooks.Open srcWBName, False, True
>  Application.DisplayAlerts = True
>  'opened book becomes active
>  Set srcWB = ActiveWorkbook
>  'back to this workbook
>  ThisWorkbook.Activate
>  Set srcWS = srcWB.Worksheets(sourceSheetName)
>  Set srcNamesList = srcWS.Range(srcSheetNamesCol & srcSheet1stNameRow _
>   & ":" & srcWS.Range(srcSheetNamesCol & Rows.Count).End(xlUp).Address)
>
>  Set destWS = ThisWorkbook.Worksheets(destinationSheetName)
>  Set destNamesList = destWS.Range(destSheetNamesCol & destSheet1stNameRow 
> _
>   & ":" & destWS.Range(destSheetNamesCol & Rows.Count).End(xlUp).Address)
>
>  'note that in VB, case is important: Bill does not = BILL
>  For Each anyDestName In destNamesList
>    For Each anySrcName In srcNamesList
>      If anySrcName = anyDestName Then
>        'have a match
>        'NOTE: number of columns in each range must be same
>        'not their addresses, but total number of columns, as
>        'C#:G# = 5 columns
>        Set srcCopyRange = srcWS.Range(srcSheet1stCopyCol _
>         & anySrcName.Row _
>         & ":" & srcSheetLastCopyCol & anySrcName.Row)
>        Set destCopyRange = destWS.Range(destSheet1stCopyCol &
> anyDestName.Row _
>         & ":" & destSheetLastCopyCol & anyDestName.Row)
>        destCopyRange.Value = srcCopyRange.Value
>        'we can quit now that we found the match
>        Exit For ' exit the anySrcName loop
>      End If
>    Next
>  Next
>  'housekeeping
>  Set destNamesList = Nothing
>  Set srcNamesList = Nothing
>  Set srcWS = Nothing
>  Set destWS = Nothing
>  'close the other workbook, do not save changes
>  Application.DisplayAlerts = False
>  srcWB.Close False
>  Application.DisplayAlerts = True
>  Set srcWB = Nothing
>  MsgBox "Copy from:" & vbCrLf & srcWBName & vbCrLf & "Completed", _
>   vbOKOnly + vbInformation, "Task Finished"
> End Sub
>
>
>
> "Tom" wrote:
>
>> Wow! You are very methodical and it performs exactly what I hope it would
>> do. Thank you very much.
>> I have a slightly similar request. This time getting back some data. Much
>> obliged if you can help with this task as outlined below:
>>
>> Read a list of column names from two open workbooks - MyWorkbook1 in 
>> Sheet1,
>> starting from Column2 Row 3, then find the same name in Myworkbook2. Copy 
>> a
>> set of 5 row values next to its right, then paste it back in MyWorkbook1
>> next to the right and stop at the end of the list. Skip if a name is not
>> found.
>>
>> "JLatham" <JLatham@discussions.microsoft.com> wrote in message
>> news:482D56F9-DCDC-45BA-963C-FCA4D67EA9A8@microsoft.com...
>> > Tom,
>> > In defense of JLGWhiz, you did say "...allows me to update the weekly
>> > wages
>> > for some of these members by..."  And that's what his code does.  Had 
>> > you
>> > initially requested code to update them all, I'm certain he would have
>> > provided exactly that.  I'm certain that he overlooked, as I did, the
>> > at-the-end of the post reference to identity numbers on sheet2.
>> >
>> > So, try this code in a copy of your workbook and see if it does what 
>> > you
>> > want or not.  You'll need to change the Const values at the beginning 
>> > of
>> > it
>> > after you do the copy to match worksheet names and column IDs in your
>> > workbook before running it.
>> >
>> > Sub UpdateWages()
>> >  'alter Const values as needed for your workbook
>> >  Const wageSheetName = "SheetWithWages" ' sheet1?
>> >  Const firstWGIDRow = 2 ' first row w/employee id
>> >  Const wsIDColumn = "A"
>> >  Const wswagecolumn = "F"
>> >  Const amtOfRaise = 0.045 ' 4.5%
>> >  Const updateListSheetName = "RaiseListSheet" ' sheet2?
>> >  Const lsIDColumn = "A"
>> >
>> >  Dim wgWS As Worksheet
>> >  Dim wgIdList As Range
>> >  Dim anywgID As Range
>> >  Dim lsWS As Worksheet
>> >  Dim lsIDList As Range
>> >  Dim anylsID As Range
>> >
>> >  Set wgWS = ThisWorkbook.Worksheets(wageSheetName)
>> >  Set wgIdList = wgWS.Range(wsIDColumn & firstWGIDRow & ":" _
>> >   & wgWS.Range(wsIDColumn & Rows.Count).End(xlUp).Address)
>> >  Set lsWS = ThisWorkbook.Worksheets(updateListSheetName)
>> >  Set lsIDList = lsWS.Range(lsIDColumn & ":" & lsIDColumn)
>> >  For Each anywgID In wgIdList
>> >
>> >    Set anylsID = lsIDList.Find(What:=anywgID, _
>> >     LookIn:=xlFormulas, _
>> >     LookAt:=xlPart, SearchOrder:=xlByRows, _
>> >     SearchDirection:=xlNext, MatchCase:=False, _
>> >     SearchFormat:=False)
>> >    If Not anylsID Is Nothing Then
>> >      'found a match
>> >      wgWS.Range(wswagecolumn & anywgID.Row) = _
>> >       wgWS.Range(wswagecolumn & anywgID.Row) * (1 + amtOfRaise)
>> >    End If
>> >
>> >  Next
>> >  Set wgIdList = Nothing
>> >  Set lsIDList = Nothing
>> >  Set wgWS = Nothing
>> >  Set lsWS = Nothing
>> > End Sub
>> >
>> >
>>
>>
>> .
>> 


0
Tom
4/5/2010 10:36:14 PM
Reply:

Similar Artilces:

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

I need to create a flip chart as a guide for teachers.
I need a template or program to create this on the computer so that i may and and or delete as needed any ideas out there? Only you know the time line and subject of your flip chart. There is a PowerPoint template here that is printable, but it will need a lot of editing. http://www.computerlab.kids.new.net/worksheets.htm A good book http://books.mcgraw-hill.com/getbook.php?isbn=0071343113&template= -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "NYC Teacher Help" <NYC Teacher Help@discussions.microsoft.com> wrot...

Cannot access windows update via Control Panel
But I can access windows update if I first start IE8 and select windowsupdate from the Tools menu. When I try to access windows update via Control Panel, I get this message: 1. On the Tools menu in Internet Explorer, click Internet Options. 2. Click the Security tab. 3. Click the Trusted Sites icon, and then click Sites... 4. Uncheck the "require server verification" checkbox. 5. Make sure the following URLs are listed in the Web Sites list box: * http://*.windowsupdate.microsoft.com * http://*.windowsupdate.com I followed the ...

Help needed for inexperienced MS Excel 2003 user
Hi group. I am very inexperienced in using MS Excel however I am trying to develop a simple spreadsheet which will do a calculation that I have to do on a regular basis. I have several questions which I hope that the people within this group can kindly help me with. Ok, here goes. 1 - I want a cell to contain the calculation (I55*3600)/I54 is the syntax simply =(I55*3600)/I54 I've currently got the formula =SUM(I55*3600)/I54 in this cell. I have believe that this formula is incorrect. 2 - The cell where the above formula is located contains the message #DIV/O! until a value is inp...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

Need Advice on Backing Up Entourage 2008 Mail
Hi there, The end of the year is fast approaching and I need to clear off some space and my Entourage mail is the logical starting point. I have over 20 GB of e-mails here of which I need to remove all but the most recent items, say I keep no more than 5 GB as a maximum. My question is this. I know I can dag/copy the mail folders to my desktop. If I then burn those folders to a CD or DVD (or even memory stick) are those folders still searchable if I need to look for a specific message? As an alternative am I better using Mail (after importing the Entourage folders into same --...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

Updating IV00102 through EConnect
Is it possible to Update IV00102 table through eConnect. I need to Update the GP-Item Quantity from my external applicatiom .I think direct updation will be danger. ...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

How to setup public e-mail address where multiple users need to receive messages?
I have been requested to setup a public address - public@foobar.com where the messages are received by several users. In addition, if the users reply, the message needs to come from public@foobar.com, not the users address. What is the best way to set this up? Exchange 2003 and Outlook 2003 with Microsoft AD. Thanks in advance. Setup a Universal Distribution Group in AD Users & Computers. Add all recipients who need to get a copy of the message as members. Also assign Send As right on the group to all members. When replying to messages using Outlook, users will need to populate t...

Desperate need for help
I'm stumped. I have an EX55 server that seems infected with some sort of mass mailing Trojan but I just can't find it. Here are the symptoms. Soon after turning on the SMTP connecter my out bound queue fills with NDRs destine to what appears to be domains in Korea. It look as if the mass mailer is emailing into my server with bogus addresses and causing the NDRs to go out. After just a few minutes the outbound queue is full and any legitimate email won't go out. I've turned off all really options and this happens even when the server is NOT connected to the network/i...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Need to check if the value in one cell is correct or not
Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digi...

Need advice on setting up a test server/customization platform...
I'm in the process of setting up a dedcated machine (SBS 2K3 with all the bells and whistles including SQL 2005) to handle testing and customization of GP 9.0 systems. Since customizing GP seems to leave a pretty nasty mess behind. (importing cnk files, vba and so on) plus different clients have different 3rd party add-ins with separate registration keys, I am looking for advice ont the best way to go about doing this. What I want is a fairly straightforward way of duplicating the GP environment of each client so if they require maintenance work or new customizations I can just lo...

exceL NOOB needs help w/sales tracking
I manage a nightclub and want to create a sales tracking chart pe server. This is easy if all servers occupied the same cells on ever worksheet. However, corporate insists that the daily sales report b designed around stations. Therefore, my servers rotate from station t station on a daily basis. I know i could always make a seperat worksheet designed around the servers and input the server totals second time onto that worksheet but i was hoping of finding someway o linking a second worksheet that totals up the sales per server as enter them into the daily sales report (the one designed ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

Need query to apply schedule to a category
I have created a sale schedule from 6pm-9pm Wed, Thr & Fri - named it NightSale. In RMS you must open each item - go to properties box - pricing tab - select schedule button & then the schedule name -- item by item. I want to apply this "sale schedule" to a CATEGORY using a query and a query to remove the sale schedule from the same category after the sale is over. My "NightSale" is schedule ID 9 - if that helps. Example: This week - apply "NightSale" schedule to all items in Category 1 & Category 2 End of week - remove "NightSale" sch...

Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...

I need a template for at a glance viewing 2005 projects for staff.
I am trying to creat an easy to use template for tracking projects of my event planners. I would like to be able to include information pertaining to dates of event, planners name, name of event, city and hotel locationl. ...

Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...