Posts by pradeep_atm

  • If function

    Re: If function


    Hi Susan,


    I am not sure whether I got you correctly..


    However try this and see if this is what you are looking for:


    [vba]
    Sub CopytoSht()
    Dim rn, cell As Range
    Dim str As String
    Set rn = Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp))
    For Each cell In rn


    If Right(cell.Text, 2) = "01" Then
    str = str & " " & cell.Value
    Else
    Sheet2.Range(Sheet2.Range("A1"), Sheet2.Range("IV1").End(xlToLeft)).Offset(0, 1) = cell.Value
    End If
    Next
    Sheet1.Range("B1") = str
    End Sub
    [/vba]


    change the sheet and range references if you got a different one.


    Please post back if you have any issues..


    HTH.

  • Conditional Formatting

    Re: Conditional Formatting


    Hi Srinivasan,


    Try this:


    In Conditional Formating-Formula-type below formula, after selecting the range for which you want to apply this formating. From the Format button you can choose whatever the colors/patterns you want to apply.


    Replace "B3" with the starting cell address of your range(without $ symbol)


    =NOT(ISERROR(FIND("System",B3)))


    HTH.

  • If function

    Re: If function


    Hi Susan,


    This can be done. Please make the if conditions clear.


    Do you want the data only in Cell B1 if the conditions are met(last two characters of a cell is 01).


    HTH.

  • Count the number of months in between dates

    Re: Count the number of months in between dates


    Hi,


    As you can see the formula which I have mentioned counts the full months only.


    To get what you want, can try this(though not an elegant one):


    =DATEDIF(B3,B4,"d")/30


    Here we are trying to get the number of days between two days and converting to months(dividing by 30). Taking two dates in your example this formula yields 2.83(close to 3) and when you make it zero decimals it will display 3.


    HTH.

  • Fixed width VBA code.

    Re: Fixed width VBA code.


    Hi,


    Welcome to the Forum!


    Try the "Trim" Function to remove the extra spaces before applying the Text to columns function. And place the code in the Workbook module(Alt+F11 &gt Insert &gt Module).


    Instead of going for the code, the built in "text to columns" tool is pretty handy..


    HTH.

  • Matrix Product Multiplication

    Re: Matrix Multiplication


    Hi Avataar,


    In response to the private msg which you have sent to me:


    worksheet functions can be used in VB by adding the prefix Application.Worksheet.FuntionName.(Ex:Application.Worksheet.MMULT())


    There is Transpose funtion to get the transpose of a range as well. To get a better response probably you can post your code and requirement and some one here can help you..


    Posting in the forum helps others also, who are looking for a similar kind of solution. And chances of getting a better solution here, when you post in public forum instead of sending in private..


    HTH.

  • Matrix Product Multiplication

    Re: Matrix Multiplication


    Hi,


    I think you need to go for coding to achieve this. Try the following:
    1. To transose the matrix
    gt Edit &gt Pastespecial & chose transpose with values


    2. Use MMULT funtion to multiply two ranges.


    Syntax: =MMULT(Range1,Range2) and since this is an array function you need to use Ctrl + Shift + Enter after entering the formula.


    HTH.

  • Flashing screen when code is running

    Re: Flashing screen when code is running


    Hi,


    It is not clear what actully you want...


    Are you saying that, with the help of UserForm(with some buttons on it), you want to open some files and move to some sheets(by pressing some buttons etc.,).


    Please be more specific on your requirements..


    Thanx

  • Return row number only after match?

    Re: Return row number only after match?


    Hi fada,


    Though am not clear as to what you are trying to achieve, the following should give the desired result:


    [vba]
    =ROW(INDIRECT(ADDRESS(MATCH(MIN(C1:C6),C1:C6,0),COLUMN(),1)))
    [/vba]


    Please note that I am trying to get the data in the same column that is why am using COLUMN(), you change this to meet your requirements.


    HTH.

  • Adding totals in bands of 10

    Re: Adding totals in bands of 10


    Hi Kris,


    May be the second part of Vandana's question can be achieved by using formula, but are you saying that one can insert rows using a (first part of the question) formula..interesting!!


    My settings here will not allow me to view your excel book. Glad if you could explain me how...


    Thanx

  • retrieving data from a folder full of spreadsheets...

    Re: retrieving data from a folder full of spreadsheets...


    Hi,


    Try this and modify to your requirements.


    [vba]
    Sub RunCodeOnAllXLSFiles()
    Dim i As Integer
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False


    On Error Resume Next


    Set wbCodeBook = ThisWorkbook


    With Application.FileSearch
    .NewSearch
    'Change path to suit
    .LookIn = "Type your path here"
    .FileType = msoFileTypeExcelWorkbooks


    If .Execute > 0 Then 'Workbooks in folder
    For i = 1 To .FoundFiles.Count 'Loop through all
    'Open Workbook x and Set a Workbook variable to it
    Set wbResults = Workbooks.Open(.FoundFiles(i))


    'Do Your Code Here


    Next i
    End If
    End With


    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End Sub
    [/vba]


    This Code loops through some folder and do some stuff and close the WB. The code I have taken from Dave's tutorial on For Loop.


    HTH.

  • Stopwatch 100th of Seconds

    Re: Stopwatch 100th of Seconds


    Hi Fugitive Mind,


    Try:


    [vba]
    Selection.NumberFormat = "mm:ss" 'Change the reference to suit your requirements.
    [/vba]


    Or else, Press Ctrl+1 &gt Custom in the Category list box, then type mm:ss in the Type dialogue box.(this will also give the same result).


    HTH.