Posts by saban123

Re: date format

The problem is with textbox I guess because if I write date in cell it leaves the format like i write it, but when this is added via textbox it switches the format from mm/dd/yyyy to dd/mm/yyyy-this is the one I want in cell

So if I want to put date in cell in format dd/mm/yyyy I must write format mm/dd/yyyy into textbox

Any ideas why

Re: date format

So you say I should write this in that part of code where content of textbox is putted into cell right?

And one more thing how can I apply this to all column (G for example)

Thnx for your help
Saban

I have txtboxes in which I write date in format dd/mm/yyyy but if I want to put date in cell correctly from this textbox in need to declare event on exit -change format to mm/dd/yyyy and then it puts date in desired format which is dd/mm/yyyy and my regional settings are English(uk)

Is there a way to put any date format in textbox and in cell there will always be format dd/mm/yyyy or at least if date is entered in format dd/mm/yyyy that also in cell the same format is putted

Thnx

Re: Sort A to G and S columns

why do i get error type mismatch at

Set lMax = WorksheetFunction.[COLOR="Red"]Max[/COLOR](Columns("H:H"))

Re: Sort A to G and S columns

I used another aproach in Cell S5 I wrote Large(H5:H2000) and then pased value of this cell to a variable like max = Range("S5").Cells

But you method is more elegant but the results are the same
Thnx for all your help

saban

Re: Sort A to G and S columns

I have another question how to find the bigest value in column H (integer value) and passed it to a variable

Thnx

Re: Sort A to G and S columns

Guys if i insert new column excel will change all the formulas coresponding to this column right?

So I dont have to rewrite all the formulas
Am I right

thnx

Re: Sort A to G and S columns

But this will delete content in G?? I dont want to do that

Re: Sort A to G and S columns

But in column G or H I already have some formulas written that means that I must rewrite some formulas coresponding to this columns is there some other way if not I will do like this

thnx for your explanation
saban

Re: Sort A to G and S columns

Yeah that is right i dont wont to sort columns from H to R

Just from A to G and S

thnx guys for all your help

Re: Sort A to G and S columns

Can you please tell me how cause I wrote
s= range("S5:S200").cells

but it is not working
thnx

r = Range("c65536").End(xlUp).Row 
Range("a5:G" & r).Select 
 
Selection.Sort Key1:=Range("G5"), Order1:=xlAscending, Key2:=Range("F5") _ 
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending, Header:= _ 
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ 
xlSortNormal 
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 
Range("j5").Select 
Application.Calculation = xlCalculationAutomat

this sorts range from A to G and set color to each second row how can I include S column in this range
I mean that cells in column S are moved with cells from columns A to G
Any help would be apreciated
thnx

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?

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

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

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

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

Re: find anywhere in A and B anything from C

I have managed to do something similar here is the 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

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

Re: find anywhere in A and B anything from C

it works pretty ok it is just that I have this small square in row 2 and this fix routine always copy it to cells where I put data in it does this fix routine copy something from row 2 but I dont get why cause there is nothing important for calculation there ? and somehow it messes with conditional formating and messes the look of tables second row why is that

Is it possible not to copy content of row 2 into cells and row 2 is always cleared from its formatting would it be possible to deal with that
Is something wrong if I copy content of first cell "zaseden" into 4 row and not in second??????
thnx
saban

Re: find anywhere in A and B anything from C

thnx now it worked(I managed to download it)

Is this fix routine runs with button or does it runs automaticly when value is changed or written. You say it should work correctly now?

thnx for everything
I will let u know how it worked

does this new routine works only on sheet aaa maybe??

This example has still =IF(G5="";"";I5-C5) in R column is this OK or did U accidentally upload the wrong one
I cant figure out what have you changed in this example could u plizz briefly explain what have u changed??
but somehow it works with this data I will test it with other and let you know

thanks for all your help