Mismatch or Range error

Question

The code below gives either Mismatch or Range error in Excel 2008. How do I fix it?

Sub PEC()
    Dim PEC As String, result As Integer
    PEC = Range("AE2:AE26848").Value    
    If PEC = "A.06" Then result = 1    
    Range("AO2:AO26848").Value = result
End Sub

What are you trying to do? You cannot get a value from a range greater than one cell. — MatthewHagemann, Jun 19 '14 at 19:48
I am trying to have "1" put into a cell if "A.06" is in a different column, if that makes sense? — user3757898, Jun 19 '14 at 19:52

score 0 · Answer 1 · answered Jun 19 '14 at 20:00

0

Sub PEC()
    For x = 2 to 26848
        If Range("AE" & x) = "A.06" Then Range("AO" & x) = 1
    Next x
End Sub

answered Jun 19 '14 at 20:00

MatthewHagemann

1,167
2
9
20

score 0 · Answer 2 · answered Jun 19 '14 at 20:14

I recommend using the following code. It might seem more complicated, but it certainly does a better and more robust job. It is simply assigning your input and output ranges as SrcRng and DstRng. FIND method for ranges is a good way to check for specific values.

Sub PEC()

Dim SrcRng As Range
Dim DstRng As Range
Dim rcell As Range

Set SrcRng = Range ("AE2:AE26848")
Set DstRng = Range("AO2:AO26848")

Set rcell = SrcRng.Find(what:="A.06", after:=SrcRng.Cells(1, 1), _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)

If Not rcell Is Nothing Then
    DstRng.Value = 1
End If

End Sub

Mismatch or Range error

2 Answers2