Help with Macro

[rwmill9716]

This macro pulls sequential pairs of data in workbook "Raw Data", contrasting Col A there against the other 9 columns.

Workbook "Calculated Results" shows its last pair of data columns in Cols B and C. Row 7 data in Cols E through I are copied into Cols J through N for each sequential pair. The last (the 1-10 pair) is shown in yellow.

In looking at the data in Cols J through N, it's obvious that some of the pairs are incorrect (2nd, 3rd and 7th). What's wrong with the code?

How would I change the code to generate all possible pairs (45 with 10 columns of data), i.e., include those not contrasted against the 1st col in "Raw Data", the 2-3 pair?

How do I place my macro here so that contained in the normal coded box I see others using?




Sub Partiton_Pairs()
Dim LastCol As Long, Col As Long, LastRow As Long
Dim rData As Range

Application.ScreenUpdating = False
With Sheets("Raw Data")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rData = .Range("A1").Resize(LastRow, LastCol)
End With
With Sheets("Calculated Results")
For Col = 2 To LastCol
.Range("B6").Resize(LastRow).Value = rData.Columns(1).Value
.Range("C6").Resize(LastRow).Value = rData.Columns(Col).Value
.Range("B6").Resize(LastRow, 2).Sort Key1:=.Range("C7"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Application.EnableEvents = False
Range("J" & Rows.Count).End(xlUp).Offset(1) = Range("E7").Value
Range("K" & Rows.Count).End(xlUp).Offset(1) = Range("f7").Value
Range("L" & Rows.Count).End(xlUp).Offset(1) = Range("G7").Value
Range("M" & Rows.Count).End(xlUp).Offset(1) = Range("H7").Value
Range("N" & Rows.Count).End(xlUp).Offset(1) = Range("I7").Value
Application.EnableEvents = True

Excel 2003
	A	B	C	D	E	F	G	H	I	J
1	AMC in CHMD	ACNin CHMD	ADN in CHMD	BHMT in CHMD	CAT in CHMD	DAMP in CHMD	DCH in CHMD	H2O in CHMD	HMD in CHMD	HMI in CHMD
2	1	2	3	4	5	6	7	8	9	10
3	102.189	80.62272	1	0.649508	9.681225	125.0444	1481.306	4.202341	94.76657	1404.508
4	103.5135	76.83823	1	0.65	9.658118	122.9461	1461.109	4.148652	94.75135	1403.865
5		70.91728	1	0.649375	9.635011	121.3683	1457.201	4.107331	94.79455	1402.796
6	101.5	64.5	1	0.645	9.611904	122	1514.5	4.11	94.805	1424.5
7		59.90933	1	0.634173	9.588796	124.2299		4.138106	94.79582	1439.393
8	110.3554	66.77461	1		9.565689		1592.323	4.274846	94.70161
9		74.62096	1	0.544938	9.542582	141.4286	1595.335	4.410558	94.60653	1343.134
10	121.5152	78.09104	1	0.54	9.519475	133.6965	1563.877	4.456062	94.57424	1351.486
Raw Data

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1										Correlation	Pair
2		ctrl p								-0.10548	1	2	AMC in CHMD	ACNin CHMD
3										0.282809	102.189	1	AMC in CHMD	ADN in CHMD
4										0.434564	121.5152	0.54	AMC in CHMD	BHMT in CHMD
5										-0.10567	1	5	AMC in CHMD	CAT in CHMD
6		AMC in CHMD	HMI in CHMD		Correlation	Pair				-0.06107	1	6	AMC in CHMD	DAMP in CHMD
7		1	10		-0.968	1	10	AMC in CHMD	HMI in CHMD	0.147066	1	7	AMC in CHMD	DCH in CHMD
8			1343.134							0.256135	0	4.107331	AMC in CHMD	H2O in CHMD
9		121.5152	1351.486							-0.10679	1	9	AMC in CHMD	HMD in CHMD
10			1402.796							-0.9677	1	10	AMC in CHMD	HMI in CHMD
11		103.5135	1403.865
12		102.189	1404.508
13		101.5	1424.5
14			1439.393
15		110.3554
16
Calculated Results

 

[L. Howard]

For the easy part of containing the code.

Without the *'s type in [*code*] then Enter to give yourself a space, then type [*/code*].
Copy and paste in between the two code tags.

Regards,
Howard

 

[rwmill9716]

Thanks, L, but I do not see two code tags. Is the first one "Code:"

 

[AlphaFrog]

See my signature block about code tags.

Not sure I follow but notce that the code for copying the calculated values are referencing the ActiveSheet and not the With Sheets("Calculated Results")

Add periods e.g.

.Range("J" & Rows.Count).End(xlUp).Offset(1) = .Range("E7").Value
[COLOR=#ff0000].[/COLOR]Range("K" & Rows.Count).End(xlUp).Offset(1) = [COLOR=#ff0000].[/COLOR]Range("f7").Value
[COLOR=#ff0000].[/COLOR]Range("L" & Rows.Count).End(xlUp).Offset(1) = [COLOR=#ff0000].[/COLOR]Range("G7").Value
[COLOR=#ff0000].[/COLOR]Range("M" & Rows.Count).End(xlUp).Offset(1) = [COLOR=#ff0000].[/COLOR]Range("H7").Value
[COLOR=#ff0000].[/COLOR]Range("N" & Rows.Count).End(xlUp).Offset(1) = [COLOR=#ff0000].[/COLOR]Range("I7").Value

Or just this...

.Range("J" & Rows.Count).End(xlUp).Offset(1).Resize(, 5) = .Range("E7:I7").Value

 

[L. Howard]

ADVERTISEMENT

No it is not Code: that 's just the way shows up if done corectly, as you see with AlphaFrog's post. And read his signature block, he has an example and I have used it here exactly as it is written in his block.

your VBA code here

You do a [ type the word code and do another ]

For the bottom one do a [/ type the word code and do another ]

When it is posted you see Code: followed by the shaded box with your stuff in it and nothing below.

Somewhere there is a test post site but I can never find it when I want to.

Perhaps you could just try it here in your thread to get you going.

Howard

 

[rwmill9716]

Thanks, Howard, I was missing the brackets.

  ......

 

[L. Howard]

You got it. You are welcome.

H