find anywhere in A and B anything from C

h1h · Jun 21st 2006

Re: find anywhere in A and B anything from C

the fixroutine uses row 2 for temporary storing of data. I took row 2 because in my sample of the file this row is not used for anything else. Of course any other location may be used. Just let me know where you can leave room for it. In my sample row 4 is used for headers.

Once I know where to go for temporary storing i will adjust the macro to also not tamper with the formatting of rows 5 onward.

saban123 · Jun 21st 2006

Re: find anywhere in A and B anything from C

I have managed to do something similar here is the code

Code

Sub fixZaseden()
Application.Calculation = xlCalculationManual


For i = 6 To Range("j65536").End(xlUp).Row
    If Cells(i, 10) = "Zaseden" And Cells(i, 7) < Cells(i - 1, 7) Then
        Range(Cells(i, 1), Cells(i, 7)).Copy Destination:=Cells(4, 1)
        Range(Cells(i - 1, 1), Cells(i - 1, 7)).Copy Destination:=Cells(i, 1)
        Range(Cells(4, 1), Cells(4, 7)).Copy Destination:=Cells(i - 1, 1)
        Range(Cells(4, 1), Cells(4, 7)).ClearContents
    Range(Cells(4, 1), Cells(4, 7)).Interior.ColorIndex = 55
    End If
Next
Application.Calculation = xlAutomatic
r = Range("c65536").End(xlUp).Row
Range("a5:G" & r).Select
Rows("4:" & r).Select
For i = 5 To r
Rows(i).Select
    If i Mod 2 = 0 Then
        Selection.Interior.ColorIndex = xlNone
    Else
        Selection.Interior.ColorIndex = 36
    End If
Next


End Sub

Display More

In my instance row 3 is used for headers and in row 2 I have buttons and in row 1 there is the name of the group (in cell A1)
so I used row 4 for temporary data

So when do you think should this fixroutine not be used??
Do you think I have amended this fixroutine sub correctly cause I get pretty descent results altough I havent tested it good yet

h1h · Jun 21st 2006

Re: find anywhere in A and B anything from C

looks good to me except that the two range....select behind each other are useless. delete the first one.

there might be instances where moving one Zaseden line up produces Zaseden for the one moved down. Maybe that is not wanted.
And of course it will not solve the issue where in order to finish one task another task would have to be interrupted.

Since your file now looks a little different to mine you might want to upload yours so in future we both have the same again.

saban123 · Jun 21st 2006

Re: find anywhere in A and B anything from C

So maybe I should consider something when this happens(moving zaseden up)
and other-already assigned document is interupted , to delete this last entry if "Zaseden" appears what do you think

Procedure should look for last document that was assigned and deletes it and use sort and fix routine again (The ideas is like this when distributor sees "Zaseden" He should press a button and delete last entry- document that was assigned when "Zaseden" appeared do you think this could work maybe

I will upload you my file but it is around 10megs with all four groups formating and stuff so I must somehow decrease the size or could I send u the file by some other way (FTP-maybe...)

Thnx man for all your help there aint much people like u left in this world
thnx again

h1h · Jun 21st 2006

Re: find anywhere in A and B anything from C

if you strip down the file to one group only you can maybe zip it. If not I will stick to what I have got. You could actually strip it to "sheet aaa" since it is this what seems to defer the most. And may translate the headers for me to understand them.

deleting a line via a button is no problem as such. But do you expect such a macro to know which task was the last one entered. Mind the sort routine might have shuffled the rows so it is not necessairily the buttom one.

I'll think about splitting tasks when I get time to do so.

saban123 · Jun 21st 2006

Re: find anywhere in A and B anything from C

I was thinking the same how could macro know which was the last entry since the sort method and fix routine shufled all the rows

But maybe this could help u I am adding records via form with textboxes on it and maybe we could put all the values from this textboxes to a variable and preserve it till the next document is added is that possible?

I will stripp and zip my file and post it as soon as possible
thnx

saban123 · Jun 21st 2006

Re: find anywhere in A and B anything from C

sory it was a mistake I am getting delirious from this workload

I thought it has failed to do simple calculation but I did a mistake not our project -actually it is more yours than mine

i will upload my file today or tomorow the latest

saty cool
saban

saban123 · Jun 22nd 2006

Re: find anywhere in A and B anything from C

I got an idea how to delete last record if it produces "Zaseden" lets say we give it a tracking number in column T from 1-2000 and then macro should sort this tracking nr together with rest of the row (A to G and T) so user can input this nr let say into text box and macro deletes row where this tracking nr. resides and resorts everything again

Is that good idea or not??
thnx

saban123 · Jun 22nd 2006

Re: find anywhere in A and B anything from C

I just dont get it I stripped my examplweof almost everything condidtional formating colors unecesary buttons, all other sheets except one etc and it is still 2 mb big
And if I zip it with max deflate it is still 300k

Any ideas why?

find anywhere in A and B anything from C

Participate now!

Share