macro help for rearranging data

msampson

Board Regular
Joined
Mar 9, 2004
Messages
129
Office Version
  1. 365
Platform
  1. MacOS
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
ABCDEFGHIJ
1time11112131415161718
2time22122232425262728
3time33132333435363738
411time44142434445464748
512time55152535455565758
613time66162636465666768
714time7
815time8
916time9
1017time10
1118time11
12time12
1321time13
1422time14
1523time15
1624time16
1725time17
1826time18
1927time19
Sheet1


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

If thats right then this should work for you


Code:
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
 
Upvote 0
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
?????
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,730
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top