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

• ## Run Macro when specific cell reached

Re: Run Macro when specific cell reached

Hi,

Welcome to the Forum.

Try:
Data-Validation - Input Message
Here you type the msg and also you can give a title to it. This message will be displayed whenever the
Cell is selected

HTH

• ## Popup to expand on a code in a cell

Re: Popup to expand on a code in a cell

Hi Tom,

Try this:
Data-Validation-Input Message(fill in Title & Input Message)

In this option you can type the message which will be displayed when the cell is selected.

HTH.

• ## Count the number of months in between dates

Re: Count the number of months in between dates

No it's not a code. This is just one excel formula and can directly be placed in any cell.

And use =DATEDIF(A1,A2,"ym"), if the dates are in the same year.

HTH.

• ## Return row number only after match?

Re: Return row number only after match?

It works for me.

[vba]
[/vba]

Change the references in above formula.

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.

• ## Outputting to a file with correct decimal places

Re: Outputting to a file with correct decimal places

Hi depesh,

You need to change the formating of the new range in which you are getting the number.

[vba]
Range("GrsAmt").NumberFormat = "0.000"
[/vba]

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

Thanx

• ## Count the number of months in between dates

Re: Count the number of months in between dates

Hi Psying,

Welcome to the Forum.

Try this:
[vba]
=DATEDIF(A1,A2,"m")
[/vba]

please note that date1(A1) should be earlier than date2(A2). This gives the number of complete months between Date1 and Date2.

HTH.

• ## Return row number only after match?

Re: Return row number only after match?

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

[vba]
[/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.

• ## Time entered as Date

Re: Time entered as Date

Hi Xlite,

Something like this helps:

[vba]
With Range("A1")
.Value = .Value & ":"
.NumberFormat = "hh:mm"
End With
[/vba]

HTH.

• ## length of array

Re: length of array

Try this:
[vba]
Ubound(array)
[/vba]

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

Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = 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.