Posts by pradeep_atm

    Re: FInd the intersection


    Hi Malay,


    Intersect method can be used in VB. It's something like this...


    [vba]
    Sub FindIntersection()
    If Intersect(Selection, Range("A10:D10")) Is Nothing Then
    MsgBox "Your Selection is not in the range"
    Else
    MsgBox "Your selection is within the range"
    End If
    End Sub


    [/vba]


    HTH.


    End Sub

    Re: Mail from sheet


    Hi Edwin,


    This should help you going:


    [vba]


    Sub send_mail()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    'If cell.Offset(0, 1).Value = "yes" Then
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = cell.Value
    .CC = cell.Offset(0, 3).Value & ";" & cell.Offset(0, 4).Value & ";" & cell.Offset(0, 5).Value
    .Subject = cell.Offset(0, 8).Value
    .Body = cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
    cell.Offset(0, 6).Value & vbNewLine & vbNewLine & _
    cell.Offset(0, 7).Value & vbNewLine & vbNewLine & _
    " Thanx & Best Regards" & vbNewLine & _
    " Type your name here" & vbNewLine
    .Attachments.Add cell.Offset(0, 2).Value
    .Display ' type send if you want directly to send
    End With
    Set OutMail = Nothing
    ' End If
    Next cell
    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    End Sub


    [/vba]


    You have to select Microsoft Outlook Object Library 9.0 (or any later version) from References menu.


    I have attached excel which will show you the format in which you have enter data. This code I have adopted from the site which I have already mentioned to you.


    Hope this what you are looking for.


    HTH.

    Re: Mail from activesheet


    Hi Edwin,


    You still not made your point clear..


    Do you want to send mails to a range of recepients....?


    However, If some Moderator sees this post, will surely be moved to correct thread.

    Re: find function is not working


    Hi Joseph,


    Try this:


    [vba]
    Sub find()
    Dim maxnum As Double
    maxnum = Application.WorksheetFunction.max(Sheet1.Range("data"))
    Sheet1.Range("Data").find(what:=maxnum).Font.ColorIndex=3
    End Sub
    [/vba]


    Hope this helps.

    Re: macros to paste to certain cells


    Hi Neff,


    Please follow these to add Code tags:


    Start with ["vba"] and end with [/"vba"]. Type vba without the quotes.
    Type code in between these two. Then your code looks like this.


    [vba]
    Sub Macro14()
    '
    ' Macro14 Macro
    ' Macro recorded 17.02.2005 by admin
    '
    ' Keyboard Shortcut: Ctrl+z
    '
    Sheets("Sheet1").Select
    Range("A1:C2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    End Sub
    [/vba]


    Regarding your query, try a search first in this forum. I am sure these sort of questions are already been answered. If you still have some difficulty post back, stating your problem clearly.


    HTH.

    Re: Insert user name in a cell


    Hi,


    Welcome to the Ozgrid forum!


    Try the following to get the user name into cell A1. You can change the cell reference to suit your requirements.


    [vba]
    Sub Usernameincell()
    Range("A1").Value = Application.UserName
    End Sub
    [/vba]


    HTH.

    Re: Identify SUM formula using vb code


    Hi Richie,


    Thanx for the hint. It works.


    Following is the code(just to let you know what I wanted and to let someone use if they also in need of the same).


    [vba]
    Option Explicit
    Sub try()
    Dim rn As Range
    Dim cell As Range
    Set rn = Selection
    For Each cell In rn
    If cell.HasFormula And Left(cell.Formula, 5) = "=SUM(" Then
    cell.Formula = cell.Formula
    Else
    cell.Value = vbNullString
    End If
    Next cell
    End Sub
    [/vba]


    I am trying to keep the format with Sum formulas deleting any other texts/numbers etc., so that I can enter the data for the next month...


    Thanx

    Hi All,


    Just need some clue as to how to identify the SUM formula using VB. I tried the following, of no use:


    [vba]
    If Not cell.HasFormula And Not cell.Formula Like "=sum(" Then
    [/vba]


    The idea being, I want to retain the SUM formula in a range of cells and delete if it has some text or other formulas.


    Any help would be appreciated.


    Thanx

    Re: Help with constructing a formula...


    Hi,


    Welcome to Ozgrid forum!


    With your example one can make 2.5 or 2 fruit salads, as there are not enough peaches available. But it doesn't require any formula.


    Divide the number of number of fruits of availble(for each variety) with the number of fruits which makes it a salad and choose the lowest one of the three(in this case it is peaches: 15/6=2.5 or 2).


    HTH.

    Re: Controlling OnAction of PASTE button on toolbar


    Hi Pankaj,


    Try this:(am not sure if it helps..as I have not tested this)
    [vba]
    Application.CommandBars("Worksheet Menu Bar").Controls("&Paste").OnAction = "Pastevaluesonly"
    [/vba]


    I have not tested this as I messed up with my own worksheet controls. When press the Paste button it says "Book6.xls" not found(I recorded the earlier code in this book only). Though I don't much use this control.


    HTH.

    Re: VBA/Excel


    Hi Karnali,


    Welcome to Ozgrid Forum!


    May I request you use code tags.


    It's very easy..try this:


    Beginning of the code: ['vba'].....your vb code(subroutine)
    ...............end with [/'vba'].


    Don't use the single quote for vba(I typed it just to show you)...just type vba.


    Using of code tags helps one read your code easily and can easily be tested.


    HTH.

    Re: Controlling OnAction of PASTE button on toolbar


    Hey,


    You are correct..Am bit over excited...sorry for that.(I edited my post as well)..


    yeah...I remember that I have replied to your earlier posts..!


    But this one... how come it did worked for me..let me check again..but it should be an easy one for a Guru..you will get the answer...


    Anyway you seems to be in a better position than what I am.


    Congrat for finding the answer for the other two(Edit&popup)...


    All the best...


    Thanx

    Re: Listbox Populating


    Hi Andy,


    While the code you suggested works great..is it possible to use the 'ESC' key to unload the UserForm, instead of Close button.


    Should be Some thing like this...


    [vba]
    Application.EnableCancelKey = xlEnabled
    [/vba]


    thanx