VBA Code Excel Macro Examples Useful 100+ How Tos for Basic and Advanced Users

Hi Chaitanya,

Welcome to ANALYSISTABS!

Please refer Example 33 the following page:

How to Add and Send an Excel Chart to an Outlook email

Thanks-PNRao

You are most welcome!

Thank You! Enjoy Learning @ ANALYSISTABS.COM

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

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,

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

Thanks
Maahendra

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

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

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!

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!

You are most welcome!

Thanks-PNRao!

Hi Joshi,

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

Thanks-PNRao!

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!

Thanks Paull! Enjoy learning VBA. -PNRao!

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 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 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-sql-macros-connecting-database/
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

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 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!

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 gcpathalla,
Thanks for your comments.
Thanks-PNRao!

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

end if


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 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 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!

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 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!

Hello,
I learnt many things from given Example.
Many Thanks
Thanks and Regards,
Rajeev

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,

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


You are welcome Ary!

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

Hope this helps! Thanks – PNRao!


Thanks PNRao, this is exactly what I needed.

Hi Evan, Thanks for your comments-PNRao!

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!

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

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

Thanks-PNRao!

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!

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

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

Hi Rao,
Thank you for your help but am getting error..
Compile Error: Syntax errors…and “Cannot execute in break mode”

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!

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 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!

Thanks PNRao

Really it’s good website. Hope you are not updating on Facebook. Every day at-least post a tip or code which will make the blog or our forum & we all mutual friend have scope to have a chat
Even I have same query if i have a Excel sheet close I wish to copy the data & close the file need code in VBA.

Hi sravan, Thanks for suggestions. Yes, I am thinking to Best ways to create a best VBA forum to discuss our ideas. I am working on it.
I am going to launch it soon.

Hi Mr Rao,

i am working in excel since 2005, i just wanted to learn VBA, please guide

Thanks,
Kalim

Hi Mr. Rao,

I need to move one excel sheet from one folder to another while the status box in one of the sheets changes to completed. The problem is, the excel sheet has got dataconnection from another sheet. I do not want that data connection to be affected, while moving it to the new folder. Could you please help me here.

I have a bunch of data on one sheet, I need a macro to sort all the data to separate sheets based on the data in Column “H” for example. Column “H” is item location, I need all items from Location A to be sorted to its respective sheet. I need a macro that is going to be efficient as there will be thousands of lines that need sorted. I am not very familiar with VBA so Any Help would be greatly appreciated.

iam very new to thisVB,can you explan me

Hi PNRao,
Congretulations for your work.
Could you please help me with the following problem?

Background:
I have an exel worksheet (36 columns and 13102 rows) which have to be distributed daily basis to other 4 people in order to bring them input and to be send back.
Action:
All these worksheets together must update a master worksheet from whrere a report will be issued.
Question:
Which macro do I need to get an automatically update from all worksheets into master worksheet (as final version).

Thank you very much,
Adrian

Hello Rao,

Please send me a email id for which I can send a sample presentation.
Because the email sent to ‘info@analysistabs.com’ is giving delivery failure message.

Looking forward your email id for which all details can be sent.

Regards
Shrikara

Hello PNRao,

Im very new to VBA Excel programming could you help me with the following problem

I have the following code with a button to generate respective plots with data. now i have to write a small code to Change the Format of the plot which is Default (Black dotted line) i need a code like to add a check box for the data with a button to chnge the Format and colour of the line maually could you help me through this.

Im very thankful if you provide a code for me.

Thank you :)

Private Sub CommandButton1_Click()

Set nwsht = Sheets.Add(after:=Sheets(Worksheets.Count)) ‘add a new sheet

‘ nwsheet.Activate

ActiveSheet.Range(“B3:c4”).Select ‘basic formatting legende
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Value = “Legende”
.Font.Bold = True
.ColumnWidth = 5
End With

ActiveSheet.Range(“B6:c7”).Select ‘basic formatting legende
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Value = “Akustik”
.Font.Bold = True
.ColumnWidth = 5
End With

ActiveSheet.Range(“B9:c10”).Select ‘basic formatting legende
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Value = “Werte”
.Font.Bold = True
.ColumnWidth = 5
End With

ActiveSheet.Range(“D1:d500”).ColumnWidth = 1 ‘basic formatting
ActiveSheet.Range(“G1:G500”).ColumnWidth = 1 ‘basic formatting
ActiveSheet.Range(“E3:F4”).Select ‘basic formatting legende cell

With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ColumnWidth = 20
.Font.Bold = True
End With

ActiveSheet.Range(“E5:G5”).RowHeight = 5 ‘basic formatting
ActiveSheet.Range(“E6:F7”).Select ‘basic formatting Ordnungen cell
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ColumnWidth = 20
.Font.Bold = True
‘ .Value = “Gesamt”
End With
ActiveSheet.Range(“E8:G8”).RowHeight = 5 ‘basic formatting

ActiveSheet.Range(“E9:F10”).Select ‘basic formatting Ordnungen werte cell
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ColumnWidth = 20
.Font.Bold = True
End With
ActiveSheet.Range(“E11:G11”).RowHeight = 5 ‘basic formatting

ActiveSheet.Range(“E12:E13”).Select ‘basic formatting drehzahl cell
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.RowHeight = 20
.Value = “Drehzahl [rpm]”
.Font.Bold = True
End With
ActiveSheet.Range(“F12:F13”).Select ‘basic formatting drehzahl cell
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.RowHeight = 20
.Value = “dB[A]/dB[lin]”
.Font.Bold = True
End With
ActiveSheet.Range(“E14:G14”).RowHeight = 2 ‘basic formatting

ActiveSheet.Range(“E6″).Select ‘data validation for Ordnungen
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=moeglischplots”
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ”
.ErrorTitle = ”
.InputMessage = ”
.ErrorMessage = ”
.ShowInput = True
.ShowError = True
End With
ActiveSheet.Range(“E6”).Value = “Gesamt”

ActiveSheet.Range(“$Z$27”).Value = “Datei”
ActiveSheet.Range(“$Z$27”).Font.Color = RGB(255, 0, 0)

ActiveSheet.Range(“$Z$28”).Value = “Gesamt”
ActiveSheet.Range(“$Z$29”).Value = “Ordnungen”
ActiveSheet.Range(“$Z$30”).Value = “Oktaven”

ActiveSheet.Range(“$AA$28”).Value = “Ordnungen”
ActiveSheet.Range(“$AA$29”).Value = “Ordnungen”
ActiveSheet.Range(“$AA$30”).Value = “Oktaven”

ActiveSheet.Range(“$AB$28”).Value = Application.WorksheetFunction.VLookup(ActiveSheet.Range(“E6”), ActiveSheet.Range(“Z28:AA28”), 2, False) ‘ SVERWEIS(E6,$Z$28:$AA$30,2,FALSCH)] ‘vlookup für gesamt als ordnungen

ActiveSheet.Range(“E9″).Select ‘data validation for indirekt
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=INDIRECT($E$6)”
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ”
.ErrorTitle = ”
.InputMessage = ”
.ErrorMessage = “Kein Zugriff”
.ShowInput = True
.ShowError = True
End With

ActiveSheet.Range(“I2:J7”).Select ‘space for button
Selection.Interior.ColorIndex = 2
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThin

ActiveSheet.Range(“B3:f4”).Select ‘legend box
Selection.Borders(xlEdgeTop).LineStyle = xlDash
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeLeft).LineStyle = xlDash
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeRight).LineStyle = xlDash
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeBottom).LineStyle = xlDash
Selection.Borders(xlEdgeBottom).Weight = xlThin

ActiveSheet.Range(“B6:f7”).Select ‘akustik box
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThin

ActiveSheet.Range(“B9:f10”).Select ‘werte box
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThin

ActiveSheet.Range(“E15:f1000”).Select ‘zahlen box
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThin

ActiveSheet.Range(“E12:f13”).Select ‘zahlen box
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThin

ActiveSheet.Range(“A15:D15”).Select ‘copy hinweis
Selection.MergeCells = True
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.Font.Bold = False
Selection.Font.Size = 7
Selection.Value = “datei ab hier kopierien”
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).Weight = xlThin

ActiveSheet.Range(“I12:K13”).Select
Selection.MergeCells = True
Selection.Value = “Lastenheft Datei”
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.Font.Bold = True
Selection.Font.Size = 16
Selection.Font.Italic = True
Selection.Borders(xlEdgeTop).LineStyle = xlDash
Selection.Borders(xlEdgeTop).Weight = xlMedium
Selection.Borders(xlEdgeLeft).LineStyle = xlDash
Selection.Borders(xlEdgeLeft).Weight = xlMedium
Selection.Borders(xlEdgeRight).LineStyle = xlDash
Selection.Borders(xlEdgeRight).Weight = xlMedium
Selection.Borders(xlEdgeBottom).LineStyle = xlDash
Selection.Borders(xlEdgeBottom).Weight = xlMedium

Call Lastenhefteingufen.CreateButton

End Sub

Just want to ask how can I create a very simple accounting system using vbA where i can input data and generate then after.

Hello,
i need a code to check a loop for multiple range name aa, ab, ac..for range e1:r24, s1:af24, ag2:at24 logically & give output range based on cell value, till the loop ends. In output i want to limit the worksheet size to 74 columns & then it should move to the next blank row..

Hello,

I am comparing a series of different materials across different year with varying amounts of dollars spent depending on the type of “contract”. These are then followed by 3 different teams, which each contain many people who “supply” the teams. I want to make these into a series of drop down menus. Meaning that once you click on one drop own menu, the next menu ill give you another choice.

For example

•Metal: Aluminum
•Forecasted Change in Year 17: 10%
•Type of Contract and it’s corresponding Spend 100,000 for fixed contracts, $50,000 for adjustable, etc.
•Team: 3 teams…each has roughly 60 suppliers who supply each team. Each supplier also has 4 sets of data to follow it (individual total spend, adjusted spend, and two more for anything that may come up).

Is this possible? I know this is a pain, but I’m hoping anyone might be willing to help me. Thank you!

hi to all
i have a ?
How can disabled the click event in the worksheet in Excel 2007 ?
tanks for all user

Hi,

I am using Excel 2007, I have a drop down box so users can select ‘Yes’ or ‘No’. If ‘Yes’ is selected how do I create a pop up message asking the user to complete a particular question (i.e. now complete Question 2), but if the user selects ‘No’ from the drop down box the pop up message will ask the user to complete a different question (i.e. now complete Question 3), please help!
Many thanks VSharp

I am trying to get a message box to appear if someone enters date in a cell that is not empty. I would like it to ask if they really want to change the value (yes, no) if Yes, allow change if No, end routine. If cell is empty then just allow input.

Is this possible.
Thanks in advance for any help you can offer.

HI,
We have 3 sheets.

Sheet1:
Features John
a L1
b L2
c
d L4

Sheet2:
w x y z
a 1
b 1
c 1
d 1

Sheet3:
Area John
w L1
x
y
z

Here a,b,c,d are features and w,x,y,z are areas. Sheet-2 is the mapping between feature and area. Suppose, in feature “a” John has L1 level. Now, we will check in Sheet2. In Sheet2 for “a” we need to check the nonblank cells, so here for “a” we have nonblank cell in “w” column. That means “a” has relation with “w”. So, in Sheet-3 “L1” value is stored in “w” row for John, like I have put “L1” in “w” row for John.
We have to design a button to fill the entire Sheet-3 on a single click.
Please help me.

Thanks.

Hi, Thank you so much for the info it is really helping me out. but however, i am stuck with a situation where, i’am trying to merge few top and few bottom rows into one. For example,

Names Status
abc active
abc active
cbv closed
dfg closed
abc closed

Here, how can i merge all those “abc” into one column without loosing any data?
any help in this would be appreciated.

Hi Everyone,

I want to export comma(,) in to a text file using vba script using any function writeline and write .

Thanks,
Bala Viswanath.

Hello pn Raw,
I want to learn vba and I know very well advance excel and now I working as a mis executive .
I don’t know anything about vba so how to start vba can u give me basic vba book or any types of material that will help me my email I’d

Thank you very much for sharing this article!!

HI,
i want to know about

in my excel sheet have some data name location amt large data
i want add time from one application want to add automate time from application to my excel sheet.for each transaction

Thank you very much. This is an excellent collection of useful VBA code!

hi
i have a queri about vba
i have data sheet in data sheet i need to filter for date and need to add formula after 3 column .
for example ab column date filter then want formula in ad column equal to .

need suggestion

regards
vishal jadhav

Hi how can I open only one instance at a time in excel 2010 using VBA code, any idea please?

Hi, I am Nabi, I have an excel macro VBA stock sheet but my Exit month wise item under report do not work when I click the Exit month button and try to see month wise report on that time report do not show and I got msg. box. The msg box has show report not available. So plz help me how can show my month wise (exit item report).

Hi,

I am new to VBA. And i feel interesting to study VBA while gone through this site.

Now i try to modify my little projects in excel into in terms of VBA. My project is simple,

I have a set of standard values in a sheet. With reference to this standard values, my result after 1 production is analysed for each row. Each row contains different item so i need to set different formula. Fortunately i had done it well :) . .

Now my aim is,
1) Copy each row into new sheet, (for eg: row1= A1:K1, row2= A2:K2…..row1=Ai: Ki). i need to copy only 1 row into new sheet…and each row contains formula. i need to copy this formula too. And the sheet name changes to item name( in first the column)

2) Now i have 89 items (i.e.rows) to copy to different sheets. in the second sheet in my workbook is a commend box. where , if i click on a command box, its name is an item name, then corresponding sheet will activate. and i can edit it. all other sheets are on hide.

3) if I enter 90th row once, these above procedures must be followed.

So if i enter details in 90th row, and then click on command box in the same sheet, a new sheet with a copy of 90th row only generate. also this worksheet is saved under another command box on the second sheet.

hoping you will understand what i mean.

your earliest reply will be highly appreciated.

thank you very much

Noufi

QUESTION:- RANGE A1 AND COLUMN 1 AUTOFILTER CRITERIA :- “04:00 PM”
IF RANGEA1&LASTROW.VALUE=”04:00 PM” THEN RANGE B2&LASTROW.VALUE= IBYB
IF RANGE A1&LASTROW.VALUE=”03:30 PM” THEN RANGE B2&LASTROW.VALUE=REMITTANCE
END IF END IF

GIVE ME SOLUTION.
VISHAL

Hi,

We are trying to build dashboard based on various types of data in excel sheets All these excel files will be located in specified path. All these files have commonly one unique field . Also , i have one more excel file which contains master list of that unique field.

For example :

I have 5 sheets in one specified folder.
all the files having one Project code as unique field.
However I need to create dashboard based on the portfolio under which the above projects are falling.
Like : for a project code “PPP” i have mapping of portfolio as “ABC”. This data will be stored in one excel file apart from the above mentioned 5 files.

Now i need to generate data based on this mapping and rest 5 excel files and then have dashboards created on top of that.

Need help in having the code for this problem.

thank you.

Hi Suppose I have value 100, 101,105 in column A and column B has different values for eg 20,40,80,60 and I want to fetch the values only those are in front of 100 in Excel 2010.
Can you please advise how can it be done

http://analysistabs.com/excel-vba/codes-examples-macros-how-tos-most-useful-basics-advanced/

http://analysistabs.com/excel-vba/codes-examples-macros-how-tos-most-useful-basics-advanced/?replytocom=52496#respond

Vba timer code

Looking for vba code to run a macro when I click on a cell

Hi Dear Sir/Madam.
How we decrease input value equal to zero. Thanks in advance.

Hi,

I want help to write an output report in VBA thru the input data
Can you help with the code

Hi I have a data in Sheet 1. I need to copy the data’s in the range of dates & description.
ex : From Date : 1st Oct 2017, To Date : 31st Oct 2017, Description : Fruit. I need to copy this data from Sheet 1 to New Workbook using excel user form.

Hello,

I need to take data stored in Excel files and populate (copy and paste ) the data into web forms, submit the form then loop through the enitre process until I have gone through the entire Excel file.

So the Excel file will have a many number of rows but always 4 colums of data per row.

I then need to:

1) login to the site (),

2) Navigate the site to get to the correct forms

3) Insert the data from Excel into the correct form field (column 1 and 2 data goes to Field 1 & 2, Field 3 to selected from drop down box based on column 3, Field 4 data pasted from column 4)

4) submit the data

5) do the process again and again to complete the entire rows

I want to create a macro for the above task. Hopefully the above points are helpful to understand my requirement.

Please help me
Thanks in advance.

Hi VBA Expert,

I would like to append many workbooks by rows which consist of two sheets “sheet1” and “Sheet2” in to single Workbook without opening any excel sheets.

column length is fixed but rows length may vary.

Please help me to do this task using VBA macro.

Thanks in advance!!!