# Posts by ashu1990

• ## Open workbooks and save as csv

Re: Open workbooks and save as csv

use

Code
``````''at start

''at end
• ## Condense two SQL UPDATE statements into one.

Re: Condense two SQL UPDATE statements into one.

this should do the work...

SQL
``````UPDATE [tblShortageBoards]
SET [ysnPriority] =  (case when [dtmDueDate] > DATE() + 5 then FALSE
when [dtmDueDate] <= DATE() + 5 then TRUE
end)
where [dtmDueDate] > DATE() + 5 or [dtmDueDate] <= DATE() + 5``````
• ## Random Times

Re: Random Times

[INDENT]This formula will give you a random time between 8 AM and 4 PM

=(RAND()+1)/3

If you want a generic version....

=RAND()*(end-start)+start

and you might want to put some rounding in there to round to the nearest minute like

=ROUND((RAND()+1)/3*1440,0)/1440 [/INDENT]

• ## sort rows and color code them

Re: sort rows and color code them

• ## Excel VBA match copy paste append

Re: Excel VBA match copy paste append

rahul welcome to the forum...

if this post relates you in some how refer a link of this post into your post.

• ## Automatically COPY+PASTE SUM of cells without copying formula

Re: Automatically COPY+PASTE SUM of cells without copying formula

click on go advance and the button after smiley is the attachment button....

• ## Automatically COPY+PASTE SUM of cells without copying formula

Re: Automatically COPY+PASTE SUM of cells without copying formula

Quote

put this in D2 and drag down till your last row of data
=Sum(D2,H2,L2,O2,R1,U2,X2)

sorru for the confusion i mean to say put the formula in Z2 not D2 it will give you only vales not the formula of the columns...

• ## Automatic unprotect sheets

Re: Automatic unprotect sheets

ok use a input box to take the password in like

Code
``````dim password as string

• ## excell missing dates

Re: excell missing dates

see your post #1 you posted date as 22/12/14 which is in text format so i have to get the date other way around now your post #5 says date as 27 Dec 14 this means the dates are not in text if so use this, see if the date captured in DAT is as per your date in excel....

Code
``dat = activecell.value``
• ## excell missing dates

Re: excell missing dates

where is your date column it should be in "A" and the data should start from A2..

• ## sort rows and color code them

Re: sort rows and color code them

replace this

Code
``Cells(lRow, 1).Interior.ColorIndex = Dict.Item(strName)``

with this

Code
``Range(Cells(lRow, 1), Cells(lRow, 5)).Interior.ColorIndex = Dict.Item(strName)``
• ## sort rows and color code them

Re: sort rows and color code them

have you tried the code which derk provided it seems to be working fine as per your scenario

• ## excell missing dates

Re: excell missing dates

• ## Automatic unprotect sheets

Re: Automatic unprotect sheets

use the same code you used for protecting the sheets and the one for protecting the workbook but difference remain in .protect and .unprotect !

• ## how to convert into excel.

Re: how to convert into excel.

try this!
it will connect your html page with excel and even you can refresh it if you want the data to be refreshed..

• ## sending email by command button

Re: sending email by command button

Happy new Year Cytop seen you after a long Time!

• ## Automatically COPY+PASTE SUM of cells without copying formula

Re: Automatically COPY+PASTE SUM of cells without copying formula

put this in D2 and drag down till your last row of data

=Sum(D2,H2,L2,O2,R1,U2,X2)

• ## how to convert into excel.

Re: how to convert into excel.

you want to create a macro that converts the HTML pages into excel data or a excel data linked with this pages.

• ## sending email by command button

Re: sending email by command button

didn't get your question as no color seems to apper into the code part..

any ways there are different approaches which can be followed like

Code
``````if Sheets("Invoice").TextBox6.Value = "" then  '<--- if blank

elseif Sheets("Invoice").TextBox6.Value <> "" then '<--- if not blank

end if``````
• ## Transfer Data between Excel and Word

Re: Transfer Data between Excel and Word

why do you want to open a word file to make the user type the letter....

instead why can't you use textbox...