That's almost it Joel, but not quite. I wish I was better at these
copy/paste-b/w-sheet-things!! Not sure why the data shows like that in the
view; when I pasted it into the window to upload to the discussion group, it
displayed fine!! Anyway, here's my data layout:
Columns E:G
Row1 AA BB CC
Row2 5 1 9
Row3 4 6 2
Row4 3 7 8
Columns A:C
Row 5 AA -5 -4 -3
Row6 BB -1 -6 -7
Row7 CC -9 -2 -8
In A1:D9 I want to see this:
A 5 BB -5
A 1 AA -4
A 9 CC -3
B 4 CC -1
B 6 AA -6
B 2 CC -7
C 3 AA -9
C 7 BB -2
C 8 CC -8
Notice, there are some blank cells! I was thinking of something like this:
For Each Cell In Range("a1:j10" & ActiveSheet.UsedRange.Rows.Count)
If Cell.Value < "" Then
' code...
End If
Next
.. . . But theres a little more to it than just that! Ultimately, there
will be MANY rows and many columns. So I think I need to test for something
like this: If Cell.Value < "" Then
Also, I think I need something like this:
'Assume start position is 1,1
lngLastRow = wsSheet1.Cells(Rows.Count, "A").End(xlUp).Row - 1
lngLastCol = wsSheet1.Cells(1, Columns.Count).End(xlToLeft).Column
Somehow, I think I need to test for cells with values, or the final result
will have lots of blanks, right. The data come from a (crazy) query. The
upper right hand quadrant has data and the lower left hand quadrant has data
(the mirror image of the upper right hand quadrant); the upper left hand
quadrant contains blanks and the lower right hand quadrant contains blanks.
Make sense?
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"joel" wrote:
The way you posted the data I'm a little confused where the data is
located. See if you can modify this code to do what you want to do.
Sub Movedata()
Set SourceSht = Sheets("sheet1")
Set DestSht = Sheets("sheet2")
FirstRow = 1 'the row where A is located
SecondRow = 4 'the row where AA is located
RowOffset = SecondRow - FirstRow
NewRowCount = 1 'row where data starts in destination sheet
With SourceSht
For RowCount = FirstRow To (SecondRow - 1)
FirstRowHeader = .Range("A" & RowCount)
SecondRowHeader = .Range("A" & (RowCount + RowOffset))
For ColCount = 2 To 4
FirstData = .Cells(RowCount, ColCount)
SecondData = .Cells(RowCount + RowOffset, ColCount)
With DestSht
.Range("A" & NewRowCount) = FirstRowHeader
.Range("B" & NewRowCount) = FirstData
.Range("C" & NewRowCount) = SecondRowHeader
.Range("D" & NewRowCount) = SecondData
NewRowCount = NewRowCount + 1
End With
Next ColCount
Next RowCount
End With
End Sub
--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197490
http://www.thecodecage.com/forumz
.