Posts by ashu1990

Re: Open workbooks and save as csv

use

''at start
application.displayalerts = false


''at end
application.displayalerts = true

Re: Condense two SQL UPDATE statements into one.

this should do the work...

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

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]

Re: sort rows and color code them

glad it helped

Re: Excel VBA match copy paste append

rahul welcome to the forum...

please post your own question and do not reply on the post which are closed....

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

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

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

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...

Re: Automatic unprotect sheets

ok use a input box to take the password in like

dim password as string


password = inputbox("Please Enter Password")


sheet.unprotect password

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....

dat = activecell.value

Re: excell missing dates

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

Re: sort rows and color code them

replace this

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

with this

Range(Cells(lRow, 1), Cells(lRow, 5)).Interior.ColorIndex = Dict.Item(strName)

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

Re: excell missing dates

your dates are in text format so had to go around....

Sub test()
Dim DAT As Date
Range("A2").Select
DAT = Mid(ActiveCell.Value, 4, 2) & "/" & Left(ActiveCell.Value, 2) & "/" & Right(ActiveCell.Value, 2)
i = 0
n = 2
Do While Range("A" & n).Value <> ""
If Format(DateAdd("D", i, DateValue(DAT)), "DD/MM/YY") <> Range("A" & n).Value Then
Range("A" & n).EntireRow.Insert
Range("A" & n).Value = Format(DateAdd("D", i, DateValue(DAT)), "DD/MM/YY")
Range("B" & n).Value = Format(DateAdd("D", i, DateValue(DAT)), "DDD")
End If
i = i + 1
n = n + 1
Loop


End Sub

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 !

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..

Sub test()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.google.com/finance/getprices?q=BATAINDIA&i=3600&p=1d&f=d,o,h,l,c,v" _
        , Destination:=Range("$A$1"))
        .Name = "getprices?q=BATAINDIA&i=3600&p=1d&f=d,o,h,l,c,v"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Re: sending email by command button

Happy new Year Cytop seen you after a long Time!

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)

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.

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

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


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


end if

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...