excel - Find and Replace VBA based on another worksheet table - Stack Overflow

admin管理员组

文章数量:1406016

I am trying to add some logic to do a find and replace for an advance VBA macro. I am not familiar with VBA but trying add a fix from your help.

my constants(separate Module):

' EMPLOYEE
Public Const EMPLOYEE_WB = "EmployeeTime.xlsx"
Public Const EMPLOYEE_WS = "EmployeeTime"
Public Const EMPLOYEE_START_RANGE = "A2"

Public Const EMPLOYEE_MAP_WS = "TimeTypeMapping"
Public Const EMPLOYEE_MAP_TABLE = "timetypemapping_table"
Public Const EMPLOYEE_MAP_COLUMN = 3

Public Const EMPLOYEE_HRS_MAP_WS = "TimeHrsConversion"
Public Const EMPLOYEE_HRS_MAP_TABLE = "timehrsconversion_table"
Public Const EMPLOYEE_HRS_MAP_COLUMN = 2

timehrsconversion_table

Need to replace column I and J based on column C

I have add something (copied from another script)

' Map unpaid leave days
Set employeeHrsMapWs = employeeWB.Sheets(cnst.EMPLOYEE_HRS_MAP_WS)
Set employeeHrsMapTable = employeeHrsMapWs.ListObjects(cnst.EMPLOYEE_HRS_MAP_TABLE)
Set sourceRange = employeeWS.Range(cnst.EMPLOYEE_START_RANGE).Offset(1, cnst.EMPLOYEE_HRS_MAP_COLUMN - 1).Resize(UBound(importTableData, 1) - Range(cnst.EMPLOYEE_START_RANGE).Row + 1, 1)
Set searchRange = employeeHrsMapTable.DataBodyRange
map_handler.GetMap sourceRange, searchRange, 3, 3, 0

map_handler.getMap module:

' General mapping

Sub GetMap(sourceRange As Range, searchRange As Range, _
           searchColumn As Long, returnColumn As Long, _
                                 replaceColumnOffset As Long)

    Dim sourceArray As Variant
    Dim resultValue As String
    Dim i As Long, j As Long
    Dim searchString As String
    Dim entered As Boolean
        
    searchArray = searchRange.value
    sourceArray = sourceRange.value
        
    For i = 1 To UBound(sourceArray, 1)
        searchString = CStr(sourceArray(i, 1))
        Debug.Print searchString
        entered = False
        
        If searchString <> "" Then
            For j = 1 To UBound(searchArray, 1)
                If Trim(searchArray(j, searchColumn)) = Trim(searchString) Then
                    sourceRange.Cells(1, 1).Offset(i - 1, replaceColumnOffset).value = searchArray(j, returnColumn)
                    entered = True
                    Exit For
                End If
            Next j
                
            If Not entered Then
               sourceRange.Cells(1, 1).Offset(i - 1, 0).value = ""              
            End If
        End If      'searchString <> ""           
    Next i
End Sub

But I do not thing its actually doing what I want instead its deleting a values from another column. Could you please help me to do find and replace

本文标签： excelFind and Replace VBA based on another worksheet tableStack Overflow