ignore the cell

the below code works only if it is in middle of the cell for example
for i = 1 to 35
if it is 10th cell k value 0 or """" it moveup one cell
but if it is last cell 35th cell k value = 0 it go to end of the file
it is not going to QUIT

if aSheet.Cells(i, "J").value = 0 AND aSheet.Cells(i, "K").value = 0 OR aSheet.Cells(i, "J").value = "" AND aSheet.Cells(i, "K").value = "" THEN
else If aSheet.Cells(i, "D").text <> "" AND aSheet.Cells(i, "E").text <> "" then
aSheet.Rows(i).Delete Shift:=xlUp
Else
QUIT

End if
End if

Quote:

---

if aSheet.Cells(i, "J").value = 0 AND aSheet.Cells(i, "K").value = 0 OR aSheet.Cells(i, "J").value = "" AND aSheet.Cells(i, "K").value = "" THEN

---

if this is true it just goes to next
i have no idea what quit does, but it only goes there if the both the if and else if are false

westconn

ifthe k cell has 0 or empty
it will skipp the row and go to next row
in middle
at the same time if the cell has 0 or empty but other rows are not empty
ithe macro go to end
how do i skip the entire row if k cell value or j cell value = 0 or empty
i tried aSheet.Row(i) .delete.xn.Up
it delete the row and move to next row
but i dont want to delete the row only move to next row
please help me

i

all your code is checking one row at a time

what does quit do? i don't know any quit

this is the code i find out from this form which has skip the row after find
if the cell k value or j value has 0 or "" but other cells from c to i not= ""
how do i do this?


Set c = .Find(MyInput, LookIn:=xlValues, SearchOrder:=xlRows)
If Not c Is Nothing Then
Count = Count + 1: ff = c.Address
k = c.Row
Redim Preserve wsName(1 To Count): wsName(Count) = ws.Name
Redim Preserve rng(1 To Count): rng(Count) = c.Address(0, 0)
Do
Set c = .FindNext(c)
If c.Address = ff Then
Exit Do
ElseIf c.Row <> k Then
k = c.Row
Count = Count + 1
Redim Preserve wsName(1 To Count): wsName(Count) = ws.Name
Redim Preserve rng(1 To Count): rng(Count) = c.Address(0, 0)
End If
Loop
ff = Empty
End If

i am more confused now about what you are trying to do, than i was before

Westconn
sorry for confusion
i am trying to copy from excel to screen
if the coloumn k or j value = 0 or empty that particular row ignore next row will copy to screen
i am finding the end of the row is if the column c is empty and column k or column j or empty or 0 also the last row
that'what i am trying to do

post some sample sheet, showing which rows you want to use and which you want to skip and how you determine last row

Westconn
first of all thanks a lot for taking time. Attach excel sheet ignore row 20, 21,23, 25,26 becasue k valure and j value have 0 or blank
i am haveing example of becasue maximum is 1000 records

for i = 14 to 1000
last row i am finding this way
If aSheet.Cells(i, "D").value = "" AND aSheet.Cells(i, "E").text = "" then
GoTo Last

End if

Last:
MyScreen.MoveTo 16,2
MyScreen.Sendkeys ("Quit")
MyScreen.SendKeys ("<Enter>")
i am attaching the sample sheet.

i will look at this tonight

what do you mean, "Go to last"?

westconn
thank you very much. go to last means it goes to here and macro will be done after enter quit

Last:
MyScreen.MoveTo 16,2
MyScreen.Sendkeys ("Quit")
MyScreen.SendKeys ("<Enter>")

vb Code:

cnt = 0
For i = 14 To 1000
    If (asheet.Cells(i, "J").Value = 0 Or asheet.Cells(i, "J").Value = "") And (asheet.Cells(i, "K").Value = 0 Or asheet.Cells(i, "K").Value = "") Then
        Else
        cnt = cnt + 1  ' do your stuff here, this counted that 8 rows would be processed in your sample sheet
    End If
    'last row i am finding this way
    If asheet.Cells(i, "D").Value = "" And asheet.Cells(i, "E").Value = "" Then
        GoTo last
    End If
Next
last:
Stop  ' put you closing code here, this happened at row 27 in your sample sheet (remove Stop)

i tried this one and it is not ignoreing the row it still wait for amount

emptu row is ignoring but if the amout 0 is not ignoring

it worked correctly when i tested it as it counted 8 rows, which is correct
i added a debug.print for the row number

Quote:

---

14 15 16 17 18 19 22 24

---

ye sit is working but what happedn it is ignoring j and k value = 0 or empty
but if j = 0.00 and k value for example 300.00 and k value 0.00 amd j value has 300.00 that two rows ignoring

you didn't ask for that............ lol
try
if val(asheet.Cells(i, "J").Value) = 0 AND val(asheet.Cells(i, "k").Value) = 0 then

if there is text in cell it will still have val = 0

are you using
asheet.Cells(i, "J").Value = 0
or
asheet.Cells(i, "J").Value = "0"

Wesstconn
thanks once again.
i use
asheet.Cells(i, "J").Value = 0
and
asheet.Cells(i, "J").Value = "0"
or
asheet.Cells(i, "J").Value = 0
and
asheet.Cells(i, "J").Value = "0"
or

asheet.Cells(i, "J").Value = ""
and
asheet.Cells(i, "J").Value = ""
the above one i used i tried =0 wihtout "" ALSO

temp = aSheet.Cells(i, "J").value = "" AND aSheet.Cells(i, "K").value = ""
temp1 = aSheet.Cells(i, "K").value = 0 AND aSheet.Cells(i, "J").value = "0
if temp or temp1 then
else

the last will not work if the one is "" and the other 0
post another sample sheet where the problem exists

Westconn
thanks for your time.
i changed the code as
For i = 14 To 1000
If (asheet.Cells(i, "J").Value = 0 AND asheet.Cells(i, "K").Value = "") OR (asheet.Cells(i, "K").Value = 0 Or asheet.Cells(i, "K").Value = "") THEN
If asheet.Cells(i, "D").Value = "" And asheet.Cells(i, "E").Value = "" Then GoTo last
END IF
' all my code here
NEXT i

i tried the above code it copies 14, 15, 21, 22 only it ignores 16, 17
i attach the sample sheet for you

westconn
it has to pick up only rows are 14,15,16,17 21,22 only
but it picksup only 14,15,21,22 only
.

your and & ors are mixed up

vb Code:

For i = 14 To 1000
If asheet.Cells(i, "D").Value = "" And asheet.Cells(i, "E").Value = "" Then GoTo last
If (asheet.Cells(i, "J").Value = 0 Or asheet.Cells(i, "j").Value = "") And (asheet.Cells(i, "K").Value = 0 Or asheet.Cells(i, "K").Value = "") Then
    Else
    Debug.Print i;
    ' all my code here
End If
Next i
last:

Quote:

---

14 15 16 17 21 22

---

also you can change the goto last to exit for, will take you out of the loop

we have long week end. i will try it on tuesday and let you know
thanks

I have no luck i tried. it shows 14,15,21,22
after 15 it shows debut 21 only

wesconn
If (asheet.Cells(i, "J").Value = 0 Or asheet.Cells(i, "J").Value = "") And (asheet.Cells(i, "K").Value = 0 Or asheet.Cells(i, "K").Value = "") Then
else
debug: print1
if i have first row j value = 0.00 and k value 300.00
and second row j vlaue ='' and k vlaue 300.00
debut showing thrid row only
immeidalty it goes to end if
how come it goes end if
the query shows j = 0 and k = 0

without seeing your sheet i can't tell, but i guess because D & E are empty

the code worked correctly on the sample sheet you posted, all i can do is test the code on sample sheet with error, so far the code works correctly each time, when i test

westconn
the cell is hide column L
do i have to mention in if clause
i will try it tomorroe and let you know
thanks westconn

i don't think column L being hidden will have any effect on the above code