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

Brass Contributor

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

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

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

@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.

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

@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.

but excel stall when i copy large columns .

=ЕСЛИОШИБКА(ВПР([@[name ]];tblAge;2;0);"Not found")
Can we put this code in vba macros and how to make faster?

@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

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

@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.

 

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.

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

@Riny_van_Eekelen 

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.

@Sergei Baklan 

 

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.

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.

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

@sergey989 

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.

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

how does Mockup make?