Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Get data from 2nd workbook
Need help with code below keep getting "Script out of range message.
HC.xls Target File HCE Worksheet Column "B2:B65536" range with code # code # format = #####.## - ## - ### HC_DB.xls Source File M_DATA Worksheet Column "B2:B65536" range with Code # code # format = #####.## - ## - ### Goal : match Code# in HC with code# in HC_DB & return offset value t HC 'From HC.xls Private Sub CommandButton1_Click() Dim cl As Range Workbooks.Open Filename:="C:\LKUP\HC_DB.XLS" Application.Windows("HC.XLS").Activate ' 'GET DATA For Each cl I Workbooks("HC").Worksheets("HCE").Range("B2" Workbooks("HC").Worksheets("HCE").Range("B65536"). End(xlUp).Address) If Trim(cl.Offset(1, 0).Text) Workbooks("HC_DB").Worksheets("M_DATA").Range("B2" Workbooks("HC_DB").Worksheets("M_DATA").Range("B65 536").End(xlUp).Address _ Then Wit Workbooks("HC").Worksheets("HCE").Range("B65536"). End(xlUp).Offset(1 0).Value = cl.Value ' 'LOCATION ON SHEET2 FROM LOCATION O SHEET1 .Offset(0, 1).Value = cl.Offset(0, 1).Value .Offset(0, 2).Value = cl.Offset(0, 4).Value .Offset(0, 3).Value = cl.Offset(0, 6).Value .Offset(0, 4).Value = cl.Offset(0, 7).Value .Offset(0, 5).Value = cl.Offset(0, 8).Value End With End If Next cl Workbooks("HC_DB.XLS").Close SaveChanges:=False End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Get data from 2nd workbook
Hi
in which line do you get the error Maybe the Offset statement do not work as the left side is not using an object reference -- Regards Frank Kabel Frankfurt, Germany Need help with code below keep getting "Script out of range message. HC.xls Target File HCE Worksheet Column "B2:B65536" range with code # code # format = #####.## - ## - ### HC_DB.xls Source File M_DATA Worksheet Column "B2:B65536" range with Code # code # format = #####.## - ## - ### Goal : match Code# in HC with code# in HC_DB & return offset value to HC 'From HC.xls Private Sub CommandButton1_Click() Dim cl As Range Workbooks.Open Filename:="C:\LKUP\HC_DB.XLS" Application.Windows("HC.XLS").Activate ' 'GET DATA For Each cl In Workbooks("HC").Worksheets("HCE").Range("B2", Workbooks("HC").Worksheets("HCE").Range("B65536"). End(xlUp).Address) If Trim(cl.Offset(1, 0).Text) = Workbooks("HC_DB").Worksheets("M_DATA").Range("B2" , Workbooks("HC_DB").Worksheets("M_DATA").Range("B65 536").End(xlUp).Addre ss) _ Then With Workbooks("HC").Worksheets("HCE").Range("B65536"). End(xlUp).Offset(1, 0).Value = cl.Value ' 'LOCATION ON SHEET2 FROM LOCATION ON SHEET1 Offset(0, 1).Value = cl.Offset(0, 1).Value Offset(0, 2).Value = cl.Offset(0, 4).Value Offset(0, 3).Value = cl.Offset(0, 6).Value Offset(0, 4).Value = cl.Offset(0, 7).Value Offset(0, 5).Value = cl.Offset(0, 8).Value End With End If Next cl Workbooks("HC_DB.XLS").Close SaveChanges:=False End Sub --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Get data from 2nd workbook
The first line below 'get data
I think it has to do win Dim Array Dim Cell Look at attachment code source file attache Attachment filename: hc_db.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54754 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to: Open closed workbook/Search data tables/Return data to open workbook | Excel Discussion (Misc queries) | |||
save excel as data only to a new workbook using VB | Excel Discussion (Misc queries) | |||
copy excel workbook to new workbook without data | Excel Worksheet Functions | |||
looking up data from an other excel workbook | New Users to Excel | |||
Open a password protected excel workbook from second workbook to fetch data using dynamic connection | Excel Programming |