Replies

sorry i seem to have duplicated the posting————————————————————————————————————————

the following code is used repatatively to place “yes” in a column in preperation for deleting the line . i would like to be able to increase the function so that i can make the macro run quicker.
—————————————————————————————————————————————————-

wsh.Range(“P4”).FormulaR1C1 = _
“=IF(RC[-15]=33070,””yes””,IF(RC[-15]=33080,””yes””,IF(RC[-15]=33180,””yes””,IF(RC[-15]=33126,””yes””,IF(RC[-15]=33085,””yes””,IF(RC[-15]=33185,””yes””,IF(RC[-15]=33087,””yes””,””””)))))))”
wsh.Range(“P4”).Copy Destination:=wsh.Range(“P5:P6000”)
wsh.Range(“Q4”).FormulaR1C1 = _
“=IF(RC[-16]=33090,””yes””,IF(RC[-16]=33190,””yes””,IF(RC[-16]=33091,””yes””,IF(RC[-16]=33093,””yes””,IF(RC[-16]=33095,””yes””,IF(RC[-16]=33094,””yes””,IF(RC[-16]=33101,””yes””,””””)))))))”
wsh.Range(“Q4”).Copy Destination:=wsh.Range(“Q5:Q6000”)
wsh.Range(“R4”).FormulaR1C1 = _
“=IF(RC[-17]=33099,””yes””,IF(RC[-17]=33097,””yes””,IF(RC[-17]=33150,””yes””,IF(RC[-17]=33135,””yes””,IF(RC[-17]=33136,””yes””,IF(RC[-17]=33100,””yes””,IF(RC[-17]=33105,””yes””,””””)))))))”
wsh.Range(“R4”).Copy Destination:=wsh.Range(“R5:R6000”)
Application.CutCopyMode = False
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate
Range(“A4”).Select
Selection.AutoFilter
Range(“A4:R6000”).Select
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If LCase(Cells(r, 15).Value) = “yes” Then Rows®.Delete
If LCase(Cells(r, 16).Value) = “yes” Then Rows®.Delete
If LCase(Cells(r, 17).Value) = “yes” Then Rows®.Delete
If LCase(Cells(r, 18).Value) = “yes” Then Rows®.Delete
Next r

sorry for the delay in replying but!!
I thank you all for your comments.

it is some times hard to tell one’s peers what one has done without causing them to shake in their boots, but nothing lost, I will endeavour to give a further insite into the reason for and the subsequent programme.

I work in a sales office in which we also have control of the dispatch of all orders taken, the system is so flexible that as shelves are re-stocked with new goods the database is updated, and orders which have reached a certain value are released for packing and delivery.

The system is volatile in as much that the file can have 2400 lines as the attached file or some 7000 line as I have seen in the past 2 days.

I will list the way that I have tried to programme the macro. The file is transferred from a UNIX computer as a CSV file,
Which make it harder to work with?

The manual working of removing all superfluous parts could take 3 hours with interruptions the macro takes approx 1.5 minutes.

1) Remove all accounts on stop——————————————– late payer

hi john i agree with your comments about a book , infact i bought “excel 2000 vba published by wrox” what a wast of money for me , examples not working and the new staff cannot get them to work. can you suggest a good book to buy with lots of worked examples (that do work)

alexanderd

sorry for the delay but ntlworld server has been down and not only that i have brought the excel file but not the latest *.bas file home with me, i will try again tommorow.

further to my request for help .
i have 3 sheets
1) barry 94
2) jim 22
3) ads1
the three sheets have identical programming except for the line
ActiveCell.FormulaR1C1 = _
“=IF(AND(RIGHT(RC[-8],5)=””TOTAL””,RC[-2]>650,RC[-2]650,RC[-2]<750),""YES"","""")"

Range("I4").Select
Selection.copy
Range("I5:I1000").Select
ActiveSheet.Paste
Range("I4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate
Range("A3").Select
Selection.autofilter
Selection.autofilter Field:=9, Criteria1:="YES"
_'########################################################################
If i alter the programme as follows i get a better result which leads me to think i have a timeing problem. cn you help with a solution.
'#########################################################################
Range("A3").Select
Selection.autofilter
Selection.autofilter Field:=9, Criteria1:="YES"
Sheets("jim 22").Select
Range("A3").Select
Selection.autofilter
Selection.autofilter Field:=9, Criteria1:="YES"
Sheets("barry 94").Select
Range("A3").Select
Selection.autofilter
Selection.autofilter Field:=9, Criteria1:="YES"
alex

thank you for all your help

alex

everthing worked fine except for one small point pls read the attached file

thanking you for all you have done so far

iam sorry i havn’t replied sooner but my email server has been down.
i include a short xl file for you

i have three columns A B C
colum a holds “A” holds an account no.
colum b holds an invoiced ammount
i am sub totaling column B . if coolum B has ammounts varying between 10 and 1500 and i wish to subtotal i might get a subtotal of 1510.
in colum “C” i have to check and see if the value is between 1500 and 2000, and copy to a separate sheet. in this case the value of the subtotal is 1510 it could quite easily be 3500 but i would pick up the value of 1500 from colum “B” where as i only want the subtotals and not the individual values.

A B C
12 1500 yes
12 10
12 485

subtotal 1995 yes

yes i agree, but whilst the sub total colum “D” would automatically be placed in the “D” colum. I want to place in colum “F” the resulting value

yes and easy to use but try to get the subtotal in another colum than wehere microsoft want to put itsubtotal should be in column “F” i wish to place it in column “K”

thank you for such a speedy reply. what i am trying to say is that each time the form is used the date is placed in cell “J12” automatically without keying in anything.