I have attached the image of my mapping table and written these two functions referring to the mapping table that I created : (Table name is "Automation") [ATTACH=JSON]{"alt":"Click image for larger version Name:\tMap.PNG Views:\t2 Size:\t57.0 KB ID:\t1198534","data-align":"none","data-attachmentid":"1198534","data-size":"full","title":"Map.PNG"}[/ATTACH]
1)
Function GetRow(rowName As String) As String
Dim refRange As Range: Set refRange = Sheet14.Range("Automation")
On Error GoTo errProc
GetRow = WorksheetFunction.VLookup(rowName, refRange, 2, 0)
Exit Function
errProc:
If Err.Number = 1004 Then
Err.Raise "5000", "Something bad happened", "Value " & rowName & " not found!!"
Else
Err.Raise Err.Number, Err.Source, Err.Description
End If
End Function
2)
Function GetMap(rowName As String) As String
Dim refRange As Range: Set refRange = Sheet14.Range("Automation")
On Error GoTo errProc
GetMap = WorksheetFunction.VLookup(rowName, refRange, 1, 0)
Exit Function
errProc:
If Err.Number = 1004 Then
Err.Raise "5000", "Something bad happened", "Value " & rowName & " not found!!"
Else
Err.Raise Err.Number, Err.Source, Err.Description
End If
End Function
And this is the snippet of my updated code :
Dim initial As String
initial = GetMap(GetRow(wkb.Sheets(SourceName)))
j = Wb.Sheets(DestName).Cells(1, 1).EntireColumn.Find(What:=initial, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
Call CopyRange(Sheets(SourceName).Range("C12:R12"), Wb.Sheets(DestName).Cells(j, 2), completed)
completed = completed + (100 / steps)
Call CopyRange(Sheets(SourceName).Range("C22:R22"), Wb.Sheets(DestName).Cells(j, 2), completed)
completed = completed + (100 / steps)
Call CopyRange(Sheets(SourceName).Range("C17:R17"), Wb.Sheets(DestName).Cells(j, 2), completed)
completed = completed + (100 / steps)
wkb.Close
When I try the code, I get an error saying "Object doesn't support this property" for the part where I want the function to return a value. I can't figure out how to correct this. Sorry, I am new to VBA. Any help would be appreciated.