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

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/

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