Loop with two workbooks
 

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

Loop with two workbooks
 

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

Loop with two workbooks
 

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