macro help for rearranging data

[msampson]

Hi all
I'm trying to set up a macro to rearrange data from a microtiter plate reader.

There is an array of 12 columns (A-L) and 8 rows (starting with 4-11) that represents the wells in the plate. This same array is repeated 100 times with a blank row between each iteration. This represents each reading at increasing time points.
I have recorded a macro to add 12 worksheets to my workbook. I then copied each column A thru L onto a separate sheet in column A. Also on the starting sheet is a row (3) that is 100 columns wide containing the time of the read. I copied that and pasted it transposed into column B on each of the 12 worksheets that correspond to each column on the plate.
What I need to do now is to take each set of 8 rows (all in column A on each different worksheet) and transpose them into 8 columns (C-J) so that each row becomes each different time point indicated in column B.

Is that clear? Can you help please?
Thanks from a rookie macro person
Maureen

 

[GaryB]

Ok this is pretty lousy sample data for an example, but is this what your after? (It's getting late this side if the pond!)

Book1
	A	B	C	D	E	F	G	H	I	J
1		time1	11	12	13	14	15	16	17	18
2		time2	21	22	23	24	25	26	27	28
3		time3	31	32	33	34	35	36	37	38
4	11	time4	41	42	43	44	45	46	47	48
5	12	time5	51	52	53	54	55	56	57	58
6	13	time6	61	62	63	64	65	66	67	68
7	14	time7
8	15	time8
9	16	time9
10	17	time10
11	18	time11
12		time12
13	21	time13
14	22	time14
15	23	time15
16	24	time16
17	25	time17
18	26	time18
19	27	time19
Sheet1

The data starts of in columns A& B, and gets copied to C-J

If thats right then this should work for you

Sub Macro3()

Dim i As Integer
   For i = 1 To 100
    j = 9 * (i - 1) + 4
   Range("A" & j, "A" & j + 7).Copy
   Range("C" & i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Next i
End Sub

Hope this helps

GaryB

 

[msampson]

what you show in the sample data is correct but when I try to run that macro I get a syntax error on the line For i= 1 To 100
?????

 

[GaryB]

I cannot understand that at all. As far as I can see there's nothing wrong with the line (there's hardly a lot of it to go wrong!). I've just copy/pasted the macro back off the board and onto my test sheet and it runs without any problem. Try recopying it, maybe something got mangled in the pasting.

Failing that try stepping through the macro (use F8) and make sure it is that line that's causing the problem, but I really can't see how!

HTH

GaryB

 

[msampson]

I erased it and closed out the files. Opened the file again, opened the vba editor and pasted the code into a new black code window and tried it again. If I run my recorded macro first, then choose one of the pages with the 2 columns of data and try to run it I get the syntax error. I opened a new workbook and copied just the two columns of data and tried it and got the same thing. This time, nothing is highlighted but all the text is red except the line
Range("A" & j, "A" & j + 7).Copy
is in black and the first two and last line are in dark blue.

Sorry to be so obtuse - I'm just not that experienced in macros.

 

[GaryB]

Ok, let's make sure the code is in the right place.
Open your VB editor.
On the left (usually!) there is the Project Explorer Window (it's title bar probably says "Project-VBA Project")
Select your spreadsheet in there - if it's got a + next to it click on that to expand it. Have you got a Modules folder at the bottom? (If not go to the Insert menu and select Module.
Modules should now have a Module 1 in it) Double click on Module 1 and copy paste your macro in there.
Try running it from there. Make sure you have a suitable sheet of your workbook active arrange the windows so you can see both Excel and the VB editor and you can step through the macro using F8.

HTH

GaryB

 

[msampson]

nope.
compile error:
syntax error.

I tried both pasting it in the module window after the recorded macro and on a separate workbook by itself (with the data arranged as if by the recorded part).

Ummm, I'm working on a Macintosh if that makes a difference? A G5 dp with OS X. I have both Excel X and Excel 2004 available. I've been trying this with Excel X so far.

 

[GaryB]

I've never used Excel on a Mac.(actually I haven't used a Mac for years) I wouldn't have thought that something as basic as a For Next loop would have different syntax, but it might be worth checking in the help files in VBA.
I have no idea if it will make a difference, but it can't do any harm to try it in 2004. Beyond that I'm afraid I run out of ideas.

I've posted up a help request on http://www.mrexcel.com/board2/viewtopic.php?p=466561#466561 It would probably be worth you putting a watch on that thread as well, in case some answers come in before I get a chance to look again. Someone on the Board is bound to know the answer.
Sorry I can't be much help


GaryB

 

[GaryB]

Maureen,
have you tried running this on your 2004 Excel version yet? MarkW has managed to run the code and so has a friend of mine I sent my test sheet to.
If you need to run this on a your 98 version, can you tell me what the help file says about For..Next loops and I may be able to adjust the syntax to suit.

Cheers

GaryB

 

[msampson]

It doesn't run (for the same reason) on Excel 2004, Excel 98 or I even tried it on our Citrix server which should be PC Excel. I even got the syntax error on the Citrix Excel which looks to be Excel 2002.

Is there a different way of writing the code?
Maureen