VBA Code Excel Macro Examples – Useful Macros, Codes, 100+ How To explained for Basic Beginners to Advanced VBA users. Tutorials to learn Excel 2003, 2007, 2010, 2013 Macros and Mastering in VBA. Selected examples to deal with different objects, methods and properties in Excel. Numerous free most useful VBA codes and tips will help you to deal with various Excel Objects like Cell, Range, Worksheets, Workbooks, Application, Charts, Pivot Tables, Hyperlinks, functions, User Forms, MsgBox, ListBox, ComboBox. Also provided free example codes to deal with MS Word, PowerPoint, Outlook, Access, Other Applications and File Handling. If you think that I missed any useful code, please feel free to write us. We will respond with a solution with in couple of days and publish here to make it available for all VBA Users.

100+ Excel VBA макросов коды Примеры | excel VBA makro kod örnekleri | Excel的VBA宏代码范例 | Excel-VBA-Makros Codes Beispiele | excel VBA Macro Codes Contoh | एक्सेल VBA मैक्रो कोड्स उदाहरण.

## The Most Useful VBA Codes Excel Examples Macros! Learning Path

Learn **VBA Codes Excel Examples Macros** and do almost everything! We have explained verity of examples to cover most frequently used codes. Start learning …!

- Cells and Range Objects
- Rows and Columns Objects
- Worksheet and Workbook Objects
- Hyperlinks
- Charting
- Tables
- Pivot Tables
- Names
- Other Applications
- Application Object
- Folders and File Handling
- Miscellaneous

### Cells and Range Objects : VBA Codes Excel Examples Macros

Reading data from a Cell was my first exciting VBA Code when I stared learning Excel VBA. Then I successfully write data to Excel Cells. That was my first happy moment which motivated me to engage with VBA from last 10 years.

Here are the common *VBA Codes Excel Examples Macros* to deal with Cell and Range Objects of Worksheet.

You can find examples on reading and writing the data, selecting, copying and pasting the data.

Clearing, changing font color, font to bold, background color of cells or range. We can also see the examples on merging cells, adding comments and changing the font case to lower or upper using Excel VBA.

- Write Data to Worksheet Cell in Excel VBA
- Writing and Reading Excel Worksheet Cells Ranges in VBA
- Read or Get Data from Worksheet Cell to VBA in Excel
- Select Cell Range in Excel VBA
- Copy Data from One Range to Another in Excel VBA
- Clear Cells in Excel Range Worksheet using VBA
- Change Font Color in Excel VBA
- Change Font to Bold in Excel VBA
- Change Text Case – Upper Lower in Excel VBA
- Change Background Color of Cell Range in Excel VBA
- Merge UnMerge Cell Range in Excel VBA
- Add Clear Comments in Excel VBA

### Rows and Columns Objects: VBA Codes Excel Examples Macros

Cell and Range objects helps to reading and writing the data from worksheet. Now we will look into Rows and Columns of the Worksheet, helps to show or hide the data. Here you can find the VBA Codes Excel Examples Macros on delete rows, columns, change row height, column width. Hiding or un-hiding columns or rows. Inserting or deleting Rows or Columns. And finding the Last row, or columns in the worksheet in different situations.

- Change Row Height and Column Width using Excel VBA
- Delete Rows and Columns in Excel VBA
- Hide UnHide Columns in Excel Worksheet using VBA
- Hide UnHide Rows in Excel Worksheet using VBA
- Finding last used Column with data in particular Row
- Finding last used Row with data in particular Column
- Inserting Columns in Excel Worksheet using VBA
- Inserting Rows in Excel Worksheet using VBA
- Find Last Column with data in Worksheet using Excel VBA
- Finding last used Row with data in Excel Worksheet using VBA

##### Worksheet and Workbook Objects: VBA Codes Excel Examples Macros

Now we will see the __VBA Codes Excel Examples Macros__ on Worksheet and Workbook Objects. Here you can find creating new workbook, opening, closing and saving workbook. And example to show running a macro on opening or staring the workbook. We will also see how to protecting or unprotecting Excel workbooks or worksheets, Copying the data from one worksheet to another worksheet.

Hiding and unhiding worksheets. Changing the tab color of worksheet. Activating workbooks or worksheet, etc.

- Create New Workbook in Excel VBA
- Run a Macro Automatically on Opening Excel Workbook
- Open and Close Excel Workbook using VBA
- Save Workbook Using Excel VBA to Specific Folder
- Protect and Unprotect Excel Workbook using VBA
- Protect and UnProtect Worksheets in Excel VBA
- Hide UnHide Worksheets in Excel VBA
- Delete Worksheet in Excel VBA
- Copy Data from one Worksheet to another in Excel VBA
- Change the Color of Sheet Tabs in Excel VBA
- Activate Workbook Or Worksheet in Excel VBA
- Get Active Workbook or Worksheet Name Path FullName in Excel VBA

##### Hyperlink: VBA Codes Excel Examples Macros

Hyperlinks are most widely used concepts in Excel. We generally use hyperlinks to navigate or open a file, link or folder. But we can do many other things using Hyperlinks. Examples in this topic will show you the power of Hyperlinks in Excel VBA. We have covered the following examples in this tutorial:

- Add Create Hyperlinks in Excel VBA
- Removing Hyperlinks in Excel VBA
- VBA Open File Folder Website Using FollowHyperlink method in Excel
- VBA Create Send Emails Using FollowHyperlink Method – Send Keys in Excel

Hyperlinks in Excel VBA – Explained with Examples!

### Charts : VBA Codes Excel Examples Macros

Another powerful tool in Excel is charting. You can create rich visualized dashboards using Excel Charts and VBA. We have covered most commonly used Charting VBA Codes Excel Examples Macros. In this topic will show you how to deal with different chart objects to automate the charting process using VBA. Examples on creating charts, changing chart types. Changing chart axes titles, chart title, axis format, primary and secondary axes. We have provided more than 33 example to cover A-z of Excel Chart VBA.

- Example tutorials on Creating Charts using Excel VBA
- Example tutorials on Chart Type using Excel VBA
- Example Tutorials on Formatting Chart Objects using Excel VBA
- Example Tutorials on Chart Collection in Excel VBA
- Other useful Examples and tutorials on Excel VBA Charting
- Excel VBA Charting Constants and Enumeration

Excel Chart VBA Examples and Tutorials

##### Tables: VBA Codes Excel Examples Macros

Tables in Excel help to manage our data in Excel and give the more control over the data. In this example we will see the different VBA Codes Excel Examples Macros like creating tables, sorting tables data and applying filters in the data. We have provided following example to deal with Tables using Excel VBA.

- Create Tables in Excel VBA
- Sorting Tables in Excel VBA
- Filtering Tables in Excel VBA
- Clear Toggle Table Filters in Excel VBA

Tables in Excel VBA – Explained with Examples!

##### Pivot Tables: VBA Codes Excel Examples Macros

Pivot tables help us to summarize the data and analyze it. VBA Codes Excel Examples Macros provided for creating pivot tables, pivot charts, adding calculated, changing row fields, column fields, value field fields in pivot tables using VBA. We have provided the following example to deal with pivot tables using Excel VBA, we will add some more examples to do more tasks using pivot tables.

- Creating Pivot Tables in Excel VBA
- Create Pivot Chart using Excel VBA
- Create Pivot Column Chart using Excel VBA
- Create Calculated Pivot Field in Excel VBA

Pivot Tables in Excel VBA – Explained with Examples!

##### Names: VBA Codes Excel Examples Macros

Names are another time saving feature in the Excel. We can define the names to Cells, Ranges and Tables and use them across the worksheets in the workbook. Her we will see how to add or remove the Names using VBA. Hiding and un-hiding names from the users. Follwing examples covered in this topic to deal with Names using Excel VBA.

- Adding Names in Excel VBA
- Deleting Names in Excel VBA
- Hide UnHide Names in Excel VBA

Names in Excel VBA – Explained with Examples!

##### Other Applications: VBA Codes Excel Examples Macros

VBA is powerful, it can interact with the other applications like MS Word, PowerPoint, Access, Outlook, Internet Explorer, VBScript etc. Here are examples to deal with other application from Excel. Below examples are covered in this tutorial:

- Interact with PowerPoint from Excel VBA
- Dealing with MS Word From Excel VBA
- Interact with MS Access from Excel VBA
- Interact with Outlook from Excel VBA
- Dealing with Internet Explorer
- Dealing with Other Applications from Excel VBA – Calculator
- Run VBScript from Excel VBA
- VBA to Attach Send An Excel Chart to Outlook Email

Excel VBA to Interact with Other Applications

##### Application Object: Excel VBA Codes Examples Macros

Mastering the Application Objects Examples help you to fasten your VBA programs and writing optimized code in Excel VBA. Examples for stopping or displaying application alerts, stopping or enabling screen updating, stopping or enabling application events. The following example procedures and functions are covered in this topic to fasten and speed up VBA code processing.

- Stop Screen Updating
- Stop Events in Excel VBA – Disable Enable
- Stop Application Alerts in Excel VBA – Disable Enable
- Display Progress on Statusbar in VBA Excel
- Set Windows State in Excel VBA – Minimize Maximize Normal
- Toggle Full Screen in Excel VBA
- Get User Name in VBA Excel
- Stop Calculations in Excel VBA – Manual Automatic
- Open Visual Basic Editor (VBE) – Open Module with VBA
- VBA to Exit from Procedure or Function

Fasten VBA Code – Application Objects Explained with Examples

##### File Handling: Excel VBA Examples Macros Codes

File handling examples are provided to creating deleting files, folders. Copying Files and Folders and Moving from one location to another location. And displaying file or folder dialog boxes to browse the files or folders. And check if files exists in a folder using VBA.

- Check if Folder Exists using Excel VBA
- Opening Folders using VBA Excel
- Creating Folders in Excel VBA
- Copying Folders From One Location to Another in Excel VBA
- Move Folder From One Location to Another in Excel VBA
- Deleting Folders in VBA Excel
- Make File Read Only in VBA Excel
- Copy all Excel Files One Folder to Another in VBA Excel
- Opening Files Using File Dialog Box in Excel VBA
- Customize File or Folder Dialog Box in VBA Excel
- Excel VBA File Dialog Box – Displaying Vanilla Dialog Box to Pick Files

Folders and File Handling in Excel VBA

Some more VBA examples are added to deal with Files and Folders using Excel VBA:

Hi,

How to add excel chart to email body?

Thanks,

Chitanya

Hi Chaitanya,

Welcome to ANALYSISTABS!

Please refer the following page:

How to Add and Send an Excel Chart to an Outlook emailThanks-PNRao

Thnk you very much.

You are most welcome!

Hi Team,

A very intersting site to learn VB. The thing which i liked here most is user friendly & explanation of each n every concepts very precisely. Applause for the whole team. Great JOB!!!

Regards

Spoorthi M

Thank You! Enjoy Learning @

ANALYSISTABS.COMHi PNRao,

It’s very interesting site to learn step by step VBA in easy way. Please allow me to copy your lesson learning. thank you

Hi Lody,

Thanks for visiting us. Unfortunately you should not copy the content from our site as it is against our terms and policies. We are a team working in day and night to build this. You can feel free to use our site as a reference for your queries our for learning purpose. We don’t allow the people to copy and duplicate our content.

For more details, please read our terms and conditions:

Privacy Policy | Terms of Use

Thanks-PNRao

Can we get 100+ Examples for Basic and Advanced Users in PDF format So that we can work or Refer while offline

Ok I got it, I read last comment Just now, Thanks for Good Effort,This website is best for all freshers who want to learn VBA programing, you are Amazing Guys.

May god Bless you.

Hi Amrut,

Thanks for visiting our site.

Yes, I am working on 100+ Excel VBA Examples and providing to my blog readers as soon as possible (with in a month).

Thanks

PNRao

Hi,

I would like to know about the matrix multiplication,transpose and inverse of a matrix (mXn). Kindly help me on this.

Thanks

Maahendra

Hi Mahendra,

I will post the example programs ASAP.

Thanks-PNRao

Hi ,

I am new to Excel VBA and my need is to create multiple worksheets on the same workbook based on the values from a range of cells(Text) from the Index Sheet and also i have to give hyperlink to the corresponding sheets.

For Eg:

cell A1 contains “Name”, a work sheets should be created on this name and Cell A1 should have hyper link to the created worksheet.

cell A1 will be in Index or 1st page.

Thanks

Hi,

I am very new to Excel VBA. I want to add multiple worksheets in the same workbook and i have to rename it with the values from a range of cells from the Index page. And also each sheet should be hyper linked to the corresponding Cell in the range.

For Eg:

Cell A1 has “Name”, i have to create a worksheet with that name and that created sheet has to be hyper linked with Cell A1.

Thanks

Hi Vivek,

Here is the example program for your requirement.

[code language="vb"]

Sub sbCreateTOCSheetHyperLinks()

iCntr = 5 ' worksheets names starts from 5th row

'loop until the cell is blank

Do While Sheets("Index").Range("A" & iCntr) <> ""

'If you want to add new worksheets from last worksheet

Sheets.Add After:=Sheets(ActiveWorkbook.Worksheets.Count)

ActiveSheet.Name = Sheets("Index").Range("A" & iCntr)

Sheets("Index").Activate

'delete if any existing hyperlink

Range("A" & iCntr).Hyperlinks.Delete

'add hyperlinks

Sheets("Index").Hyperlinks.Add Anchor:=Range("A" & iCntr), Address:="", _

SubAddress:="'" & Sheets("Index").Range("A" & iCntr).Value & "'!A1", _

TextToDisplay:=Sheets("Index").Range("A" & iCntr).Value

iCntr = iCntr + 1

Loop

End Sub

[/code]

You can download the example file form our downloads page: Download Now

You can use our add-in to create better TOC in easy way.

Hope this helps!

Thanks-PNRao!

How to write vba macro to copy data from one sheet to another without using “copy” command ?

Hi Jaimin,

You can use for loop to get the data from one sheet to another sheet. For instance, if you want to get the data of

cells A1:A20 from Sheet2tocells B1:B20 of sheet1.For iCntr=1 to 20

Sheets1.Cells(iCntr,2).value=Sheets2.Cells(iCntr,1)

Next

Its depending up on your requirement. If you want to copy only few cells. you can do it without using for loop.

However Copy command [Sheets2.Range("A1:A10").Copy Destination:=Sheets1.Range("B1)] works more faster, you can copy the data including formats.

Hope this helps!

Thanks – PNRao!

Hi Team,

I want to Split data into multiple worksheets based on column(this column will contain numbers)

with VBA code. It will be more helpful if you would help on this.

Thanks

Hi Jagadeesh,

Please check for the example (Copy Data from One Sheet to Different Sheets:) in the following page:

Excel VBA Downloads

Hope this help you to do your task.

Thanks-PNRao!

Hi,

Really thanks for that, really that helps me a lot….

You are most welcome!

Thanks-PNRao!

Hi,

I am at a beginner level of learning VBA. So far it really helped me a lot at my workplace to drag my manager’s attention. I prefer referring your site and I really love the way you interpret useful info in the simplest way. Your work actually attracted me towards learning more & more of VB. Thank you for your hard work & knowledge sharing!

Hi Joshi,

Thank you for your comments, I am happy that our blog is helping and creating interest towards learning VBA.

Thanks-PNRao!

how to send email through excel using macro or without macro

Hi Nitin,

Please check the following topics discussed about sending emails using macros.

http://analysistabs.com/excel-vba/interact-with-other-applications/

http://analysistabs.com/excel-vba/hyperlinks-examples/

http://analysistabs.com/excel-vba/chart-examples-tutorials/

Thanks-PNRao!

Hi great site – i’m self learning VBA mostly through using the record macro function and analysing the coded output. Trying to make use of the text held within an object to drive an action based on the digit held within this object.

Fantastically built side in terms of content and style

Thanks Paull! Enjoy learning VBA. -PNRao!

Hi,

first of all congrats for the amazing helpful website.

I am wondering if you could help me with this VBA issue:

1) Hypothesize we have in “sheet1″ cell a1, a3, a5, a7 and so on with a gap of 2.

2) I would like a Macro that links (instead of copy if possible) cell b1, b2, b3, b4 and so on of “sheet2″ to cell a1, a3, a5 … of “sheet1″ (basically removing the Gap)

Hope I was clear.

Thanks a lot for your help,

Patrizio

Hi Patrizio,

Thanks for your comments. Here are VBA macros for your requirement.

[code language="vb"]

Sub sbFill_OddRows()

'Declaration

Dim iCntr, jCntr, lastRow As Long

'iCntr to iterate Sheet2

'jCntr to iterate Sheet1

'lastRow for storeing last row with data in Sheet2

lastRow = 25

'Assuming you have data up to 25th row in Sheet2

'If the last row is not fixed, then check

'our most useful vba examples to find Last row

'in different scenarios

jCntr = 1

For iCntr = 1 To lastRow

Sheet1.Cells(jCntr, 1) = Sheet2.Cells(iCntr, 1)

jCntr = jCntr + 2 'increasing jCntr to skip one row

Next

End Sub

'*To fill even rows: Same as above with one change

Sub sbFill_EvenRows()

Dim iCntr, jCntr, lastRow As Long

lastRow = 25

jCntr = 2 ' 1 to fill Odd rows, 2 to fill Even rows

For iCntr = 1 To lastRow

Sheet1.Cells(jCntr, 1) = Sheet2.Cells(iCntr, 1)

jCntr = jCntr + 2

Next

End Sub

[/code]

Hope this helps, let me know if you need more clarification.

Thanks-PNRao!

Hi,

I want to insert multiple objeats using VBA,Please help me on this.

Ex:Coulamn c is having the 100 objects names,I want to search in my system and i want to insert repect to that coulmn.

Hi Sivakumar,

Thanks for writing us! Could you please specify your problem in more detailed.

I understand that you will have your object names in C column of on of the sheet and you want to insert them just right to that respective cell. The thing I could not understand is Object: What are you referencing as object? is this a image?… please elaborate your question.

Thanks-PNRao!

hi this is my query, could you please help

i have these percentages

35%,40%,25% and then i have a value say 50,000

i have made a tab on which i click and it should calculate the percentages itself

will the same macro run for multiple entries?

Hi Ashish,

I am assuming you have your Value at A1 and Percentages at B1,B2,B3 and you want to print the percentage of that values in C1,C2,C3.

for this you can use simple formula in C1=A1*B1/100 [Example, if you have 200 in A1 and 10 in B1, this will pront 20 in C1]

Sorry, your question is not clear, please provide more information. So that I can help you.

Thanks-PNRao!

Hi Team,

I want to extract data from MSSQL server between two date and this date must be insert by user in the text box of a vba form so give me an example where we take the date from text box n put it on SQL query and direct extract the data from the server.

Thankyou

Hi,

Please refer the below articles:

http://analysistabs.com/excel-vba/ado-vba-connecting-database-sql/

http://www.mrexcel.com/forum/excel-questions/617788-visual-basic-applications-connect-sql-server.html

Change the sqlString as:

“Select * From TableName where DateVariable Between ” &inputbox1.value &” and ” &inputbox2.value

Thanks-PNRao!

Hi,

Can you please show me the code to insert alphabets from A to Z in Horizontally and vertically.

Thanks,

Sravan

Here you go…

Sub sbPrintAlphabetsVertically()

`For iCntr = 1 To 26`

Cells(iCntr, 1) = Chr(64 + iCntr)

Next

`End Sub`

`Sub sbPrintAlphabetsHorizonatally()`

`For iCntr = 1 To 26`

Cells(1, iCntr) = Chr(64 + iCntr)

Next

`End Sub`

Thanks-PNRao!

Hi

I am new to VBA can u please show me the code to search amount with negative sign in particular column and move it next/ before that column.

Thanks in advance,

Aslam

Hi Aslam,

You can just check if the Column A value is <0 then print into Column B. Here is the Example:

Sub sbPrintNegativeColumnAValuesInToColumnB()

Dim iCntr, jCntr, lastRow As Long

lastRow = 50 ‘ Last Row of Column A with data:You can change this

For iCntr = 1 To lastRow

If Cells(iCntr, 1) < 0 Then Cells(iCntr, 2) = Cells(iCntr, 1)

Next

End Sub

Similar to PNRao response. I want to selectively pick data out of a column (the picks will be stepped apart by 3) , select the picks and paste to another column in contiguous fashion (meaning continuous-no step). This works in manually recording a macro and running it but I cannot make it work with programmed VBA language. Excel 2003.

Thanks

arc

Hi Arc,

You can use STEP statement. Here is the example code:

Assuming you have data in Column A: A1,A4,A7,…..

And you want to print in Column B: B1,B2,B3…

Sub sbPrintColumnAByStep3InColumnB()

Dim iCntr, jCntr, lastRow As Long

lastRow = 50 ' Last Row of Column A with data:You can change this

jCntr = 1 ' Counter for Column B

For iCntr = 1 To lastRow Step 3

Cells(jCntr, 2) = Cells(iCntr, 1)

jCntr = jCntr + 1

Next

End Sub

Thanks-PNRao!

Great Site!!

Very Helpful!!

Thanks a Lot!!

Hi gcpathalla,

Thanks for your comments.

Thanks-PNRao!

Hi, I am beginner, please advise me how to create tracker in VB using excel sheet. In a Excel sheet there are data in 5 column with titles, wanted to create Title on VB template. If I search with 1st Co data rest 4 columns data should be reflect e.g Column A Contain Dates, Column B contains Days, Column C Contains Activity etc

If the VB template if I input Dates Field all 4 Fields reflects relevant data

Regards

Satish Telgote

Hi Satish,

You can use Vlookup formula to achieve this. If you want to build your own function, you can loop through the each row and display the column data wherever its matching the given criteria.

The sample code looks like this:

Dim dtAVal as Date

Dim lastRow as Long

Dim blnFound as Boolean

blnFound =False

dtAVal = cdate(InputBox1.value) ' Your input date to find the respective data

lastRow=200 'Your last row in the worksheet

`'looping through all rows and trying to get match row number`

For iCntr=1 to lastRow

If Cells(iCntr,1)=dtAVal then

blnFound =True

exit For

End if

Next

`if blnFound =True then`

'Fill the respective data into other columns

TextBox2.Value=Cells(iCntr,2) 'Column B

TextBox3.Value=Cells(iCntr,3) 'Column C

TextBox4.Value=Cells(iCntr,4) 'Column D

`end if`

Hope this helps!

Thanks-PNRao!

Hi, i heed an help, as i need coding , when some one click in txt box, then time should start, and when click on submit, should stop the time,

Hi Gajanan Ashok Pujari,

You can create two variables on module level variable 1 to capture the time on text box click. And the variable 2 is to capture the time on Submit button click. Now you can get the time difference of the variable 1 from variable 2.

Hope this helps!

Thanks-PNRao!

Hi!

Also very much a beginner! I am trying to link excel to ppt using VBA, more precisely I want to be able to automize “moving” the value of one cell in excel to a specific location in my ppt presentation. Hoping this is possible and if so I could really use some help.

Thank you in advance,

Malin Tedesund

Hi Malin,

Thanks for visiting our blog. PPT automation is 100% possible with Excel VBA.

I am assuming, you want to export data/figures from Excel to particular slide in the PPT with required format. We can automate anything with PPT using Excel VBA, we can design a tool to do this task.

Please let us know your requirement, you can send the files and requirement to info@analysistabs.com.

Thanks-PNRao!

Hi,

As I visit this website this is the best website for VBA developers.

I have a problem I have a folder with about 700 excel workbooks as telephone bills. I want to first divide all workbooks into proper folders. how can I move these files in to proper folder when I run the macro first it ask me about the Source Folder, second wants to select the files then it ask the Destination folder. if it is possible please help me in this regard.

Thanks in advance,

Lailuma

Hi Lailuma,

You can do this as explained below:

Create 3 buttons in Worksheet

Button1:Place at Range A3 – This is to select the Source Folder: Use the File dialog to pick the folder name and put the folder path into Range B3[vb]

Set fldFolder = Application.FileDialog(msoFileDialogFolderPicker)

With fldFolder

.ButtonName = "Hello choose a Folder Now"

.Title = "Choose a Folder"

.Show

Range("B3")=.SelectedItems(1)

End With

[/vb]

Button2:Place at Range A4 – This is to choose your files from the source folder: Use the File dialog to pick the multiple file names.[vb]

Set fldFile = Application.FileDialog(msoFileDialogFilePicker)

With fldFile

.ButtonName = "Hello choose your Files Now"

.Title = " Choose an Excel File"

.Show

For iCntr= 1 to .SelectedItems.Count

Range("B" iCntr+4 )=.SelectedItems(iCntr)

Next

End With

[/vb]

Now you have your file names ready to move into a another folder.

Button3:Place at Range A5 – This is to choose your destination folder. use the the same method to choose a folder shown for Button1. And loop through the files in your worksheet and move the file.See the file handling examples to move the files from one location to another location.

Hope this helps.

Thanks-PNRao!

This website is really great! I am looking to compile cell A1 from 50 different tabs in 50 different spreadsheets that are all in one folder, into one column on one spreadsheet. Please help!!

Hi Adam,

You can loop through all the files in the folder and then fetch the range A1 and put it in your destination sheet.

[vb]

Sub sbLoopThroughAllFilesInFolderGetData()

Dim StrFolder As String

StrFolder = Dir("c:\temp\") ‘ Your folder name

iCntr = 0

Do While Len(StrFolder) > 0

iCntr = iCntr + 1

Set wb = Workbooks.Open(StrFolder) ‘open each file here

Cells(iCntr, 1) = StrFolder ‘ this your file name

Cells(iCntr, 2) = wb.Sheets("Sheet1").Range("A1") ‘ this your data from Range A1

StrFolder = Dir

Loop

End Sub

[/vb]

Thanks-PNRao!

Hi I have worked with the above code unfortunately it is not working an give “Run-time error ’1004′: Method ‘Range’ of object ‘_Worksheet’ Message. if it is possible could you please write me the entire code.

Thanks,

Lailuma

Hi Luiluma,

Could you please let us know the code which you have tried. And the Office Version which you have tried. So that we can help you to fix the issue.

Thanks-PNRao!

I want to open text document in excel which is pipe saperator and last column as date and time i want date and time saperately while clicking command button

Hello,

I learnt many things from given Example.

Many Thanks

Thanks and Regards,

Rajeev

Hi Rajeev, Thanks for your comments – PNRao!

Some other features of Microsoft Office 2010: microsoft

Publisher 2010 Access. There are several ways you can run your

brand new macro. Connecting your employees with specific information and expertise.

Feel free to surf to my web site: Microsoft Office 365 serial number, Lucia,

Hey.. Thank you for your very helpful website. I am new to VBA and i am now stuck at this problem. I have a big data for temperature.

1. 21.40

2. 21.45

3. 21.38

4. 22.89

5. 23.27

……1000. 85.54

and so on. And i put these data in Column A. So for each temperature, i want to assign their density and heat coeff. values in Column B (respective to the temperature). So i try this:

Dim Temp As Range, Density As Double

Set Temp = Range(“A1:A1000″)

Temp = Range(“A1:A1000″).Value

If Temp = 21 Then

Density = 998.08

ElseIf Temp=22 Then

Density = 997.86

ElseIf…(I do until the required Temp)

End If

Range(“B1:B1000″).Value = Density

And i got mismatch error. Could you please help me?

Thanks.

Regards,

Ary

Hi Ary,

You can’t put everything in one-shot, use the for loop instead.

`Sub temperature2HeatCoeff()`

Dim iCntr As Long

Dim Temp As Double, Density As Double

For iCntr = 1 To 1000

Temp = Range("A" & iCntr)

If Temp = 21 Then

Density = 998.08

ElseIf Temp = 22 Then

Density = 997.86

'ElseIf…(I do until the required Temp)

' ----

End If

Range("B" & iCntr) = Density

Next

End Sub

Thank you very much. With your help now i can do it

You are welcome Ary!

Hi PNRao,

I need some help on this. Okay, as example i have data in excel and this is only a part of them:

Temp : (21, 22, 23, 24, 25, 26, 29, 30, 26 25, 24, 23, 22), (24, 25, 26, 30, 27, 28, 29, 25, 21, 19), (20, 22, 23, 36,30, 34, 35, 30, 25, 23), (24, 26, 30, 34, 28, 25, 20)

These are temperature of a liquid. The temperature are increasing until the peak and then go down until certain temperature (one cycle). Then it is increasing and goes down again(next cycle). In these example there are 4 cycles. And it repeats until i have a few cycles.

For every each of the temperature, i can calculate its volume.

I try to write code to detect these cycles because i want to calculate the average volume for each cycle. But i don’t have idea how to start since i am new to VBA.

Thank you

Hi Ary,

Here is the code, I am assuming your volume data in Column A (I pasted all your data in the column A from row 1 to Row 40)

Sub CycleAverage()

Dim lRow As Long

`lRow = Range("A" & Rows.Count).End(xlUp).Row`

'Find

totSum = 0

totCount = 0

AvgVolCntr = 1

For iCntr = 1 To lRow

totSum = totSum + Range("A" & iCntr)

totCount = totCount + 1

`'If New Cycle Starts : When I find series of values some thing like this: 2-1-2 or 9-3-4 or a blank`

If iCntr > 2 Then

If (Range("A" & iCntr) > Range("A" & iCntr - 1) And _

Range("A" & iCntr - 1) < Range("A" & iCntr - 2)) Or Range("A" & iCntr) = "" Then

`'print Averages in Column D & E`

Range("D" & AvgVolCntr) = "Cycle: " & AvgVolCntr

Range("E" & AvgVolCntr) = totSum / totCount

`'and set totals to zero`

totSum = 0

totCount = 0

`'increase AvgVolCntr`

AvgVolCntr = AvgVolCntr + 1

End If

End If

`Next iCntr`

`End Sub`

Hope this helps! Thanks - PNRao!

Thank you so much. This helps me a lot

Hi, I’m trying to get part of a tag out of a cell into a new cell, the tag looks like this: “xxx-xxx-xxx-xxx-xxxxxx” or some look like this: “xxx-xxx-xxx-xxx-xxxxxx-xxxxxx”, I just want to get the last part of the tag (after the last “-”) into a new cell, I have +-35000 tags to do this with. can you tell met how to go about please.

Hi Charl,

You can find last sting using this VBA code:

Assuming that you have your data in Column A. And printing the last string in Column B

Sub find_Last_String()

Dim lrow As Long

`'find last row`

lrow = Range("A" & Rows.Count).End(xlUp).Row

`'process from first row to last row`

`For iCntr = 1 To lrow`

MyString = Cells(iCntr, 1)

findLastHypen = Len(MyString) - InStr(1, StrReverse(MyString), "-")

finalString = Mid(MyString, findLastHypen + 2, Len(MyString) - findLastHypen)

'print in Column B

Cells(iCntr, 2) = finalString

Next

End Sub

`Hope this helps! Thanks – PNRao!`

Thanks PNRao, this is exactly what I needed.

Everything is very open with a very clear description of the

challenges. It was definitely informative. Your website is

useful. Thank you for sharing!

Hi Evan, Thanks for your comments-PNRao!

I want a macro for replacing all the names same as in one cell say with a name in another cell.

Hi Sumit,

Assuming Range A1 is having the name to find, Range B1 is having the Name to be replaced with.

The following code will find the name mentioned at Range A1 and Replace with the name mentioned at Range B1:

Sub VBAToReplaceAString()

strToReplace = Range("A1").Value

strReplaceWith = Range("B1").Value

Cells.Replace What:=strToReplace, Replacement:=strReplaceWith, LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Range("A1").Value = strToReplace

End Sub

Hope this helps-Thanks-PNRao!

Very Nice website, Thankyou Mr Rao for your support & great efforts, its simply Great Effort…

Thank you Mr. Deependu – We are very happy to receive such a sweet feedback from our reader.

Thanks-PNRao!

Dear Sir,

I would like to register for the Aug 2014 course. Upon entering my e-mail id, i did not receive any mail from your site. Could you please let me know the procedure to register for the VBA macro course.

Thanks,

Krishna M

You’r registration is successful, and I can send the discount detail before starting our online classes!

Thanks-PNRao!

hi i m new to VBA…could you please tell me how to do coding for BFS algorithm in excel?please reply ASAP..

Hi, I need some help:

Given:

- The stock price is 68.

- The risk-free rate is 0.05.

- The stock’s expected return is 0.15 when jumps are ignored.

- The stock’s volatility is 0.3.

- When jumps are ignored, stock prices are lognormally distributed.

- The annual number of jumps follows a Poisson distribution with l = 1.8.

- Jump magnitudes are lognormal with aj = -0.1 and sj = 0.3.

Using control variate method to estimate the value of an European call option with 0.5 year to maturity and strike price of 70. Given 95% confidence interval as answer on the spreadsheet with proper statement and annotation.

Report on the estimate by (i) crude method (ii) control variate method and (iii) efficient of the control variate method over the crude method for 5000 simulations.

Thanks!

Hi Joey,

Could you please provide me sample file. So that it is easy to understand your problem to help you.

Thanks-PNRao!

Say I have 18 files in a folder and I need to plot the 5th column of data each file onto the same graph. Any advice on this? Thanks so much!

Hi Brett,

You can do this using formulas, if your data ranges are always fixed. Other method is using VBA, you can write a simple macro to retrieve the data from all those file and plot a chart.

Thanks-PNRao!

hi,

I’m trying to sum a column/range within a column or row using VBA. I’d like to use the worksheetfunctions SUM, but I can’t seem to get it to work correctly. In fact I keep getting an error. Maybe I should be using something else? Please help.

Thanks – Roopert

Hi PNRao,

I have an excel project that I am currently working on and I want to use VBA. I want to change the font color of all negative values, like for example column A, B and C. I update these values every month so the the value for let say, row 1 might be positive this month but negative next month. Just the negative values that i want the font color be changed into red and all the positive values remain black. Sorry, I am just new on VBA and macro.

Thanks in advance PNRao!

Hello Sir,

Need your help?

I want to pull all the data from sheet1 to sheet2, using a commandbutton and two textbox in which i want to give the date range (such as 09/01/2014 to 09/25/2014) and will pull the complete information between that range from sheet1 (that is master sheet) to Sheet2 (that where I want the data between that date range).

Thanks

Hi Hardeep Singh,

Assuming that you have data in sheet1 in Column A and B and you want these records into sheet2 based on given date range.

If you have date in Column A:

startDate = “09/01/2014″

endDate = “09/25/2014″

lRow = 100 ‘ This is thelast row in sheet1: please refer our 100+ codes if your last row is not fixed.

jCntr = 1

For iCntr = 1 To lRow

If Format(Sheets(“Sheet1″).Range(“A” & iCntr), “dd/mm/yyyy”) >= startDate And Format(Sheets(“Sheet1″).Range(“A” & iCntr), “dd/mm/yyyy”) <= endDate Then

Sheets(“Sheet2″).Range(“A” & jCntr) = Sheets(“Sheet1″).Range(“A” & iCntr)

jCntr = jCntr + 1

End If

Next

Hope this helps.

Thanks-PNRao!

Hi Rao,

Help me with a script to add a comma after every 2words in a cell.

Eg: Column A (How to add a comma after every two words in excel); so the result in Column B(How to, Add a, comma after, every two, words in, excel)

Also another script: Column A (How to split cell after every three words and result in four different column and the rest in last column)

so the result will be in Column B(How to split); Column C(Cell after every); Column D(three words and); Column E(result in four different column and the rest in last column)

Answer for your first query:

Sub sbUsage()

lRow = 20 ‘Last row with data in Cloumn A

For iCntr = 1 To lRow

Range(“B” & iCntr) = fnCommasEvery2TwoWords(Range(“A” & iCntr))

Next

End Sub

Function fnCommasEvery2TwoWords(ByVal strText As String)

arrText = Split(strText)

If UBound(arrText, 1) >= 2 Then

strText = “”

For iCntr = 0 To UBound(arrText, 1) Step 2

If iCntr = 0 Then

strText = arrText(iCntr) & ” ” & arrText(iCntr + 1)

ElseIf iCntr + 1 < = UBound(arrText, 1) Then

strText = strText & ", " & arrText(iCntr) & " " & arrText(iCntr + 1)

Else

strText = strText & ", " & arrText(iCntr)

End If

Next

End If

fnCommasEvery2TwoWords = strText

End Function

I hope, now you can solve the second query yourself.

Thanks-PNRao!

Hi Rao,

Thank you for your help but am getting error..

Compile Error: Syntax errors…and “Cannot execute in break mode”

Hi Kay,

Could you please post the VBA code which you are trying.

Thanks-PNRao!

Greetings,

I am trying to develop a tax calculator using Excel VBA. I request you to assist me with a code for a separator for every three digits, for instance 1,000,000 and 234,456 etc.

I will be very grateful if my request is considered.

Hi James,

You can use number formats:

Assuming that you have 1000000 in Range A1:

Sub GFormatNumbersWithCommas()

MsgBox Format(Range(“A1″), “#,##0″)

‘OR

Range(“B1″) = Range(“A1″)

Range(“B1″).NumberFormat = “#,##0″

End Sub

Thanks-PNRao!

heloo

i am new in vba. i want to write a program that subtracts such that it can carry over from another cell in relative mode. eg, i want to subtract 2003 6 18 from 2014 10 19 such that it takes carryover a month that is adding up 12 in second cell and 30 days in another cell. all these number are in different cells.

thank you in advance

It’s Great place to learn excel programming… I am very new to this site.

I have a question, please help me on this.

From this place I learnt how to hide and unhide excel sheet via VBA. Now my question is I want to hide sheet in excel and should open by using activex button and close the sheet completely after review. Is it possible to do it in VBA please help me.

Hi Vasu,

You can use the same method to hide the sheets.

Follow the below process for your requirement:

-> Enter 0 at some range (Example at Range A1000)

-> Add two activeX controls (Review Now, Complete)

-> when user press the Review Now, check if range A1000= 0 then un hide the sheet to review

-> when user press the ‘Complete’, enter 1 at A1000 and hide the sheet

Hope this helps!

Thanks-PNRao!

Hello,

I am beginner to VBA, and I am trying to do some automation in my project,… Please help me.

I have around 20 to 25 tabs in my excel file and I want to hide all this only on pressing a button I need this tab to open then there should be close option which hides the file again.

could you please help me with code for this.

Regards,

Vasu

Hi Vasu,

You can not hide all sheets at any particular time, at least one sheet should be opened. Follow the below approach.

-> Have a Home worksheet

-> here you can provide the environment to choose a sheet and provide button to open it

-> the below macro will help you to do this:

Sub sbHideAllExceptOne()

shtToShow = “SheetToShow” ‘Change this as per your requirement

Sheets(shtToShow).Visible = True

For Each sht In ThisWorkbook.Worksheets

If sht.Name shtToShow Then sht.Visible = False

Next

End Sub

Thanks-PNRao!

I am looking for an code to send email automatically, when a range of cells are changed in an excel sheet(irrespective of Alphabets or numericals).