My loop doesn't seem to work for all cells

[hitch_hiker]

The loop I have just written seems to only for the first 9 cells, obviously I did it wrong

Dim RS As Worksheet
Dim LastRow As Long
    rl = 9
Set RS = Sheets("product list") 
 
'there is code here which works and has no bearing the problem
 
 
With RS
       ' Range("e1:e").Select
          LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
          
            For Each cell In .Range("e9:e" & LastRow)
                If Range("e" & rl) <> 3 Then Range("o" & rl) = 1
                    rl = rl + 1
                Next cell
                
          End With

the range starts at e9, though there is no data in e1:e8, so there is no reason why I can't start the range at e1, if it will make matters work

this is my second loop ( the first one didn't work so I just changed every cell ), but in this case I have an expanding list, so I have no choice but to do it as a loop

 

[taurean]

Hi,

From the looks of it, you want to put 1 in col O if corresponding col E value is not equal to 3. Is it correct?

Are there any blank cells in column E? If yes, how do you want to handle them?

 

[hitch_hiker]

thanks taurean,
yes, if "e" is not 3 then "o" = any type of data so for convience we'll call it "1", the reasoning is, the category 3 is taxable, and I'm using an if qualifier ( if data exists then item is exempt ). The range is blank e1:e8, e9:last row always has numerical value ( no more than 2 digits ) then 1 blank cell then a numerical , I have no blank trapping , but, do I need one, would 0 not read as " not 3 ", the sheet is being updated by importing data from another program and therefore I have no control over it

 

[taurean]

Hi,

Try this code and see if it works as you need:

Public Sub AddOneToO()
Dim lLastRow As Long
Dim wsRS As Worksheet
Set wsRS = Sheets("product list")
With wsRS
    lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("O9:O" & lLastRow)
            .Formula = "=IF(E9<>3,1,"""")"
            .Value = .Value
        End With
End With
End Sub

For Next would be:

Public Sub AddOneToO1()
Dim RS As Worksheet
Dim LastRow As Long, rl As Long
Set RS = Sheets("product list")
With RS
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For rl = 9 To LastRow
        If Range("E" & rl) <> 3 Then Range("O" & rl) = 1
        Next rl
End With
End Sub

And For Each would be:

Public Sub AddOneToO2()
Dim RS As Worksheet
Dim LastRow As Long
Dim rng As Range
Set RS = Sheets("product list")
With RS
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For Each rng In .Range("E9:E" & LastRow)
            If rng.Value <> 3 Then rng.Offset(, 10).Value = 1
        Next rng
End With
End Sub

 

[hitch_hiker]

Thnaks Shrivallabha,

the first example you gave results in cells 1 to 9 in column o with "1" in them , data starts a 9, the next one did not give a result, and the last one gave me "1" in seemingly random columns and cell 1 to 8,

So I wonder what is in mine and your first example which limits the results to the first 9 rows, currently I have 126 rows

 

[hitch_hiker]

in my example the variable LastRow equates to 1, I think this is where the problem lies

 

[hitch_hiker]

Thanks Shrivallabha,

I've solved the problem, the problem was because of the leading zeros in the range (e1:e8)which I could not avoid , however I had a column which did not have leading zeros so I got the "LastRow" from that column then everything works
thanks for your help
Best Regards
Russell