-
Apr 28th, 2021, 07:58 AM
#1
Thread Starter
Member
How do we remake the program to copy not one value value but with range in column age
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.xlsm
A B
name age
peter1
jane2
ted
peter3
age.xlsm
A B
name age
mark3 1,2
peter1 5
jane2 1,5
the program copy one value, how do we copy all values from age column from age.xlsm to shool.xlsm file
Sub copydata()
Dim iAge As Integer
Set src = Workbooks.Open("age.xlsm", True, True)
iAge = src.Worksheets("Sheet1").Range("B2").Value
ThisWorkbook.Worksheets("Sheet1").Range("В2").Value = iAge
End Sub
Edit: Code added from comment
Sub Insertdata()
Dim iAge As Integer
Set src = Workbooks.Open("age.xlsm", True, True)
t = 2
lastrow = ActiveSheet.UsedRange.Rows.Count
Do Until t = lastrow
iAge = src.Worksheets("Sheet1").Range("B" & t).Value
ThisWorkbook.Worksheets("Sheet1").Range("B" & t).Value = iAge
t = t + 1
Loop
End Sub
the last makros return wrong output jane2 must be value of 1,5 and it is not it is 5 we need return connected date with key name
-
Apr 28th, 2021, 09:17 AM
#2
Thread Starter
Member
Re: How do we remake the program to copy not one value value but with range in column
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
-
Apr 29th, 2021, 05:13 AM
#3
Re: How do we remake the program to copy not one value value but with range in column
i guess initial value of d = 0
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 29th, 2021, 06:50 AM
#4
Thread Starter
Member
Re: How do we remake the program to copy not one value value but with range in column
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
MsgBox lastrow
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
End If
d = d + 1
Loop
t = t + 1
Loop
End Sub
-
Apr 29th, 2021, 06:51 AM
#5
Thread Starter
Member
Re: How do we remake the program to copy not one value value but with range in column
why my program does not work right?The program do not 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.
-
Apr 29th, 2021, 07:35 AM
#6
Re: How do we remake the program to copy not one value value but with range in column
this double loop looks terribly inefficient
try like
Code:
with src.sheets(sheet1")
do unitl d = lastrow
set fnd = range("a:a").find(.cells(d, 1)
if not fnd is nothing then fnd.offset(,1) = .cells(d, 2)
d = d + 1
loop
end with
this was typed into the browser so may contain typos or code errors, the code before the loops is still required
i would prefer a second sheet object variable for the initial active sheet, rather than just relying on the activesheet object being the current worksheet /book when the code is running, as when you open the second workbook, the sheet in it may then be the activesheet, so your code both loops look at the same worksheeet, you could test for this ?activesheet.parent.name
maybe
before opening the workbook
Code:
set rng = range("a:a")
you could even specify the number of rows range("a2:a" & lastrow)
then
Code:
with src.sheets(sheet1")
do unitl d = lastrow
set fnd = rng.find(.cells(d, 1)
if not fnd is nothing then fnd.offset(,1) = .cells(d, 2)
d = d + 1
loop
end with
Last edited by westconn1; Apr 29th, 2021 at 07:47 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 29th, 2021, 07:39 AM
#7
Thread Starter
Member
Re: How do we remake the program to copy not one value value but with range in column
comiler error syntax error
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
with src.sheets(sheet1")
do unitl d = lastrow
set fnd = range("a:a").find(.cells(d, 1)
If Not fnd Is Nothing Then fnd.Offset(, 1) = .Cells(d, 2)
d = d + 1
Loop
End With
End Sub
-
Apr 29th, 2021, 07:54 AM
#8
Re: How do we remake the program to copy not one value value but with range in column
use code tags when posting code
specify which line contains the error or is highlighted, you still needed an initial value for d
looks like missing a " before sheet1, typo do until, closing bracket after range.find
also i have made some edits to the previous post
too many typos, off to bed
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 29th, 2021, 08:20 AM
#9
Thread Starter
Member
Re: How do we remake the program to copy not one value value but with range in column
compiler error variable not define on line Set Rng = Range("a:a")
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 Rng = Range("a:a")
Set src = Workbooks.Open("D:\school1\age.xlsm", True, True)
lastrow = ActiveSheet.UsedRange.Rows.Count
MsgBox lastrow
t = 2
d = 2
With src.Sheets("sheet1")
Do Until d = lastrow
Set fnd = Rng.Find(.Cells(d, 1))
If Not fnd Is Nothing Then fnd.Offset(, 1) = .Cells(d, 2)
d = d + 1
Loop
End With
End Sub
-
Apr 29th, 2021, 02:03 PM
#10
Re: How do we remake the program to copy not one value value but with range in column
Code:
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
Dim Rng As Range
Set Rng = Range("a:a")
Set src = Workbooks.Open("D:\school1\age.xlsm", True, True)
lastrow = ActiveSheet.UsedRange.Rows.Count
MsgBox lastrow
t = 2
d = 2
With src.Sheets("sheet1")
Do Until d = lastrow
Set fnd = Rng.Find(.Cells(d, 1))
If Not fnd Is Nothing Then fnd.Offset(, 1) = .Cells(d, 2)
d = d + 1
Loop
End With
End Sub
-
May 3rd, 2021, 04:58 AM
#11
Thread Starter
Member
Re: How do we remake the program to copy not one value value but with range in column
compile error variable not define
Set fnd = Rng.Find(.Cells(d, 1))
-
May 4th, 2021, 03:50 AM
#12
Re: How do we remake the program to copy not one value value but with range in column
do you want to post a workbook (zip first) with some sample data to test against
Last edited by westconn1; May 4th, 2021 at 04:33 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 7th, 2021, 08:02 AM
#13
Thread Starter
Member
Re: How do we remake the program to copy not one value value but with range in column
-
May 12th, 2021, 05:55 AM
#14
Re: How do we remake the program to copy not one value value but with range in column
Code:
Sub Insertdata()
Dim src As Workbook
Dim fnd As Range
Dim d As Integer
Dim lastrow As Integer
Dim Rng As Range
Set src = Workbooks.Open("c:\temp\dr\age.xlsm", True, True)
' Set src = Workbooks("age.xlsm")
Set Rng = src.Sheets("sheet1").Range("a:a")
d = 1
With ThisWorkbook.Sheets("sheet1")
lastrow = .UsedRange.Rows.Count
Do Until d = lastrow
Set fnd = Rng.Find(.Cells(d, 1))
If Not fnd Is Nothing Then .Cells(d, 3) = fnd.Offset(, 2)
d = d + 1
Loop
End With
End Sub
this is tested to work without error and appears to work as required, but you should test properly
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|