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
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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 do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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.
Last edited by india123; May 15th, 2008 at 07:35 AM.
Reason: wrong excel sheet
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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 do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
it worked correctly when i tested it as it counted 8 rows, which is correct
i added a debug.print for the row number
14 15 16 17 18 19 22 24
Last edited by westconn1; May 16th, 2008 at 03:59 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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"
Last edited by westconn1; May 17th, 2008 at 12:21 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
the last will not work if the one is "" and the other 0
post another sample sheet where the problem exists
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
Last edited by india123; May 18th, 2008 at 09:06 AM.
Reason: change the excel sheet
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:
14 15 16 17 21 22
also you can change the goto last to exit for, will take you out of the loop
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i don't think column L being hidden will have any effect on the above code
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Reply With Quote
