Search & insert

[Ticotion]

Hi

I'm new to programming in Excel and have some problems.

I have an excel sheet in workbook1 where in cell A1 I have a name eg. Jhon
Jonsson.

In cell c4 I have an estimated workload eg. 75% and in cell C5 real workload
eg. 100%. In cell F2 I have a week number eg. 50.

I want excel to search for the name in coloumn A range 16 to 35 in another
excel workbook2. After that is has to find the week number in row 15. If it
finds a match it should input the estimated workload from workbook1 into the
corrisponding field in workbook2 and the real workload in the corrisponding
field. The layout of workbook2 is like this:

Est. Workload Actual workload Est. Workload
Actual workload
Week 1
2
John Johnson
name2
name3
etc.

Could you help?

Br
Ticotion

 

[Ticotion]

Hi Joel

This almost works as I want. One thing is though that in row 13 in the
destsht the code has to find the Estworkload or the realworkload, then have
to find the weekno, and then insert the value that corrisponds to to row13
headlines. How whould this be done?

Br.
Ticotion

 

[Ticotion]

Hi

I found the solution. Very simple just added

..Cells(c.Row, c1.Column) = EstWorkLoad
to the code

Would it be possible to write to a closed excel workbook? An how could this
be done in the code?

Thank you for your help

Ticotion

Hi Joel

This almost works as I want. One thing is though that in row 13 in the
destsht the code has to find the Estworkload or the realworkload, then have
to find the weekno, and then insert the value that corrisponds to to row13
headlines. How whould this be done?

Br.
Ticotion

Modify as required


Sub MoveData()

Set sourcesht = Workbooks("Book1.xls").Sheets("Sheet1")
Set DestSht = Workbooks("Book2.xls").Sheets("Sheet1")

With sourcesht
Person = .Range("A1")
EstWorkLoad = .Range("C4")
RealWorkLoad = .Range("C5")
WeekNum = .Range("F2")

End With

With DestSht
Set c = .Range("A16:A35").Find(what:=Person, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Set c1 = .Rows(15).Find(what:=WeekNum, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c1 Is Nothing Then
.Cells(c.Row, c1.Column) = EstWorkLoad
End If
End If
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=163315

Microsoft Office Help

.

 

[Ticotion]

Hi Joel

Thank you for your help. Is is possible to update the DestSht (destination
excel file) if it is closed? How can this be done?

I use the following code which is your original code modified slightly:

Function MoveData()

Set sourcesht = Workbooks("SIM overview TEST.xls").Sheets("Sheet1")
Set DestSht = Workbooks("Activity overview1.xls").Sheets("Sheet1")

With sourcesht
Person = .Range("A1")
Estworkload = .Range("C4")
Realworkload = .Range("C5")
WeekNum = .Range("F2")
End With

With DestSht
Set c = .Range("A15:A34").Find(what:=Person, _
LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

Set c1 = .Rows(14).Find(what:=WeekNum, _
LookIn:=xlValues, lookat:=xlWhole)

If Not c1 Is Nothing Then
..Cells(c.Row, c1.Column) = Estworkload
..Cells(c.Row, c1.Column + 1) = Realworkload

End If
End If
End With


End Function

Thank you

Ticotion