Hi,

I am a novice to intermediate user with vba. I have two worksbooks, one that
contains the assignment data and the other that contains the raw data.

assignment book (file A)
fields - workQ (col A)
- BeginRange(col B)
- EndRange (Col C)
- Name (Col F)
raw data (file B)
Fields - WorkQ (using variable name "workq" via find function)
- SecDesc (using variable name "secdesc" via find function)

We currently have a macro that formats file B. I need to modify this macro
to look at file A and where there is a match to bring over the person who is
assigned the task.

We need to match 3 columns from file B with file A, work queue, begin letter
range, end letter range. To get the letter range, I am using the left
function and pulling the first letter of an account(secdesc) from file B. On
file A,
certain workqueues have subaccounts and that is where the letter range comes
in.

What would be the best way to perform the matches between workbooks and if
possible could you provide me examples. I hope I made sense explaining this.

Any assistance would be greatly appreciated.

--
Thank you

Dave

Hi,

Maybe I could elaborate some more. The macro worked when we were doing
matches on the WorkQ alone on each workbook but now I need to further expand
the matches to include the account range. Please see the code below which is
how we have it today and I need to expand it to look at the
account(security). Should I still use vlookup or would index match be a
better option?

With Application.FileSearch
.NewSearch
.Filename = _
"\\NTSDATA02607A\GCTEAMSDEPT\GCTeams\Corpact\O ps
Support\Assignments\Specialist-Supervisor Assignments.xls"
.Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderDescending
Workbooks.Open .FoundFiles(1), IgnoreReadOnlyRecommended:=True
End With

Set logicBook = ActiveWorkbook
l = Cells(1, 1).CurrentRegion.Rows.Count
StrRng = Range(Cells(2, 1), Cells(l, 8)).Address

SuiteWbk.Activate

c = ActiveSheet.Cells.Find(what:="WORK_QUEUE").Column
Work_Q = Cells(2, c).Address(RowAbsolute:=False, ColumnAbsolute:=False)

StSpecialist = "=VLookup(" & Work_Q & ",'[" & logicBook.Name & "]SSAssign'!"
& StrRng & ", 6, false)"
Debug.Print strLookup
Cells(2, 3).Formula = StSpecialist
Range("C2").Copy Destination:=Range(Cells(2, 3), Cells(rr, 3))
--
Thank you

Dave


"Dave" wrote:

Hi,

I am a novice to intermediate user with vba. I have two worksbooks, one that
contains the assignment data and the other that contains the raw data.

assignment book (file A)
fields - workQ (col A)
- BeginRange(col B)
- EndRange (Col C)
- Name (Col F)
raw data (file B)
Fields - WorkQ (using variable name "workq" via find function)
- SecDesc (using variable name "secdesc" via find function)

We currently have a macro that formats file B. I need to modify this macro
to look at file A and where there is a match to bring over the person who is
assigned the task.

We need to match 3 columns from file B with file A, work queue, begin letter
range, end letter range. To get the letter range, I am using the left
function and pulling the first letter of an account(secdesc) from file B. On
file A,
certain workqueues have subaccounts and that is where the letter range comes
in.

What would be the best way to perform the matches between workbooks and if
possible could you provide me examples. I hope I made sense explaining this.

Any assistance would be greatly appreciated.

--
Thank you

Dave