[RESOLVED] Replacing cells in one wksht with lookup from another

[MarkWalsh]

Next step in my current project, I need to move user's data from worksheets that I have imported from an older workbook in VBA.

I'm not quite sure how to do a quick replace of the contents. I can manually type the following formula in one of the cells of the worksheet to be updated (the source worksheet is named 'SOURCE' here):

=VLOOKUP((A11),SOURCE!A11:V11,15)

And this will show the proper lookup value, but I would like to set the contents to the values (not the formula) in the corresponding cells using VBA. I need to replace columns 12 through (last used column) for all of the cells from row 11 to the last row, based on the lookup value in column 1. Do I need to loop through each row, and then loop through each column, or can I replace the contents of the entire range with a single command?

My next steps will be marking rows that are in the destination worksheet that don't exist in the source worksheet ('New'), and then duplicating rows in the source that don't exist in the destination and marking those as 'Deleted'.

I appreciate any assistance that anyone can provide.

[westconn1]

you can convert formulas to static values at a given point using

range("C32").Value = range("c32").Text

[MarkWalsh]

you can convert formulas to static values at a given point using

range("C32").Value = range("c32").Text

Thanks, Westconn, although that's not quite what I'm trying to do (the formula is not already set in the cells).

The workbook I am working on contains data on customers in 4 different segments (4 worksheets). The first column is the matching unique ID number, the next 10 columns is static data that is being provided to the users (they should not be changing this data) and the rest of the used columns #11+ (the number and data changes per wksheet) is user-specific entered data.

I need to update the user-specific columns when a new version is sent to the users. This data will be blank in the updated file. What I currently have will allow the user to select their previous file, and it checks to make sure each data worksheet exists in both files. Then it pulls a copy of each worksheet into the new file. Here I need to update the user data columns in the current worksheet with matching values in the imported worksheet, and this is what I have been struggling with.

Is it possible to set a range in the current worksheet to the values of the lookup from the imported worksheet in one shot, or do I need to loop through each cell of the range one by one setting it's value to the lookup value? My VBA experience has been working in PowerPoint, and I have been unable so far to get something working. Here's a sample of the code I have been (unsuccessfully) trying:

    With rngDestData
        'Loop through all rows in reverse
        For lRowNum = .Rows.Count To 1 Step -1
            'Get the value from the current row
            vLookupValue = .Cells(lRowNum, 1).value
            If Not (vLookupValue = "") Then
                'Look for that value in the range of known good values
                'Using the .Find Method of the Range Object
                'Checking for a complete match (xlWhole)
                If rngSourceData.Find(What:=vLookupValue, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
                    'If no match - mark row as 'NEW'
                    Debug.Print "Row '" & vLookupValue & "' is New!"
                Else
                    'If match - set values of user data range
                    ' NOTE - Range will vary per worksheet - Range = column L through last used column
                     rngDestData.FormulaR1C1 = Application.WorksheetFunction.Lookup(("A11"), sourceWksht.Name & "!A11:V11", 15)
                End If
            End If
        Next lRowNum
    End With

Once the update is done, I will delete the imported worksheet.

There are a few more details I need to work out (they want to highlight new rows that didn't exist in the old data, as well as highlight rows that were removed from the new version). Also, our client has already been using this file, and didn't lock the file before distributing it, so I will need to do several validations since there was nothing to prevent the users from changing/moving the columns/rows or renaming the worksheets. But my first priority is showing a working version that updated the data, assuming that nothing has been changed by the user.

Thanks again for the assistance, I hope I have explained better what I am dealing with.

[MarkWalsh]

OK, I'm making progress. Since I haven't used Excel enough, I'm still struggling with the range object, and the lookup (which I am reading up on the help sections). But I now have the following as a test:

Dim rng As Range
Set rng = Range("A11", Range("A11").End(xlDown))
rng = WorksheetFunction.VLookup(rng, Range("TEST_Import!A11:V51"), 14, 0)

And this is getting me closer to what I want - It's getting the lookup values, but it's currently putting the values into the first column. Now I need to figure out what I am doing wrong in the lookup (or setting the range).

[MarkWalsh]

Aha! I think I got it!

Set rng = Range("A11", Range("A11").End(xlDown))
Set destRng = Range("N11", Range("N11").End(xlDown))
destRng = WorksheetFunction.VLookup(rng, Range("TEST_Import!A11:V51"), 14, 0)

[MarkWalsh]

I've gotten most of the code working the way I want it to for now, but I do have one issue I want to solve. If the row does not have a matching row in the import worksheet, I get '#N/A' as the value. I'm not sure if there's an easy adjustment I can make to the code to fix this. Here's the code I am using:

Option Explicit

Public Function doLookup()
    Dim sourceWksht As Worksheet
    Dim destWksht As Worksheet
    
    ' Column to use as lookup in Destination worksheet (should be first column - XXX_ID)
    Dim rngLookup As Range
    ' Column to set user data in Destination worksheet
    Dim rngDestination As Range
    ' Range of cells in Source worksheet to retrieve user data
    Dim rngSource As Range
    
    ' First row containing data (Should be row 11 if user made no changes)
    Dim startingRow As Integer
    ' First column containing user data (Should be column 12 if user made no changes)
    Dim firstDataColumn As Integer
    
    Dim lastColumn As Integer
    Dim lastRow As Integer
    Dim i As Integer
    
    ' Set source & destination worksheets - These will be function parameters in the final function
    Set sourceWksht = Worksheets.Item("TEST_IMPORT")
    Set destWksht = Worksheets.Item("TEST")
    
    ' Get first row containing data in Destination worksheet
    startingRow = 11
    firstDataColumn = 12
    ' Get last row & column in Destination worksheet
    lastColumn = FindLastColumn(destWksht)
    lastRow = FindLastRow(destWksht)
    If (lastColumn < 1) Or (lastRow < 1) Then
        ' No data in worksheet, exit function
        Exit Function
    End If
    
    ' Set lookup column & destination column in Destination worksheet
    If True Then
        Set rngLookup = Range(destWksht.Name & "!A" & startingRow).Resize(rowSize:=lastRow - startingRow)
        Set rngDestination = Range(destWksht.Name & "!L" & startingRow).Resize(rowSize:=lastRow - startingRow)
    Else
        'Set rngLookup = Range(destWksht.Name & "!A" & startingRow, Range(destWksht.Name & "!A" & startingRow).End(xlDown))
        'Set rngDestination = Range(destWksht.Name & "!L" & startingRow, Range(destWksht.Name & "!L" & startingRow).End(xlDown))
    End If
    
    ' Get first row containing data in Source worksheet
    startingRow = 11
    ' Get last row & column in Source worksheet
    lastColumn = FindLastColumn(sourceWksht) ' Need to get last column of Source worksheet here (Not current)??
    lastRow = FindLastRow(sourceWksht) ' Need to get last column of Source worksheet here (Not current)??
    If (lastColumn < 1) Or (lastRow < 1) Then
        ' No data in worksheet, exit function
        Exit Function
    End If
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    ' Set the source range in Source worksheet to locate user data - first column will be the lookup column
    Set rngSource = Range(sourceWksht.Name & "!A" & startingRow).Resize(rowSize:=lastRow - startingRow, columnsize:=lastColumn)
    For i = firstDataColumn To lastColumn
        ' Lookup values from current column
        rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
        
        ' Note need to reset range destination here!
        Set rngDestination = rngDestination.Offset(columnoffset:=1)
    Next i
      
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Function

Private Function FindLastColumn(Optional ByRef wksht As Worksheet = Nothing) As Integer
    'Finds last used column, copies and selects the next
    Dim lastColumn As Integer
    Dim NextColumn As Integer
    If wksht Is Nothing Then
        ' If no worksheet/workbook active, need to return 0
        If ActiveSheet Is Nothing Then
            FindLastColumn = -1
            Exit Function
        Else
            Set wksht = ActiveSheet
        End If
    End If
    'Find last column with text
    If WorksheetFunction.CountA(wksht.Cells) > 0 Then
         'Search for any entry, by searching backwards by Columns.
        lastColumn = wksht.Cells.Find(What:="*", After:=[A1], _
                                        SearchOrder:=xlByColumns, _
                                        SearchDirection:=xlPrevious).Column
    End If
    FindLastColumn = lastColumn
End Function

Private Function FindLastRow(Optional ByRef wksht As Worksheet = Nothing) As Integer
    'Finds last used Row, copies and selects the next
    Dim lastRow As Integer
    Dim NextRow As Integer
    If wksht Is Nothing Then
        ' If no worksheet/workbook active, need to return 0
        If ActiveSheet Is Nothing Then
            FindLastRow = -1
            Exit Function
        Else
            Set wksht = ActiveSheet
        End If
    End If
    'Find last Row with text
    If WorksheetFunction.CountA(wksht.Cells) > 0 Then
         'Search for any entry, by searching backwards by Rows.
        lastRow = wksht.Cells.Find(What:="*", After:=[A1], _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlPrevious).Row
    End If
    FindLastRow = lastRow
End Function

Is there a change I can make to this line:

rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)

That will leave the cells empty for rows that don't have a matching value? I have tried replacing that line with:

rngDestination = WorksheetFunction.IfError(WorksheetFunction.VLookup(rngLookup, rngSource, i, 0), "")

And that will leave the rows that don't have a matching value blank, but with this line empty cells in matching rows are now set to '0'

If anyone has any comments/suggestions on any other improvements I can make to this, please let me know. In several places, I am setting values based on the current document (starting rows for data, etc.). Eventually, I will expand this to verify that the correct data is in the proper place (in case the user has added/removed/moved any columns - unfortunately, the client sent this workbook out unlocked to be used before they created an 'update' procedure, and that's what they have asked me to do for this project)

Thanks again for anyone who gives this a look.

[westconn1]

but with this line empty cells in matching rows are now set to '0'

i presume this must be because of the numberformat of the range

[MarkWalsh]

i presume this must be because of the numberformat of the range

I don't think so; the range was originally set to 'General', and the same thing happens even if it's set to 'Text'.

I was able to find a solution by using the following after doing the lookup:

Call rngDestination.Replace("#N/A", "")

And this does give me the results I want, although in the slim chance that the user has actually entered "#N/A" into a cell, it will be removed, so I'd still prefer to find a way to do this during the lookup if possible. Plus if I can do it in one command, it should hopefully speed things up a little as well.

I have also posted a question here as well, looking for a solution to the 'N/A' issue.