Hello Zigman

The problem is word wrap in your news editor.

The code mentioned shall be entered as one line or with a " _ " as
line seperator as I did later in the code.


---
Per

On 7 Jan., 03:34, zigman wrote:
Hello Per Jessen. *First, I want to thank you for taking time to answer my
question. * *
I tried to run the program in the excel vb editor and there is a syntax error
associated with the following line of the program

Set f = Columns("A").Find(what:="G", After:=Range("A" & lastrow),
lookat:=xlWhole, MatchCase:=True)

Do you know what the problem could be?
--
Zigman



"Per Jessen" wrote:
Hi

With the imported data in Sheet 1, this macro will generate a list in sheet2
with the desired data:

Sub TransposeData()
Dim DestSh As Worksheet
Dim TargetSh As Worksheet

Set TargetSh = Sheets("Sheet1")
Set DestSh = Sheets("Sheet2")
DestRow = 2 ' Headings in row 1

TargetSh.Activate

lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set f = Columns("A").Find(what:="G", After:=Range("A" & lastrow),
lookat:=xlWhole, MatchCase:=True)
Debug.Print f.Row
Do
* * DestSh.Cells(DestRow, 1) = Cells(f.Row + 21, 2).Value
* * DestSh.Cells(DestRow, 2) = Cells(f.Row + 23, 2).Value
* * DestSh.Cells(DestRow, 3) = Cells(f.Row + 24, 2).Value
* * DestSh.Cells(DestRow, 4) = Cells(f.Row + 26, 2).Value
* * DestSh.Cells(DestRow, 5) = Cells(f.Row + 47, 2).Value
* * DestRow = DestRow + 1
* * fRow = f.Row
* * Set f = Columns("A").Find(what:="G", After:=Range("A" & f..Row), *_
* * * * lookat:=xlWhole, MatchCase:=True)
Loop Until f.Row = 1
End Sub

Regards,
Per

"zigman" skrev i meddelelsen
...
I am trying to transpose repeating records in excel. *My data is exported
from a Lotus notes database. *I have selected to export and use a "G" to
separate each record. *I need to align similar classes of data in similar
columns so I can map fields when importing the records into Outlook. * *I
am
really only interested in the following data from each record [
streetname,
cityname, statecode, postcode, comname]. *Of course when I exported the
data
from Lotus notes, I got a template with lots of generic data fields in
column
A and the unique data in column B. *Here is a sample of the data.

G
ADRNO
ADR
COMPANYTYPE
ENDFLG 1
$ConflictAction 1
Startup 1
OldLocationCode * E01
PreLocationCode * E01
PreLocationName
Administrator * [Manager]
Readers * [Manager]
SalesPersonNotesID
InsidePersonNotesID
AreaManagerNotesID
UserList
CLDCode
SelectList
ComAdditionalName
LocationCode * E01
LocationName
StreetName * 600 Thomas Drive
POBox
CityName * New York
StateCode NY
StateName New York
PostCode 10999
CountryCode * USA
CountryName * United States
Tel
Fax
CurrencyCode * USD
CurrencyName * US Dollar
LanguageCode * ENG
LanguageName * English
PayMethodCode
PayMethodName
PayTermCode
PayTermName
KeyPerson
SalesPersonCode
SpecialCustomer
InsidePersonCode
AreaManagerCode
WorkerNo
Budget
Authors * ,,
ComName Humphry, Inc.
CustomerFlag 1
BlockFlag
BlockDate
BlockMemo
BlockHistory
Distributor
SalesPerson
InsidePerson
AreaManager
IndustryClass01Code
IndustryClass01Name
IndustryClass02Code
IndustryClass02Name
UpdateDate xxxx
UpdateName xxxx
ComType 1
OldComCode axxxx
PreComCode
ComCode axxxx
$UpdatedBy xxxx

G
ADRNO
ADR
COMPANYTYPE
ENDFLG 1
$ConflictAction 1
Startup 1
OldLocationCode * E01
PreLocationCode * E01
PreLocationName
Administrator * [Manager]
Readers * [Manager]
SalesPersonNotesID
InsidePersonNotesID
AreaManagerNotesID
UserList
CLDCode
SelectList
ComAdditionalName
LocationCode * E01
LocationName
StreetName * One Best Rd
POBox
CityName Lisbon
StateCode OH
StateName Ohio
PostCode 19203
CountryCode * USA
CountryName * United States
Tel * 301-999-3911
Fax
CurrencyCode * USD
CurrencyName * US Dollar
LanguageCode * ENG
LanguageName * English
PayMethodCode
PayMethodName
PayTermCode
PayTermName
KeyPerson
SalesPersonCode
SpecialCustomer
InsidePersonCode
AreaManagerCode
WorkerNo
Budget
Authors * ,,
ComName Lustor, Inc.
CustomerFlag 1
BlockFlag
BlockDate
BlockMemo
BlockHistory
Distributor
SalesPerson
InsidePerson
AreaManager
IndustryClass01Code
IndustryClass01Name
IndustryClass02Code
IndustryClass02Name
UpdateDate
UpdateName xxxxx
ComType 1
OldComCode xxxxx
PreComCode
ComCode xxxxx
$UpdatedBy xxxxx

Can anyone help me?

--
Zigman- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -