Serial numbers and ZEROS

[emukiss10]

Hello,

I have a problem with disapearing zeros..

my formula so far to copy proper numbers to other sheet is:

Dim cellR As RangeDim lastRowR As Long, r As Long


lastRowR = Range("J" & Rows.Count).End(xlUp).row
r = 2


For Each cellR In Sheets(1).Range("J1:J" & lastRowR)
     If Len(cellR) = 9 Or Len(cellR) = 14 Then
        cellR.EntireRow.Copy Sheets("SERIAL").Cells(r, 1)
        r = r + 1
    End If
Next

the problem is that the files that I need to work on have values in column J looking like this:

="00000000"
="029384756"
="000776819"
="284758933"

I need to copy entire rows to my new sheet if value in column J has 9 or 14 characters. The formating makes things difficult.

we can strip those numbers of ="" but than excel is removing leading zeros.

Anybody know how to bypass this?


Best Regards
W.

 

[emukiss10]

How can I get two criteria in one For from different column like:

For Each cell1 In Sheets(1).Range("H1:H" & lastRow1)
If cell1.Value = "3" Or cell1.Value = "4" AND column Q <> "100.0%" Then
cell1.EntireRow.Copy Sheets("BLOOP").Cells(i, 1)
i = i + 1
End If
Next

 

[Scott T]

Try

Note by putting "" around the 100.0% you are turning it into text. The text 100.0% <> the number 1 (100% when formatted as a percent). I am assuming that you have a number in column Q formatted as a percent.

lastRow1 = Cells(Rows.Count, "H").End(xlUp).Row
i = 2
For Each cell1 In Sheets("Sheet5").Range("H1:H" & lastRow1)
 If (cell1.Value = "3" Or cell1.Value = "4") And Cells(cell1.Row, 17) <> 1 Then
 cell1.EntireRow.Copy Sheets("BLOOP").Cells(i, 1)
 i = i + 1
 End If
 Next

 

[emukiss10]

Thanks, and no, it'a a text "100.0%"