rng no work correct...

[sal21]

Why in this part of code in the first CEL have "FIL." and not the
first cell in filter????
In this case 4500 when SOMMA is =0 ????
(start Sub SPOSTA_LINEE_ZERO())

If SOMMA = 0 Then
For Each CEL In RNG1
RIGA = CEL.Row
If CEL = SPORTELLO And Range("D" & RIGA).Value = SOSPESO Then
WS.Range("AA" & RIGA) = "ZERO"
WS.Range("AA1") = WS.Range("AA1") + 1
End If
Next CEL

here the file:
http://www.mytempdir.com/1247401

 

[Jay]

Hi Sal21-

In your orignal code, the object RNG1 refers to a range that includes the
header row cell ("FIL.") as its 'first' value. Therefore, the value "FIL."
is assigned to CEL in the the first iteration of the statement "For each CEL
in RNG1".

One way to start assignments to CEL that begin with data (instead of the
header field "FIL."), would be to resize the range RNG1 before the
For...Each statement. Here is a suggested modification to do that:

1. Delete (or comment out) the following statements in:
With WS.AutoFilter.Range
Set RNG1 = .Columns(12).SpecialCells(xlCellTypeVisible)
FROWS = RNG1.Count
End With

2. And replace them with the following 4 statements at the same location:
Set RNG1 = RNG1.Columns(12).Cells
Set RNG1 = RNG1.Offset(1, 0).Resize(RNG1.Cells.Count - 1, 1)
Set RNG1 = RNG1.SpecialCells(xlCellTypeVisible).Cells
FROWS = WorksheetFunction.CountA(RNG1)

 

[sal21]

Jay work perfect!
Tks.

Hi Sal21-

In your orignal code, the object RNG1 refers to a range that includes the
header row cell ("FIL.") as its 'first' value. Therefore, the value "FIL."
is assigned to CEL in the the first iteration of the statement "For each CEL
in RNG1".

One way to start assignments to CEL that begin with data (instead of the
header field "FIL."), would be to resize the range RNG1 before the
For...Each statement. Here is a suggested modification to do that:

1. Delete (or comment out) the following statements in:
With WS.AutoFilter.Range
Set RNG1 = .Columns(12).SpecialCells(xlCellTypeVisible)
FROWS = RNG1.Count
End With

2. And replace them with the following 4 statements at the same location:
Set RNG1 = RNG1.Columns(12).Cells
Set RNG1 = RNG1.Offset(1, 0).Resize(RNG1.Cells.Count - 1, 1)
Set RNG1 = RNG1.SpecialCells(xlCellTypeVisible).Cells
FROWS = WorksheetFunction.CountA(RNG1)

--
Jay







- Mostra testo tra virgolette -