Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Office 2000)

    > I can solve with code but it takes alot of it
    Is it possible that you could open Notepad, copy your code in from the Excel VBEditor, save it as a Text document (TXT extension) and then post it as an attachment here in the Lounge?
    Gre

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Office 2000)

    Here is the easy way to do this without VBA:

    Go to Sheet 2 and select an empty column. Insert the formula below into row 1 of that column:

    <pre>=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$B$20,2,FA LSE)),B1,VLOOKUP(A1,Sheet1!$A$1:$B$20,2,FALSE))
    </pre>


    Copy this formula down the column as far as there are names and handicaps in columns A & B.

    This column should now display the updated handicaps.

    Now, select the column with the updated handicaps and select Copy from The Edit menu. Then select cell B1 and then select "Paste Special" from the edit menu. Put a tic mark next to Values in the Paste section of the dialog box. Click the OK button and the updated handicaps should be put into column B. Now you can delete the column with the formulas.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Office 2000)

    If you want to do this with code, then the code below should do what you want (not really a lot of code). It has the additional advantage that if there is a name on sheet1 that is not on sheet2, the code will add it to sheet 2.

    <pre>Public Sub UpdateHandicaps()
    Dim lLastRowNew As Long, lLastRowOld As Long, I As Long, J As Long
    lLastRowNew = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    For I = 0 To lLastRowNew
    lLastRowOld = Worksheets("Sheet2").Range("A65536").End(xlUp).Row - 1
    For J = 0 To lLastRowOld
    If Worksheets("Sheet1").Range("A1").Offset(I, 0) = _
    Worksheets("Sheet2").Range("A1").Offset(J, 0) Then
    Worksheets("Sheet2").Range("A1").Offset(J, 1) = _
    Worksheets("Sheet1").Range("A1").Offset(I, 1)
    Exit For
    End If
    Next J
    If J > lLastRowOld Then
    Worksheets("Sheet2").Range("A1").Offset(J, 0) = _
    Worksheets("Sheet1").Range("A1").Offset(I, 0)
    Worksheets("Sheet2").Range("A1").Offset(J, 1) = _
    Worksheets("Sheet1").Range("A1").Offset(I, 1)
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Feb 2004
    Location
    Findlay, Ohio, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace values (Excel 2000 VBA)

    Subject Edited by HansV to make it more descriptive than "Excel"

    I have a problem with an Excel worksheet that I can solve with code but it takes alot of it. This problem relates to golf and a league I'm setting up for the year. On sheet1 I have i.g. 20 names in Col A and their newly updated handicaps in Col B. On sheet2 I have 100 names in Col A and their last years handicaps in Col B. What I would like to do is replace the handicaps on Sheet2 Col B with the newly updated handicaps from Sheet1 Col B. The names on sheet 1, Col A, will not be in the same order as the names on Sheet2 Col A. Sheet1 contains the golfers playing the current week which will change from week to week.
    Hopefully I have submitted enough information. I would like to do this with alot less code.
    Thank you,
    Max <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    New Lounger
    Join Date
    Feb 2004
    Location
    Findlay, Ohio, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace values (Excel 2000 VBA)

    Legare,
    This is exactly what I was looking for. Thank you very much, I really appreciate it. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •