Replace values (Excel 2000 VBA)

Topic

New Reply

> 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?

Reply | Quote

Replies

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

Reply | Quote

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:

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$B$20,2,FALSE)),B1,VLOOKUP(A1,Sheet1!$A$1:$B$20,2,FALSE))

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.

Reply | Quote

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:

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$B$20,2,FALSE)),B1,VLOOKUP(A1,Sheet1!$A$1:$B$20,2,FALSE))

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.

Reply | Quote

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.

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

Reply | Quote

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.

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

Reply | Quote

Legare,
This is exactly what I was looking for. Thank you very much, I really appreciate it.

Reply | Quote

Legare,
This is exactly what I was looking for. Thank you very much, I really appreciate it.

Reply | Quote