Merging two spreadsheets using VBA based on a number of conditions

[methuselah90]

I have 2 spreadsheets - uat.xlsx and main.xlsx - both in the same directory.

main.xlsx

[Click Here](null)

uat.xlsx

Click Here

I am trying to copy across information from uat.xlsx to main.xlsx, by accomplishing the following in VBA (the VBA code will be stored on main.xlsx and launched by button click):

If column A in main.xlsx matches column B in uat.xlsx, then check to see if there is anything in column J-Lin main.xlsx matches anything in column A on uat.xlsx for the user found, if these two conditions are met then:

on the matching row in main.xlsx, update column M on main.xlsx with "Yes"

once this is done, grab the first 10 characters of column D in uat.xlsx, then on the same matching row on main.xlsx, update column Q with the values

once this is done, check column C in uat.xlsx, if the value is "I already have", then on the same matching row on main.xlsx, update column N with "Physical", if the value is "Required", then on the same matching row on main.xlsx, , update column N with "Virtual"

This only needs to happen once per match - ignore any duplicate matches on uat.xlsx (i.e. on a new row)

Secondly - I need to eliminate any differences in "case" by making standardising all the values checked to either lowercase/uppercase.

How would I articulate this as VBA code?

[Stuugie]

Have you tried a VLookup? It's not VBA but whenever someone wants to find matches, that's the first thing I think of.