Setting values as ranges

[aa2000]

Hi all

In my current code, I require two ranges to make a comparison of their values. The ranges are being defined by two cells which vary depending on the range size.

How can I set rngcomp1 and rngcomp 2 to be based around the values of pr1, pr2, w and y?

Currently I get runtime error 1004, about the range having a global failure, which I believe is due to the range command not accepting values that aren't actual cells. What alterations can I make to the code to set the two ranges as these 4 values?

Sub Compare1()
 
Dim pr1 As Integer
Dim pr2 As Integer
Set Rng1 = Range("A1:A1000")
Set Rng2 = Range("D1:L65536")
 
For p1 = 1 To 1000
 
If Cells(pr1, 1).Value = "EXAMPLE" Then
 
 
        For pr2 = 1 To 1000
        If Cells(pr2, 10).Value = Cells(pr1, 1).Value Then
 
 
            Dim Str1 As String
            Dim Str2 As String
        
 
            w = Cells(p1, 1).End(xlDown).Address
           
            x = Cells(w).End(xlToRight).Offset(0, -2)
 
            y = Cells(p2, 10).End(xlDown).Address
 
            z = Cells(y).End(xlToRight).Offset(0, -2)
 
 
                Set rngcomp1 = Range("pr1:w")
                Set rngcomp2 = Range("pr2:y")
                For Each Cell In rngcomp1
                Str1 = Str1 & Cell.Value
                Next
                For Each Cell In rngcomp2
                Str2 = Str2 & Cell.Value
                Next
                If Str1 = Str2 Then
                Cells(pr2, 10).Offset(0, 1) = "OK"
                Else
                Cells(pr2, 10).Offset(0, 1) = "NOT OK"
                End If
        End If
 
        Next pr2
    End If
 
Next pr1
 
End Sub

Any help would be greatly appreciated guys!

Thanks

 

[Scott Huish]

I'm not sure why you are using strings for ranges, but:

The & w has to go outside the quotes:

Set rngcomp1 = Range("pr1:" & w)

What is this code supposed to do?

 

[aa2000]

The ranges being compared are being turned into strings for the comparison to be made easier, hence the use of ranges as strings. This may not be the best method for doing this, but its one that I've found to work so far.

Thanks for the hints, I'll try it out tomorrow at work!

This code is part of a much larger macro which pulls text files containing experimental data from 3 projects (university research project) puts them in separate excel sheets and compares results from the three experiments. This code is to make the comparison between data, and well if you look at my previous posts you can see some parts of the other code if you're interested

Thanks

 

[cb12]

I'm not sure why you are using strings for ranges, but:

The & w has to go outside the quotes:

Set rngcomp1 = Range("pr1:" & w)

What is this code supposed to do?

It seems to me that pr1 is just a number between 1 and 1000 so it should probably be something like:

Set rngcomp1 = Range("A" & pr1 & ":" & w)

 

[aa2000]

It seems to me that pr1 is just a number between 1 and 1000 so it should probably be something like:

Set rngcomp1 = Range("A" & pr1 & ":" & w)

Thanks cb12! I realised this was also a problem whilst looking back over the code, but now at least I can rectify this as well. Cheers mate!