Excel VBA - Get data from 2nd workbook

[Jim O]

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("B65536").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

 

[Frank Kabel]

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

 

[Jim O]

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