Hi.

My problem is how to properly run a loop in one workbook
that is referring to cells in another workbook. I know
how to run loops when I am in only one workbook, but once
I open another workbook, the macro references will point
to the active workbook.

In the sample code below, I want to run a loop from
LastCell to LastRow in Demos.XLS. Inside this loop, I
need an IF section of code that will search for a string
in another workbook. If the value is found, then the
values in Column 1,2,and 3 of that same row need to be
copied to columns 2,3, and 4 of row "i" in the first
workbook . Any help will be greatly appreciated!

Thanks,
Mike.

Dim LastRow As Long
Dim LastCell As Long
Dim IP As String
LastRow = Cells(Rows.Count, 10).End(xlUp).Row
LastCell = Cells(Rows.Count, 1).End(xlUp).Row - 1
Workbooks.Open Filename:="C:\Documents and
Settings\Mike\My Documents\IP Addresses.xls"
For i = LastCell To LastRow ' in Demos.xls
IP = Cells(i, 5)
'I need an IF section here that looks the IP value up
in
'Column 4 of The IP Addresses workbook. If the value
is found, then
'the values in Column 1,2,and 3 of that same row need
to be copied to
'columns 2,3, and 4 of row "i" in the first workbook
(Demos.xls).
Next i

Private Sub LookIntoIPAddresses ()

Dim LastRow As Long, LastCell As Long
Dim FirstWb as Workbook
Dim FirstCell as Range, IPRg as Range, IPCell as Range

'Pls note I have changed your IP as String Variable to FirstCell a
Range

LastRow = Cells(Rows.Count, 10).End(xlUp).Row
LastCell = Cells(Rows.Count, 1).End(xlUp).Row - 1

Set FirstWb = ActiveWorkbook ('your Demos.xls)

Workbooks.Open Filename:="C:\Documents and
Settings\Mike\My Documents\IP Addresses.xls"

'IP Adresses.xls will become active when opened, so the next line o
code sets the variable for a Range WITHIN this Wb, and we'll be able t
look for a value inside this range with no need to activate either W
or Ws

Set IPRg = Columns(4)

FirstWb.Activate

For i = LastCell To LastRow ' in Demos.xls
set FirstCell = Cells(i, 5)

'For each FirstCell in Demos.xls, we look into the IPRg, if foun
values from columns 1-2-3 in IP Addresses.xls are copied to column
2-3-4 in Demos.xls

For Each IPCell in IPRg
If oCell.Value = IP.Value Then
IP.Offset(0, -3).Value = oCell.Offset(0, -3).Value
IP.Offset(0, -2).Value = oCell.Offset(0, -2).Value
IP.Offset(0, -1).Value = oCell.Offset(0, -1).Value
End If
Next IPCell

set IP = Nothing
Next i

End Sub


This is untested!! Plus I'm totally self taught and moreover quite ne
to the VBA business. But I've just used something similar in one of m
macros.

Let me know if it doesn't work.
Ta
Etie

--
Message posted from http://www.ExcelForum.com

Actually simplier way to do the For Each loop is: (And also I had lef
an IP as Range variable in there that I'd used earlier!)


For Each IPCell in IPRg
If oCell.Value = FirstCell.Value Then
range(FirstCell.Offset(0, -1), FirstCell.Offset(0, -3)
Range(oCell.Offset(0, -1), oCell.Offset(0, -3)
End If
Next IPCell

Etie

--
Message posted from http://www.ExcelForum.com