# how do we fill in school.xlsx file age column from age.xlsx file by using name column with visual b

Occasional Contributor

# how do we fill in school.xlsx file age column from age.xlsx file by using name column with visual b

how do we fill in school.xlsx file age column from age.xlsx file by using name column with visual basic excel? Tables have variable length and have more than 10000 rows.

school.xlsx

|A | B | C|
|:---- |:------:| -----:|
|name | code | age|
|peter1 | 254 |
|jane2 |153 |
|ted | 323 |
|peter3 |323 |

age.xlsx
|A |B|
|:---- |:------:|
|name |age|
|mark3| 1,2|
|peter1 | 5 |
|jane2 | 1,5|

'''

Sub Insertdata()
Dim iAge As Integer
Set src=Workbooks.Open("age.xlsx", True, True)
iAge = src.Worksheets("Sheet1").Range("B2").Value
Worksheets("Sheet1").Range("C2").Value = iAge
End Sub

'''
Now the program returns value 1 in field C2 in age.xlsx file.
How do we remake the program to insert age data in right row in shcool.xlsx?

18 Replies

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

@sergey989 Why VBA? A simple VLOOKUP or INDEX/MATCH (or XLOOKUP if your Excel version supports it) can do this.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

can you show me example?
but we have variable length column

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

@sergey989 See attached example. I chose to put the data in structured tables. All three lookup methods are in the table at the top under age1, age2 and age3.

See if you can get it to work on your side.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

can we make in two different files and very large and very large variable length table?

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

@sergey989 Yes you can. Look at the MS support pages for the function you want to use and follow the instructions there. Not much more I could do than repeat what has been written there already.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

but excel stall when i copy large columns .

Can we put this code in vba macros and how to make faster?

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

@sergey989 Please provide a bit more info, otherwise we will go back and forth without achieving anything. Perhaps you can upload some files that are like your real ones and then I can perhaps enter the required formulae. From Google translate I understand that you are going for "ВПР" = VLOOKUP

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

sample two files

how do we fill in school.xlsx file age column from age.xlsx file by using key name column with visual basic excel? Tables have variable length and have more than 10000 rows.

school.xlsx

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

@sergey989 Two files attached. This time I just used the data as you provided it. Didn't bother to create structured tables. You need to have both files open.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

how do i make condition in cell for example that i have value of cell in shool.xlsm file and i don't want it will be lost but formula work only for those values that exist in age.xlsx? Another words new values.

how do we instead of Not found value in third column in shcool.xlsm file have previous value that was stored before from early version of file age.xlsx? example attach

for example, BRS43 now have value Not found but ealier it has value 2. How can we make that file school.xlsm store previous values and formula do not erase previous value and only update some rows with values from newer age.xlsx file.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

@sergey989 Sorry, I don't understand. Perhaps @Sergei Baklan can help out. You can communicate with him in Russian.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

Sorry, VBA is not my territory. If I understood correctly if the value is not found in age we keep it in school as it is, otherwise take value from age.

I'd use Power Query to combine data such way if that's suitable.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

how do we instead of Not found value in third column in shcool.xlsm file have previuos value that was stored before from early version of file age.xlsx? example attach

for example BRS43 now have value Not found but ealier it has value 2. How can we make that file school.xlsm store previos values and formula do not earese previuos value and only update some rows with values from newer age.xlsx file.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

how do we instead of Not found value in third column in shcool.xlsm file have previous value that was stored before from early version of file age.xlsx? example attach
for example, BRS43 now have value Not found but ealier it has value 2. How can we make that file school.xlsm store previous values and formula do not erase previous value and only update some rows with values from newer age.xlsx file. New verion of file included.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

how can we use Power Query

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

how about idea to use another shool2.xlsx file that renew only old values with old values where value return by formula is Not found

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

If to keep previous value in stack and return into the same cell result of calculation or previous value, depends on condition, that's only with VBA. Can't help with it.

If Power Query it'll be one more table which merge these two ranges and return the value from one or another depends on they match or not. Mockup is in attached file.

# Re: how do we fill in school.xlsx file age column from age.xlsx file by using name column with visua

How dose file school.xlsm was made with power query?

how does Mockup make?