Macro Compiling Problem

rwmill9716

Active Member
Joined
May 20, 2006
Messages
448
I am using the following macro on two Windows 8 based computers with no problems. This macro pulls columns of data from tab "Washed Raw Data" (all possible pairs), writes them to tab "Calculated Results" (one pair at a time) then calculates their correlation coefficient and various other id parameters in Cols J throug N which dynamicly add rows.

When I upload the Excel sheet (2010 versuib) and try to use it on two other computers, I get a compile error where Col1 is highlighted in the 11th line of code (For Col1 = 1 To LastCol).

Any suggestions would be appreciated.

Thanks,

ric

Code:
 Sub Partiton_Pairs()
    Dim LastCol As Long, Col As Long, LastRow As Long
    Dim rData As Range
    
    Application.ScreenUpdating = False
    With Sheets("Washed 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 Col1 = 1 To LastCol
        For Col = Col1 + 1 To LastCol
            .Range("B6").Resize(LastRow).Value = rData.Columns(Col1).Value
            .Range("C6").Resize(LastRow).Value = rData.Columns(Col).Value
                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
            
        Next Col
      Next Col1
      
    End With
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps you need to Dim Col1

Rich (BB code):
    Dim LastCol As Long, Col As Long, LastRow As Long
    Dim rData As Range
    Dim Col1 As Long
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,199
Messages
5,835,936
Members
430,396
Latest member
dzifna

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
Top