|
| |
| |
| |
|
|
|
| MS Excel help |
| MS Excel help, VBA, E-books, formulas, Excel tips and Macro help , Microsoft Excel help, Macro, VBA, e-books download, Advance formula, |
| Language: English |
| RSS Feeds for this Blog |
|
Statistics |
| Unique Visitors: 0 |
| Total Unique Visitors: 2341663 |
| Visitors Out: 2816 |
| Total Visitors Out: 12942 |
|
|
|
| |
|
|
| |
|
| VBA code, extract Number from Text |
| 2009-08-18 13:58:31 |
During this post I will try to share VBA/Macro code to extract number from the text. Also, we will discuss the code to help you in understanding how to code in VBA. After all the aim is to make you enable write VBA code. Though in most of my post I write this at the end. This time I am advising you in middle to subscribe yourself to this blog. Also, your comments motivates us.
Let's move ahead with first understanding the application/scenario under which you can use this code. Take an example where you Microsoft Excel file where you have series of number where text is between number and you are only interested in numbers and not next. If the text is on fixed place you can remove that using LEFT() , RIGHT() or MIDDLE() function. But if the numbers and text are placed in cell where you...
Visit blog for more details
...
|
| |
|
| VBA function to Concatenate |
| 2009-08-06 15:16:32 |
In this post, let's take a look at VBA code/function to concatenate string. Like in Microsoft Excel concatenate function you cant provide function with range. So you have to provide function with each cell at a time which is time consuming. The best solution to help
VBA Code
Function VBAConcatenate(target As Range, delimiter As String) As String
Dim str As String
For Each c In target
str = str + delimiter + c
Next c
If delimiter = "" Then
VBAConcatenate = str
Else
VBAConcatenate = Right(str, Len(str) - 1)
End If
End Function
VBAConcatenate require following parameters
Target: Target is range of cell you wish to...
Visit blog for more details
...
|
| |
|
| Book Mark Add-in |
| 2009-08-04 02:05:57 |
Huge reports with more than 5 sheets are very difficult to browse. Also, in spite of making very professional report you will it confusing. So, solution to avoid confusion is to add book mark sheet with link to each Microsoft Excel worksheet. This worked in my case with large number of reports. But the process is very hectic. Like adding a shape, adding text to shape and than linking it to right worksheets which no one would like to do for each reports you make. Hence, I developed an MS Excel add-in which will automatically does these all for you. Take a look at the video below how it works.
How to use Bookmark Add-ins
This add-ins cost only 5$. If you like to receive this add-in click on button below to pay via Paypal. We will send you add-ins via email. Instructions to...
Visit blog for more details
...
|
| |
|
| VBA Code to delete worksheets |
| 2009-07-29 12:43:43 |
In my last post we have seen how to add worksheets to Microsoft Excel workbook using VBA code. In this post we will delete the worksheet. Now, you have to be very careful while deleting worksheet because you won’t be able to recover any worksheet that deleted by using this VBA/Macro code. I have written two different set of codes. First one will delete active sheet while other one will delete based on sheet name provided by end user. I have purposely kept the prompt so that you don’t end up loosing any data while testing code.
So, let’s move ahead with first set of VBA/Macro code.
Sub DeleteActiveSheet()
' deletes active worksheet in the active workbook
On Error Resume Next
Dim str As String
str = ActiveSheet.Name
Sheets(str).Delete
End Sub
Warning: The...
Visit blog for more details
...
|
| |
|
| VBA code to Add worksheet |
| 2009-07-28 19:07:50 |
Today we will discuss a different method in VBA to add worksheet in MS Excel workbook. Now when I say different method I mean ways of adding worksheet based on your requirement. Let’s moved ahead and start with simple piece of code to complex one in steps and based on scenario. Scenario 1: Add worksheet with default name. Sub Addsheet() Worksheets.Add End Sub Note: The above code will add a worksheet before active worksheet. Scenario 2: Add worksheet with specific name. Sub Addsheet() Worksheets.Add().Name = "Sarfaraz" End Sub Scenario 3: Add multiple worksheets. Sub Addsheet3() 'Add Multiple worksheets Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4 End Sub Scenario 4: Add worksheets at...
Visit blog for more details
...
|
| |
|
| Sequence formula |
| 2009-07-26 23:23:38 |
Many a times you must want MS Excel to sequence the entry automatically. But thats not one of the features of MS Excel. So what you do is you enter the list than you type 1 in the top most row and drag it till last row. isn't it? This is most common practice I have seen around. Today here I will share to tricks on sequence.
In first sequence type, we will count the based on number of rows filled without checking if the value is duplicate with respect to previous row.
Formula: =IF(B6="","",COUNTA($B$6:B6))
In the above example, we are considering that we are sequencing based on column B starting from 6th row. The formula check if the value is present in B column and then counta find the count of non-blank from column B6 to respective row and returns count....
Visit blog for more details
...
|
| |
|
| Upper function to change case |
| 2009-07-22 01:37:02 |
This post is for beginners of MS Excel. This function convert all the characters to upper case(capitals). It is very useful when you have a data which is not in uniform case. Like data received from Data entry operators.
Syntax: =Upper(Text)
Attached below is snapshot with Upper function as example. Take a look, I am sure this will help you.
Click on image below to view enlarge
There are other function in MS Excel which help you with cases are Lower(), Proper(). Also, the Upper function is very useful when you want to compare text from two cells. Since Exact() function is case sensitive it treats 'a' and 'A' as two different letters. I have enclosed the link to my earlier post where I have demonstrated to find cases using exact function
Check which CASE is used...
Visit blog for more details
...
|
| |
|
| Workday function |
| 2009-07-20 23:13:06 |
Workday function is used to find either past or future date before/after the given number of business days. The function only consider working/business days which mean while arriving to date it excludes Weekly off's and holidays. What I like most about this function is you can also provide it with holiday list you don;t want to be considered.
Now if you are thinking what will be the application of this function than let me tell you whenever I decide any dead line for project I use this function to calculate date. This exclude all Weekends and holidays list which I provide to function. Also, this can be use to calculate delivery date or number of days of work performed.
Let's take a look at the syntax of formula below.
=Workday(StartDate, Days, Holidays)
StartDate is the...
Visit blog for more details
...
|
| |
|
| Add-in and macro to convert seconds to hh:mm:ss |
| 2009-06-25 23:07:40 |
This add-in and macro will help you in converting seconds to hh:mm:ss. I have put add-ins for those who just wants to use the add-ins and macro for those who wants to understand this trick. This is very useful in BPO/Contact Center where you have source data in seconds and you have to represents data in hh:mm:ss seconds.
Mentioned below is macro code
Sub convert_hh_mms_ss()
Dim i As Double
Dim x As Integer
Dim y As Long
x = 1
y = 1
For Each c In Selection
i = c / 86400
Selection.Cells(x, y) = i
Selection.Cells(x, y).NumberFormat = "[h]:mm:ss;@"
x = x + 1
Next c
End Sub
Logic: c is each...
Visit blog for more details
...
|
| |
|
| VBA to close excel.exe |
| 2009-06-22 22:46:47 |
Closing all orphan Excel.exe process
I came across this issue while I was generating reports on SQL server using MS Excel. Here we use to generate reports using MS Excel macro. However this macro's where executed via DTS in SQL. At times if any error occurs, the DTS use to close down leaving excel.exe running as orphan object. This not only sometimes prevent other excel objects but also slow downs your server performance. To tackle this we used the following code to close all MS Excel objects before opening new object.
Sub Close_Excel()
Dim strClsExl As String
strClsExl = "TASKKILL /F /IM Excel.exe"
Shell strClsExl, vbHide
End Sub
Warning: Kindly save all your work before trying above code as this will close all MS Excel instances running...
Visit blog for more details
...
|
| |
|
| |
 |
