Forum Discussion
sergey989
Apr 27, 2021Brass Contributor
How do we remake the program to copy not one value value but with range in column age?
0 How do we remake the program to copy not one value value but with range in column age? We need copy all values from age.xlsm age column to another age column in shchool.xlsm file school.xls...
sergey989
Apr 28, 2021Brass Contributor
new version why program does not work Run-time error '1004' application-defined or object defined error on line If Cells(d, "A") = iName Then Sub Insertdata() Dim iAge As Integer Set src=Workbooks.Open("D:\school1\age.xlsm", True, True) t = 2 lastrow = ActiveSheet.UsedRange.Rows.Count Do Until t = lastrow iAge = src.Worksheets("Sheet1").Range("B" & t).Value IName = src.Worksheets("Sheet1").Range("A" & t).Value Do Until d = lastrow If Cells(d, "A") = IName Then ThisWorkbook.Worksheets("Sheet1").Range("B" & d).Value = iAge d = d + 1 End If Loop t = t + 1 Loop End Sub
- sergey989Apr 29, 2021Brass Contributorwhy my program stall with no output?
Option Explicit
Sub Insertdata()
Dim src As Workbook
Dim iAge As Integer
Dim t As Integer
Dim d As Integer
Dim lastrow As Integer
Dim iName As String
Set src=Workbooks.Open("D:\school1\age.xlsm", True, True)
lastrow = ActiveSheet.UsedRange.Rows.Count
t = 2
d = 2
Do Until t = lastrow
iAge = src.Worksheets("Sheet1").Range("B" & t).Value
iName = src.Worksheets("Sheet1").Range("A" & t).Value
Do Until d = lastrow
If Cells(d, "A") = iName Then
ThisWorkbook.Worksheets("Sheet1").Range("B" & d).Value = iAge
d = d + 1
End If
Loop
t = t + 1
Loop
End Sub
The program copy all values from age.xlsm age column to another age column in shchool.xlsm file according to name column. Name is key in tables.
school.xlsm
|A | B |
|:---- |:------:|
|name | age|
|peter1 | |
|jane2 | |
|ted | |
|peter3 | |
age.xlsm
|A |B|
|:---- |:------:|
|name |age|
|mark3| 1,2|
|peter1 | 5 |
|jane2 | 1,5|