Posts by senarumugam2003

  • Union Method In Vba

    Hi,


    How to use the union method in VBA


    Code
    Dim rngTemp as Range
                 If enterReg = 1 Then
                    Set rngTemp = Range(Range("a" & stfsStRow), Range("o" & stfsEnRow))  ' It will be executed when the first xls is opened
                Else
                    Set rngTemp = Union(rngTemp, Range(Range("a" & stfsStRow + 1), Range("o" & stfsEnRow))) ' It will be executed after the first xls
                End If


    I have 4 xls and i need to consolidate into one file. I am opening the first file and get the data from xls and able to assign to rngTemp (Range Variable). But for the next consecutive xls, i am able to get the data but not able to assign it the rngTemp (Range variable) using Union method (The place i am struggling to get the result)


    In the above code, if condition will satisfy for the first xls only. And for the rest of the files else part will work.


    I am facing problem with else part only.


    I am not able to union the existing rngTemp to new range of data from another file.


    Can any body help me on this.


    Thanks
    Senthil.

  • Calculate The Hours Between The Two Dynamic Dates

    Hi Guys,


    How to calculate the hours between two dynamic dates.


    Rules 1: It has to calculate only office hours (Let's say 9 AM to 5 PM)


    Rules 2: The starting time may fall before 9 AM, then we have to do calculation from 9 AM on the same day. (Let's say start date with time 17/04/07 7:00:00 AM, then we have to consider the date with time from 17/04/07 9:00:00 AM for the calculation)


    Rules 3:The starting time may fall After 5 PM, then we have to do calculation from 9 AM on the next day. (Let's say start date with time 17/04/07 8:00:00 PM, then we have to consider the date with time from 18/04/07 9:00:00 AM for the calculation)


    Rules 4: It has to exclude the Saturday and Sunday


    Can you please help me in this.


    Thanks & Regards
    Senthil

  • Extracting Sub-folder From File String

    Re: Extracing Sub-folder From File String


    Try this


    Code
    Dim str As String
    str = "c:\Contracts\Jeremy Hills\Hills House Lvl 1.xls"
    str = Mid(str, InStr(1, str, "\") + 1, Len(str))
    str = Mid(str, InStr(1, str, "\") + 1, Len(str))
    str = Left(str, InStr(1, str, "\") - 1)



    cheers!!!!!!
    senthil

  • Sorting

    Hi Guys!!!!!


    How to sort if we have the list of data is more than one column.


    Say Example:


    The range of the values is from 1 to 1,00,000 which is not fit to one column. It needs more than one column to accommodate. In the scenario how to sort the values.


    Can any one please help me in this.


    Cheers!!!
    Senthil

  • Error In Finding Date In Another Range

    Re: Error In Finding Date In Another Range



    try this


    Code
    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim tdy As Range
    Dim today
    Set s1 = Sheets(1)
    Set s2 = Sheets(1)
    today = s1.Range("C4").Value
    Set tdy = s2.Range("A:A").Find(WorksheetFunction.Text(today, "dd/mm/yy"), s2.Range("a:a").Cells(10, 1), LookIn:=xlValues, lookat:=xlWhole)
    Cells(tdy.Row - 1, 1).Select


    cheers!!
    senthil

  • Transferring Text Boxes To Cells

    Re: Transferring Text Boxes To Cells


    Quote from mickeymo

    Thanks for your thoughts guys but that doesn't work either. Now getting error 1004, "Unable to get OLE objects property of the worksheet class", the same line beginning Cells.... is again highlighted. Looking back at the original code, the problem area seems to be that attaching variables to the shapes("Text Box 1") is causing the stumbling block but I don't know why.


    Hi,


    Code
    Dim a As Shape
    For Each a In ActiveSheet.Shapes
        MsgBox a.Name    ' This is will give u the name of the existing object present in the active sheet.
                                ' And You use this area to get the value from the text box to cells that u wanted to place
    Next

  • Max Value Search & Highlight

    Re: Max Value Search & Highlight


    Quote from youngsie81

    I am attempting to search a row of data and highlight the cell that has MAX value.


    How am I able to do this?


    I would also like to run this in a loop so I can perform this function over multiple rows



    1) Select the rows that u wanted to highlight
    2) Goto Format menu and then click Conditional Formating
    3) Then select the Cell Value Is in the first drop down
    4) Then select the Equals to in the second drop down
    5) In the third text box =max(selected_rows_name)
    ex =max(a1:a6) will highlight the highest value found in the a1 to a6 rows
    6) Click the Format button
    7) Then select the Pattern tab
    8) Click any color that you want to show up
    9) Click ok
    10) Click ok



    Have a nice day.



    cheers!!!!!
    senthil

  • Calculate Column Total Via Vba

    Re: Calculate Column Total Via Vba


    Code
    Range("n" & (Range("n" & Rows.Count).End(xlUp).Row) + 2) = "=Sum(" & Range("n7", Range("n" & Rows.Count).End(xlUp)).Address & ")"


    While runing the macro, the total cell should be in blank.


    This will calculate the data which is starting from N7.


    i hope this will help you.


    Cheers!!!!!
    senthil

  • Identify The Opened .xls

    Re: Identify The Opened .xls


    Hi,


    I am currently using ADODB connection to connect to the File1.xls through the File2.xls. So in this case, i will only open the File2.xls where in i developed the Userform. Data from the Userform in File2.xls, will be entered to the File1.xls without even opening it.


    In this case, The problem what i am facing is when the File1.xls is locked by one person and when the another person is attempting to connect to the same File1.xls.


    In this scenario,


    Firstly Sometimes the File1.xls may opened as Readonly mode and Datas are keep entering into File1.xls in the readonly mode.


    Secondly, I got the error message.


    I wanted to fix this without getting any error message and by programmatically. When the one person is locked the File1.xls, and at the sametime other person is attempting to connect to File1.xls, then for the second person, the program will be in the waiting mode till File1.xls is released.


    How do i accomplish this.


    Please help me.


    cheers!!!


    Thanks
    Senthil Arumugam P.

  • Identify The Opened .xls

    Hi,


    I am using ADO connections in my program to insert the datas in the excel.


    I want to find if the excel is locked by another user. How do i do this in ADO commands.


    Can you please help me in this.




    Thanks
    Senthil Arumugam P.

  • Library File Name

    I am using Mid function in my program. During execution, i am getting the waring message of "Complier Error: Cann't find project or library".I am not able to find the what is the reason behind on it. If really the library file missing, could you please advice what is the name of library file or else anything to be installed newly or path to be checked for the file existing.

  • Move The Cursor Automatically

    Hi,


    I have the command button which is placed in excel to open the SaveAs Dialogbox.


    When i click command button, The cursor will automatically moving to File menu and do click on it then the dropdown will come. And again cursor will moving to SaveAs option and do click on it.


    Can you please help me to do this.


    Thanks You