Posts by pradeep_atm

  • Function reference in worksheet

    Re: Function reference in worksheet


    Hi Kaide,


    Are you aware that, one can refer to the UDF's through the Funcion dialogue box(Shift+F3 &gt UDF's).


    To my knowledge one can't refer to the UDF's without the Worbook reference. (unless someone here provides some clue, will be eagerly waiting..)


    HTH.

  • Adding totals in bands of 10

    Re: Adding totals in bands of 10


    Hi Vandana,


    Try the following:


    [vba]
    Sub taketotals_sheet2()
    Range("b3").Select
    For i = 27 To 126
    Sheet1.Range("f" & i) = ActiveCell.Offset(10, 0).Value' Change the Sheet reference here to suit your requirements
    ActiveCell.Offset(11, 0).Select
    Next i
    End Sub
    [/vba]


    You can change this reference to your liking.


    HTH.

  • Adding totals in bands of 10

    Re: Adding totals in bands of 10


    Hi Vandana,


    Try this:


    [vba]
    Sub sumatten()
    Range("A3").Select
    For i = 1 To 100
    ActiveCell.Offset(10, 0).Insert shift:=xlDown
    ActiveCell.Offset(10, 1).FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    ActiveCell.Offset(11, 0).Select
    Next i
    End Sub
    [/vba]


    This code makes set of 10 rows starting from A3 and puts SUM funtion in Column B to total the 10 rows above.


    Please post back if you have any difficulty. My settings here will not allow me to download the file and hence this code I worked based on your post.


    HTH.

  • Find a Cell on one sheet, write data into another sheet

    Re: Find a Cell on one sheet, write data into another sheet


    Hi,


    [vba]
    Set MyRange = Intersect(ActiveCell.EntireColumn, AreaRange)
    [/vba]


    If you look at the above code, it selects the Intersection of the Activecell.cerrent region(=Area Range) and the current column. That is current data table(unlike UsedRange).


    Hence it is taken care in this code.


    As far your second question concerned, this code is to place the entire row and not just the cell alone to the next sheet.


    HTH.

  • Find a Cell on one sheet, write data into another sheet

    Re: Find a Cell on one sheet, write data into another sheet


    Hi,


    Try the following code.


    Place the cursor where "yes" is there in your Search sheet and then run this macro. Before running this macro please ensure the following:


    Let your Target sheet be Sheet2(if not change the sheet reference). The code start placing the data in Target sheet from Cell A2 on wards, you can change this to your requirements.



    [vba]
    Sub Cond_Copy()


    Application.ScreenUpdating = False
    ReturnSheet = ActiveSheet.Name
    Set AreaRange = ActiveCell.CurrentRegion
    Set MyRange = Intersect(ActiveCell.EntireColumn, AreaRange)

    ' Define area that matches selected cell value
    x = ActiveCell.Value
    For Each Cell In MyRange
    If Cell.Value = x Then
    If i = 0 Then
    Set NewRange = Cell.EntireRow
    Else
    Set NewRange = Union(NewRange, Cell.EntireRow)
    End If
    i = i + 1
    End If
    Next
    Set NewRange = Intersect(NewRange, AreaRange)

    ' Copy & Paste
    Sheets(2).Select
    Cells.ClearContents
    Range("A2").Select

    NewRange.Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select

    Sheets(ReturnSheet).Select
    Application.ScreenUpdating = True

    MsgBox ("See Sheet2")
    Sheets(2).Select

    End Sub
    [/vba]


    This code I have taken from xl-logic.com. Post back if you have any problems.


    HTH.

  • Worksheet Default

    Re: Worksheet Default


    Hi Paul,


    Try this:


    [vba]
    Private Sub Workbook_Open()
    Sheet3.Activate
    End Sub
    [/vba]


    Change the sheet reference to your requirement. And place this code in Workbook open event.


    HTH.

  • Compare Column

    Re: Compare Column


    Hi,


    Welcome to the Ozgrid forum.


    Place this formula in Column D and drag it to the end of the list, which will display the entries which are not there in Column C. Change the range reference according to your requirements. Remember to use $ reference in case of the Second range and not to use $ reference in the first case.



    =IF(COUNTIF($C$3:$C$5,B3)>=1,"",B3)


    There may be better ways of attempting this..


    HTH.

  • member info retrivial from number???

    Re: member info retrivial from number???


    Hi Pantheist,


    It's very much possible. Please attach an excel file with the sample data(change the data if it is confidential). Also mention in which format the data relating to the member to be displayed(if you have desinged a separate Form to display data etc.,).


    Also it is very easy. You can try LOOKUP formulas as Richard suggested.



    HTH.

  • PRODUCTION ISSUE with Macro performance

    Re: PRODUCTION ISSUE with Macro performance - **URGENT**


    Hi Clemson,


    Please use Code tags(read the posting rules).


    Before the code type: ['vba']...here type vba without quotes


    ....your code here


    [/'vba'].....here again type vba without quotes.


    Before someone can look into your problem this is necessary..