Ignoring cells in a range/array

[chrgrose]

I have the script:

Sub Numbers()
Dim r As Range, c As Range, i As Currency
Set r = Range("E2210:EHQ3408")
For Each c In r
    For i = 1 To 1800
        If c.Value >= i / 10 And c.Value <= i / 10 + 0.1 Then
            Cells(Rows.Count, i + 3650).End(xlUp).Offset(1).Value = c.Offset(-1811).Value
            Exit For
        End If
    Next i
Next c
End Sub

Which works well but is very slow because about 80% of the values in the range will never satisfy the condition and have a value of "-1".

Does anyone know how to have VBA determine which cells In the array "r" have the value of -1 so that they are ignored, making the script much faster?

Thank you.

 

[VoG]

Try changing the -1s to blanks or text then

Sub Numbers()
Dim r As Range, c As Range, i As Currency
Set r = Range("E2210:EHQ3408")
For Each c In r.SpecialCells(xlCellTypeConstants, xlNumbers)
    For i = 1 To 1800
        If c.Value >= i / 10 And c.Value <= i / 10 + 0.1 Then
            Cells(Rows.Count, i + 3650).End(xlUp).Offset(1).Value = c.Offset(-1811).Value
            Exit For
        End If
    Next i
Next c
End Sub

 

[chrgrose]

Hm.. I tried this but as given it seems to be stuck (is processing but seems to be not finding anything) and doesn't stop. Even more odd I tried using

.SpecialCells(xlNumbers)

instead of

.SpecialCells(xlCellTypeConstants, xlNumbers)

and it tells me that there are no cells were found! I changed the data in the range to be "NA" for cells to ignore but all other cells have numbers only (eg. "85.49791"). The formating is set to "general" for all cells and not "numbers" but I don't think that should matter.

 

[VoG]

The syntax that I gave you is correct.

Does this find the right cells - it should colour the number cells yellow

Sub test()
Dim r As Range, c As Range, i As Currency
Set r = Range("E2210:EHQ3408")
For Each c In r.SpecialCells(xlCellTypeConstants, xlNumbers)
    c.Interior.ColorIndex = 6
Next c
End Sub

 

[chrgrose]

The syntax that I gave you is correct.

Does this find the right cells - it should colour the number cells yellow

Sub test()
Dim r As Range, c As Range, i As Currency
Set r = Range("E2210:EHQ3408")
For Each c In r.SpecialCells(xlCellTypeConstants, xlNumbers)
    c.Interior.ColorIndex = 6
Next c
End Sub

I ran it and it colors all cells in the range yellow, including the cells with the value NA.

 

[VoG]

I've just noticed what an enormous range you are working with. I think that will cause problems with SpecialCells. Maybe this (change -1 to "NA" if you wish).

Sub Numbers()
Dim r As Range, c As Range, i As Currency
Set r = Range("E2210:EHQ3408")
For Each c In r
    If c.Value <> -1 Then
        For i = 1 To 1800
                    If c.Value >= i / 10 And c.Value <= i / 10 + 0.1 Then
                Cells(Rows.Count, i + 3650).End(xlUp).Offset(1).Value = c.Offset(-1811).Value
                Exit For
            End If
        Next i
    End If
Next c
End Sub

 

[chrgrose]

This works! Thank you. As 76% of the values are skipable and this method is about 4 times faster, it looks like the efficiency is perfect so that the unwanted cells are not involved. A++