Relational Macro in 2003

[Hightechtoo]

I see in Excel 2007...there is an option of using "relative references" in a macro.

Is there any way to do that in 2003?

 

[Scott Huish]

I don't have XL2007, what are you trying to do exactly?

 

[Hightechtoo]

I have a whole lot of data in this format....

name
title
company
address
address
address
email

and want to move it into this format....
name title company address address address email

An earlier poster gave me the great response to use edit, copy.....then paste special....all and transform.....which does a great job and will save a lot to typing.

Unfortunatly...I have to do that on every single one of the records. I can't highlight the entire column and do it or I would have one long string of records going across the sheet.

I can record a macro that will edit, copy, paste special, all and transform BUT......It only does it on the range where the macro was recorded.

I see in the Mr. Excel Book.....(page 28)....in Excel 2007....there is an option in the macro menu to use "relative references". I'm assuming that I could go to the next record....hit the macro and have the macro run on the NEW record, but I don't seem to have that option in 2003.

right?

 

[Scott Huish]

Couldn't say, is every record comprised of 7 rows? And what row does the data start in?

 

[Hightechtoo]

No.....some records have 5 rows....others 6....and others 7.
But that's not really a problem....because I can have the macro use the maximum number of rows.....and it'll get the whole record.


All the records are currently in Column A

 

[Scott Huish]

OK, so they do all use 7 rows but some of the rows may be blank, is this correct?

 

[Scott Huish]

If that's the case, perhaps this, this assumes data starts in row 1, and will do all of the records in Column A.

Sub test()
Dim lastrow As Long, c As Long, d As Long
lastrow = -Int(-Range("A" & Rows.Count).End(xlUp).Row / 7) * 7
For c = 1 To lastrow Step 7
    d = d + 1
    Range("A" & c).Resize(7).Copy
    Range("B" & d).PasteSpecial Transpose:=True
Next
Application.CutCopyMode = False
End Sub

 

[tlk123]

Hi

CopySpecial Transpose works on multiple columns.

Experiment a little - I got it to work.

You may need to get same length of rows (fields).

Terryl

 

[Scott Huish]

I don't think the op has multiple columns, I think the op has multiple records in a single column that needs to be broken out into multiple columns, like this:

<b>Like This:</b>

Excel Workbook
	A
1	name
2	title
3
4	address
5	address
6	address
7	email
8	name2
9	title2
10
11	address2
12	address3
13	name
14
15
16	title
17
18	address
19	address
20	address
21	email
Sheet

<b>To This:</b>

Excel Workbook
	B	C	D	E	F	G	H
1	name	title		address	address	address	email
2	name2	title2		address2	address3	name
3		title		address	address	address	email
Sheet

 

[Hightechtoo]

hotpepper has got it.....

Multiple records in column a......

I can use the paste special.....transpose on each record individually.
I can create a macro to do ONE record....but when I run the macro...it just does that same ONE record again.

I want to go to the first field in the record....and run the macro and have it do THAT record. Make sense?

I see that Excel 2007 has an option for what I think I need....a "relational macro".


I just didn't know if there was anything like that in 2003.

Thanks for all your help.