About Us

Patrick Molloy

glad to have helped :)

"Volker Hormuth" wrote in message
...
Hello Patrick,

many thanks for your help. The code runs as desired.
The code I had postet I use to put on form tables. These are saved in an
other step as Workbooks.
I ask my error to excuse.

Volker

"Patrick Molloy" schrieb im Newsbeitrag
...
your transpose was incorrect - the code you gave never populated the
RegionA sheet as shown in the first mail. This code corrects that.
How it works
we look at each row of the data table in sheet Daten. starting at row 2,
we move down each row until theres nothing in column A

for each row, we identify the region sheet by the name in column A then
put the 4 values from B,C,D and E into the region sheet, set by the
pattern described.

Option Explicit
Sub RegionUpdaten()
Dim Daten As Worksheet
Dim Region As Worksheet
Dim RegionRow As Range
Dim RegionIndex As Long
'initialise
Set Daten = Worksheets("Daten")
RegionIndex = 2
Do Until Daten.Cells(RegionIndex, 1) = ""

Set RegionRow = Daten.Rows(RegionIndex)
Set Region = Worksheets(RegionRow.Range("A1").Value)
With RegionRow
Region.Cells(1, 1) = .Range("A1")
Region.Cells(2, 2) = .Range("B1")
Region.Cells(3, 2) = .Range("C1")
Region.Cells(5, 2) = .Range("D1")
Region.Cells(6, 3) = .Range("E1")
End With
RegionIndex = RegionIndex + 1
Loop
MsgBox "Job Done"
End Sub

"Volker Hormuth" wrote in message
...
Hi Patrick,
again many thanks for your help.
I have replaced the line. Nevertheless, there comes the same error
message. Do you have one more tip?
Volker

"Patrick Molloy" schrieb im Newsbeitrag
...
sorry - there's a typo

Set F = Worksheets(D).Cells(rw, "A"))

"Volker Hormuth" wrote in message
...

"Patrick Molloy" schrieb im Newsbeitrag
...
Set D = Worksheets("Daten")

dim rw as long

rw = 2
do until D.cells(rw,"A") =""
Set F = Worksheets( D.cells(rw,"A") )

i = 2

Do While Not IsEmpty(D.Cells(i, 1))
F.Cells(1, 1) = D.Cells(i, 1)
F.Cells(2, 2) = D.Cells(i, 2)
F.Cells(5, 2) = D.Cells(i, 3)
F.Cells(6, 3) = D.Cells(i, 4)
i = i + 1
Loop

rw=rw+1
loop

MsgBox "Job Done"
End Sub

Hello Patrick,

many thanks for your quick response. As soon as I the programme
execute I get an error message:
Runtime error 13 - types unacceptable - Typen unverträglich
line: Set F = Worksheets(D.Cells(rw, "A"))

What must I change?
Thank you very much!
Volker

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Update Formula Loop	RedFive	Excel Programming	5	November 5th 08 08:30 AM
For To / Next loop doesn't update...	TFriis	Excel Programming	2	May 20th 08 11:50 AM
returning back to loop check condition without completing the loop	ashish128	Excel Programming	13	April 3rd 08 12:53 PM
update range in For loop	David	Excel Programming	23	April 17th 06 10:39 PM
Advancing outer Loop Based on criteria of inner loop	ExcelMonkey	Excel Programming	1	August 15th 05 05:23 PM

Menu

About Us