Learn VBA

tag:blogger.com,1999:blog-3650056753443679332011-11-27T15:49:10.239-08:00Learn VBAViteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.comBlogger171100tag:blogger.com,1999:blog-365005675344367933.post-54952746596231879642009-10-02T18:23:00.000-07:002009-10-02T18:27:15.362-07:00Using Indian Currency Format

Its not easy to create a format for indian currency, but its easy to use a macro which could be able to convert your number into the desired indian currency format.For using it follow the below steps:1. Open the sheet on which you want to apply the formatting2. right click on the sheet tab and select view code3. paste the below code in the white areaPrivate Sub Worksheet_Change(ByVal Target As

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-15849493014184290692009-09-27T05:56:00.000-07:002009-09-27T05:57:11.251-07:00Format the selected range dependent on each cell value

Sub Ifcondition()ActiveSheet.ActivateSelection.ActivateFor Each c In SelectionCell_Value = c.ValueIf Cell_Value = 1 Then With c .Font.Bold = True .Font.ColorIndex = 15 .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End WithEnd IfIf Cell_Value = 2 Then With c .Font.Bold = True .Font.ColorIndex = 5 .Interior.ColorIndex = 13 .

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-71361229436096362872009-09-27T05:55:00.001-07:002009-09-27T05:55:50.109-07:00Cell Formatting on the active cell based on the cell value

I am trying to write an If condition statement, where, if the value is true, then macro 1 should run and if the value is false, then macro 2 should run. I don't want it to be dependent on any one cell.can anyone help? Your help is truly appreciated :) Aniruddha - Orkut UserConsidering the code you want to run is for cell formatting, you can use the below code.Sub Ifcondition_1cell()

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-39321814815943572812009-02-28T03:25:00.000-08:002009-02-28T03:34:02.440-08:00Working with Loops

Why we use Loops in VBA?The purpose of a loop is to get Excel to repeat a piece of code a certain number of times. How many times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this for as many times as there are rows of data), or some specific condition got satisfied(e.g. a cell contains value greater than 50).Loops can be constructed

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-30115709142787240102009-02-21T07:06:00.000-08:002009-02-21T07:10:37.050-08:00Format a Cell

For Formatting a cell for Medium Border on the cell, With White color of Text, Bold style and Black base of the cell, we can apply the following code:Sheet3.Cells(1, 1).BorderAround xlContinuous, xlMedium, 1Sheet3.Cells(1, 1).Interior.ColorIndex = 1Sheet3.Cells(1, 1).Font.Bold = TrueSheet3.Cells(1, 1).Font.Color = vbWhite

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-15918977193715414902009-02-21T06:42:00.000-08:002009-02-21T06:44:31.192-08:00Assigning Value of a cell to some variable

Say we want to assign the value of a cell to variable 'A' thenIf your sheet is activatedFirst MethodA = Range("A1").ValueSecond MethodA = cells(1,1).ValueIf sheet is not activatedFirst MethodA = Sheet1.Range("A1").ValueSecond MethodA = Sheet1.Cells(1,1).Value

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-81591523520146089732009-02-21T06:41:00.000-08:002009-02-21T06:42:17.544-08:00Inserting Value in a Cell

If your sheet is activatedFirst MethodRange("A1").Value = "Value"Second Methodcells(1,1).Value = "Value"If sheet is not activatedFirst MethodSheet1.Range("A1").Value = "Value"Second MethodSheet1.Cells(1,1).Value = "Value"

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-17511690518328078872009-02-21T06:33:00.000-08:002009-02-21T06:38:35.680-08:00Selecting a cell - VBA Code

If your sheet is activatedFirst MethodRange("A1").SelectSecond Methodcells(1,1).SelectIf sheet is not activatedFirst MethodSheet1.Range("A1").SelectSecond MethodSheet1.Cells(1,1).Select

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-61564093440283695032009-02-19T22:29:00.000-08:002009-02-19T22:36:01.078-08:00Copy data from 1 column to another

help required in VBA macro hi guyss.. need some helpI want the VBA code to search the column (F) and copy the value that is less than 0 to column (B), suppose: I have a negetive value in (F6) I want that value to be copied in (B6).. and this should b done with every negetive value, and the remaining will stay untouchedAsked by: USer of OrkutSolution:This macro will work for sheet1 and for column

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-57860871186451031242009-02-19T22:21:00.001-08:002009-02-19T22:22:42.076-08:00Loading and Unloading Userform

For Loading userform1UserForm1.ShowFor unloading userform1Unload UserForm1

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-67629319705489685402009-02-19T22:18:00.000-08:002009-02-19T22:20:28.755-08:00Adding Items to Combo Box

Below is the method to add individual items to combo boxComboBox1.AddItem "Sum"ComboBox1.AddItem "Product"

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-7846859422152089662009-02-16T21:42:00.000-08:002009-02-16T21:45:00.728-08:00How to Reverse characters of a string

Sub REVWORDS()N = InputBox("N:")Dim u(0 To 100) As String 'Defining ArrayA = Len(N)If A > 100 ThenMsgBox ("STRING EXCEEDING THE DEFINED CHARACTER LENGTH OF 100")GoTo 10 'Jumping on some given labeled statementEnd IfSheet1.Cells(1, 12) = ""For i = 0 To A - 1u(i) = Left(Right(N, i + 1), 1) Sheet1.Cells(1, 12) = Sheet1.Cells(1, 12) & u(i)Next i10 End Sub

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-62407164736241407262009-02-16T21:41:00.001-08:002009-02-16T21:42:14.013-08:00COUNTING NUMBER OF RECORDS IN SOURCE DATA

Sub countrecord()'COUNTING NUMBER OF RECORDS IN SOURCE DATAG = 1Do Until Sheet6.Cells(G, 1) = ""G = G + 1LoopH = G - 1 'H is the number of records in the source dataEnd Sub

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-70069332707844810552009-02-14T00:08:00.000-08:002009-02-14T00:12:11.001-08:00Generate a series of N numbers through user input

Sub series_1_to_n()A = InputBox("Enter the last number of series (N):")For I = 1 To ASheet1.Cells(I, 1) = INext IEnd SubThis will result in a series from 1 to N in cell A1 upto the Nth row of column A.For further query or training on Excel and/or VBA, please feel free to contact at info@exceltechconsultant.comexceltechindia@gmail.com

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-80115662767669744692009-02-14T00:05:00.000-08:002009-02-14T00:08:02.370-08:00Generating series from 1 to 50

This is an example of "For Loop"Sub series_1_to_50()A = 50For I = 1 To ASheet1.Cells(I, 1) = INext IEnd SubThis will result in a series from 1 to 50 in cell A1 to A50.For further query or training on Excel and/or VBA, please feel free to contact at info@exceltechconsultant.comexceltechindia@gmail.com

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-60411503626595249862009-02-13T23:40:00.000-08:002009-02-14T00:00:36.694-08:00Adding two numbers through user input

Press Alt + F11 key for VBA editor, where you could write your code.Goto insert and select module. Start typing the below code for addition of two numbers;Sub Add()a = InputBox("Enter first number:")b = InputBox("Enter second number:")c = a + b Sheet1.Cells(1, 1) = cEnd SubNow press Alt + F8 to open macro window. Select 'Add' in the window and click on Run.An inputbox will appear, which will ask

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0tag:blogger.com,1999:blog-365005675344367933.post-55533262425138928742009-02-13T23:30:00.000-08:002009-02-14T00:01:48.706-08:00For adding two numbers and printing the result in cell A1 of sheet1 of your workbook

Press Alt + F11 key for VBA editor, where you could write your code.Goto insert and select module.start typing the below code for addition of two numbers;Sub Add()a = 2b = 3c = a + bSheet1.Cells(1, 1) = cEnd SubNow press Alt + F8 to open macro window. Select 'Add' in the window and click on Run.Cell A1 of sheet1 will show 5 as a result.For further query or training on Excel and/or VBA, please

Viteshhttp://www.blogger.com/profile/11341237591070383663noreply@blogger.com0