I saw the tutorial at
http://www.exceltip.com/st/Using_Loo...Excel/628.html
for looping but I need something a litle bit diffrent.

I want it to look in a file called subject.xls for the first name, go
to main.xls move 3 columns accross i.e. if I start at A it goes to D.

Get the value and paste it in subject.xls at the row of the same
person.

How can I do that?

Thank you for your help in advance.


--
thewebmaster
------------------------------------------------------------------------
thewebmaster's Profile: http://www.excelforum.com/member.php...o&userid=36601
View this thread: http://www.excelforum.com/showthread...hreadid=563428

This is what I have at the moment:


Code:
--------------------

Sub UserForm_Initialize()
Dim i As Integer
Dim SourceWB As Workbook
i = 1

Application.ScreenUpdating = False

Set SourceWB = Workbooks.Open("main.xls", _
False, True)

With Worksheets(1).Range("C:C")
Set c = .Find(Name, LookIn:=xlValues)
c.Activate
c.Value = "Banane"
If Not c Is Nothing Then
MsgBox ("no")

End If
End With
Name = SourceWB.Worksheets(1).Cells(i, 1).Value



SourceWB.Close False

Range("A5").Value = Name



Set SourceWB = Nothing

End Sub



--------------------


--
thewebmaster
------------------------------------------------------------------------
thewebmaster's Profile: http://www.excelforum.com/member.php...o&userid=36601
View this thread: http://www.excelforum.com/showthread...hreadid=563428

OK I managed to fix the loop but it doesn't want to copy the contents o
the cells.

Have a look at this please:


Sub UserForm_Initialize()
Dim i As Integer
Dim SourceWB As Workbook
i = 9

subjnumber = 1 ' change that to the subject number
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("I:\Exam results\main.xls"
False, False) ' change that to where the main file is


For z = 2 To ActiveSheet.UsedRange.Rows.Count
thisname = Sheet1.Cells(z, 1)
MsgBox (thisname)

With SourceWB.Sheets(1)
For i = 9 To i + .UsedRange.Rows.Count
If .Cells(i, 3) = thisname Then
ActiveSheet.Cells(z, 9).Value = .Cells(i, 7
subjnumber)
ActiveSheet.Cells(z, 11).Value = .Cells(i, 64
subjnumber)
Exit For
End If
Next i
End With

Next z
SourceWB.Close False
Set SourceWB = Nothing
End Su

--
thewebmaste
-----------------------------------------------------------------------
thewebmaster's Profile: http://www.excelforum.com/member.php...fo&userid=3660
View this thread: http://www.excelforum.com/showthread.php?threadid=56342