Skip to main content

VBA excel automatic date when value add

 In Microsoft Excel, you can use Visual Basic for Applications (VBA) to automatically enter a date in a cell when a value is added. Here's an example of how you can do this: Press Alt + F11 to open the VBA editor. Right-click the sheet tab where you want to add the date, then select "View Code". In the code editor, paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Target.Offset(0, 1).Value = Date End If End Sub Close the code editor by clicking the "X" in the top-right corner. This code will automatically enter the current date in the cell next to the active cell whenever a value is added in the cells from A1 to A10. If you want to change the range of cells, you can modify the range in the line "If Not Intersect(Target, Range("A1:A10")) Is Nothing Then". Note: This code works in all recent versions of Micr
Post a Comment

Excel Vlookup and Hlookup


VLOOKUP and HLOOKUP are functions in Excel that allow you to search for a specific value in a table and return a corresponding value from a different column in the same row.

VLOOKUP (vertical lookup) searches for a value in the leftmost column of a table and returns a value from a specified column in the same row. The syntax for the VLOOKUP function is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: the value you want to search for in the leftmost column of the table
  • table_array: the table or range of cells containing the data you want to search
  • col_index_num: the column number in the table from which you want to return a value (e.g., 2 for the second column)
  • range_lookup: optional; if set to TRUE, VLOOKUP will perform an approximate match search; if set to FALSE, it will perform an exact match search

For example, suppose you have a table with two columns: Name and Age. To use VLOOKUP to search for the age of a person with a specific name, you could use the following formula:

=VLOOKUP("John", A2:B6, 2, FALSE)

This formula would search for the value "John" in the leftmost column (column A) of the table A2:B6 and return the corresponding value from the second column (column B) in the same row.

HLOOKUP (horizontal lookup) is similar to VLOOKUP, but it searches for a value in the top row of a table and returns a value from a specified row in the same column. The syntax for the HLOOKUP function is as follows:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

  • lookup_value: the value you want to search for in the top row of the table
  • table_array: the table or range of cells containing the data you want to search
  • row_index_num: the row number in the table from which you want to return a value (e.g., 2 for the second row)
  • range_lookup: optional; if set to TRUE, HLOOKUP will perform an approximate match search; if set to FALSE, it will perform an exact match search

For example, suppose you have a table with two rows: Age and Name. To use HLOOKUP to search for the name of a person with a specific age, you could use the following formula:

=HLOOKUP(30, A1:B2, 2, FALSE)

This formula would search for the value 30 in the top row (row 1) of the table A1:B2 and return the corresponding value from the second row (row 2) in the same column.

VLOOKUP and HLOOKUP are useful functions for looking up and retrieving data from tables and ranges of cells in Excel. With practice, you'll be able to use these functions effectively to search for and return specific values in your data. 

Labels:

Comments

Post a Comment

Popular posts from this blog

Multiple Find and Replace word document using VBA

On the File tab, go to Options > Customize Ribbon. Under Customize the Ribbon and under Main Tabs, select the Developer check box. After you show the tab, the Developer tab stays visible, unless you clear the check box or have to reinstall a Microsoft Office program. The Developer tab is the place to go when you want to do or use the following: 1. Write macros. Sub ReplaceFromTableList() Dim oChanges As Document, oDoc As Document Dim oTable As Table Dim oRng As Range Dim rFindText As Range, rReplacement As Range Dim I As Long Dim sFname As String 'Change the path in the line below to reflect the name and path of the table document sFname = "C:\Users\admin\Documents\FindReplaceTable.docx" Set oDoc = ActiveDocument Set oChanges = Documents.Open(FileName:=sFname, Visible:=False) Set oTable = oChanges.Tables(1) For I = 1 To oTable.Rows.Count Set oRng = oDoc.Range Set rFindText = oTable.Cell(I, 1).Range rFindText.End = rFindText.End - 1 Set r
1 comment
Read more

Sales promotion marketing

Sales promotion marketing refers to a variety of short-term incentives that are designed to encourage customers to make a purchase or take some other desired action. These incentives can include discounts, coupons, free samples, contests, and other similar promotions. Sales promotion marketing is often used as a complement to other forms of marketing, such as advertising and public relations, to increase sales and drive revenue. One of the key benefits of sales promotion marketing is that it can be used to drive short-term results. For example, a business may use a discount or coupon promotion to increase sales during a slow period, or to clear out inventory. Sales promotions can also be used to attract new customers, or to encourage repeat business from existing customers. There are several types of sales promotion strategies that can be used, including: Price promotions: This includes discounts, coupons, and other incentives that are based on price. Premium promotions: This includes
Post a Comment
Read more

VBA excel automatic date when value add

 In Microsoft Excel, you can use Visual Basic for Applications (VBA) to automatically enter a date in a cell when a value is added. Here's an example of how you can do this: Press Alt + F11 to open the VBA editor. Right-click the sheet tab where you want to add the date, then select "View Code". In the code editor, paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Target.Offset(0, 1).Value = Date End If End Sub Close the code editor by clicking the "X" in the top-right corner. This code will automatically enter the current date in the cell next to the active cell whenever a value is added in the cells from A1 to A10. If you want to change the range of cells, you can modify the range in the line "If Not Intersect(Target, Range("A1:A10")) Is Nothing Then". Note: This code works in all recent versions of Micr
Post a Comment
Read more