Run Time Error 91

[Brett.Ferguson]

Hi, I am new to excel and am getting the error message Object variable or With block variable not set, I do not know what this means. If someone could explain why this may be occurring that would be great. Thanks

Brett

 

[Jonmo1]

Welcome to the board..

It could be any one of several problems...

Can you post the relevant code, and tell us which line is highlighted when you click Debug..

 

[Brett.Ferguson]

or i = 0 To 23 'hours
For j = 0 To 59 'minutes
If rec1secdata(0, i, j, 0).blnValid = True Then 'Valid minute
With rec1minData(i, j)
For col_cnt = 12 To 13 'on each 1 second data column
col_cnt1 = 88
For k = 0 To 59 'seconds

'get 60 second average for both wind speed readings

'60 second sum for wind speed 1
avg1 = _
avg1 + rec1secdata(0, i, j, k).sngCol(12)

'60 second average for wind speed 1
avg1 = avg1 / 60

'60 second sum for wind speed 2
avg2 = _
avg2 + rec1secdata(0, i, j, k).sngCol(13)

'60 second average for wind speed 1
avg2 = avg2 / 60

'Get the value for the numerator of the correlation coefficient equation
SumNumerator = SumNumerator + _
((rec1secdata(0, i, j, k).sngCol(12) - avg1) * _
(rec1secdata(0, i, j, k).sngCol(13) - avg2))

'Get the value for the first half of the denominator of the correlation coefficient equation
SumDenominator1 = SumDenominator1 + _
(rec1secdata(0, i, j, k).sngCol(12) - avg1) ^ 2

'Get the value for the second half of the denominator of the correlation coefficient equation
SumDenominator2 = SumDenominator2 + _
(rec1secdata(0, i, j, k).sngCol(13) - avg2) ^ 2

'Getiing the correlation coefficient for each 60 second data period
Correl = SumNumerator / Sqr((SumDenominator1 ^ 2) * _
(SumDenominator2 ^ 2))

Next
Next 'Col_cnt
End With
End If
Next 'Min
Next 'hour


The avg1 line is highlighted

 

[Brett.Ferguson]

Also how can i post code so it looks the same way it does in the VBA editor?

 

[Jonmo1]

It looks like it's expecting rec1secdata to be returing a range, but it's not..

I think we need to see the codes for
rec1secdata and rec1minData

And possibly an explaination of what this code is meant to do...

 

[Jonmo1]

Also how can i post code so it looks the same way it does in the VBA editor?

Use code tags...

[#code]
Your code goes here
[#/code]

Without the # symbols

 

[Brett.Ferguson]

Alright, I will try and explain what my code is supposed to do. My code is being added to a massive excel spreadsheet that takes in thousands of lines of data and I need my section to generate a correlation coefficient but I was told I can not use the standard excel formula as we take in 1 second data of wind speed and I need to calculate a correlation coefficient for each minute of data.

Here is the code again..

    Dim avg1 As Range
    Dim avg2 As Range
    Dim SumNumerator As Range
    Dim SumDenominator1 As Range
    Dim SumDenominator2 As Range
    Dim Correl As Double
    
    For i = 0 To 23             'hours
        For j = 0 To 59         'minutes
            If rec1secdata(0, i, j, 0).blnValid = True Then    'Valid minute
                With rec1minData(i, j)
                    For col_cnt = 12 To 13 'on each 1 second data column
                        col_cnt1 = 88
                            For k = 0 To 59     'seconds
                            
                            'get 60 second average for both wind speed readings
                                                    
                            '60 second sum for wind speed 1
                            avg1 = _
                            avg1 + rec1secdata(0, i, j, k).sngCol(12)
                            
                            '60 second average for wind speed 1
                            avg1 = avg1 / 60
                            
                            '60 second sum for wind speed 2
                            avg2 = _
                            avg2 + rec1secdata(0, i, j, k).sngCol(13)
                            
                            '60 second average for wind speed 1
                            avg2 = avg2 / 60
                            
                            'Get the value for the numerator of the correlation coefficient equation
                            SumNumerator = SumNumerator + _
                            ((rec1secdata(0, i, j, k).sngCol(12) - avg1) * _
                            (rec1secdata(0, i, j, k).sngCol(13) - avg2))
                        
                            'Get the value for the first half of the denominator of the correlation coefficient equation
                            SumDenominator1 = SumDenominator1 + _
                            (rec1secdata(0, i, j, k).sngCol(12) - avg1) ^ 2
                            
                            'Get the value for the second half of the denominator of the correlation coefficient equation
                            SumDenominator2 = SumDenominator2 + _
                            (rec1secdata(0, i, j, k).sngCol(13) - avg2) ^ 2
                            
                            'Getiing the correlation coefficient for each 60 second data period
                            Correl = SumNumerator / Sqr((SumDenominator1 ^ 2) * _
                            (SumDenominator2 ^ 2))
                                                     
                        Next
                    Next    'Col_cnt
                End With
            End If
        Next    'Min
    Next    'hour