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

Excel points

Excel is a powerful tool for organizing, analyzing, and visualizing data. It's used in a variety of industries, from finance and business to education and research. If you're new to Excel, here are some basics to get you started: Understanding the Excel interface: The Excel window is divided into three main areas: the ribbon, the worksheet, and the formula bar. The ribbon is the top menu that contains tabs for different functions and features, such as Insert, Home, and Data. The worksheet is where you enter and manipulate data, and it's made up of rows and columns. The formula bar is where you enter formulas and functions to perform calculations on your data. Entering and formatting data: To enter data into a cell, simply click on the cell and type in your data. You can format your data by using the formatting tools in the Home tab of the ribbon. This includes options for changing the font, size, color, and alignment of your data. Using formulas and functions: Formulas and ...
Post a Comment
Read more

What is Brand Marketing

Brand marketing is the process of building and maintaining a strong and recognizable brand. A brand is more than just a logo or a name, it is the perception that a customer has of a product or service. A strong brand can help to differentiate a company's offerings from its competitors and create a sense of trust and loyalty among customers. There are several key elements of brand marketing, including: Brand Identity: This includes the visual elements of the brand, such as the logo, color scheme, and typography, as well as the brand's name and tagline. Brand Strategy: This includes the overall positioning of the brand, the target audience, and the key messaging. Brand Communications: This includes the various tactics and channels used to communicate the brand, such as advertising, public relations, and social media. Brand Experience: This includes the overall customer experience, from the product or service itself to the customer service and support. Brand Management: This inclu...
Post a Comment
Read more

Windows shortcut keys

 Here are some common Windows shortcut keys: Windows key + E: Open File Explorer Windows key + R: Open the Run dialog box Windows key + L: Lock your computer or switch accounts Windows key + T: Cycle through apps on the taskbar Windows key + D: Show the desktop Windows key + P: Project to a different screen Windows key + Tab: Open the Task View Windows key + Ctrl + D: Create a new virtual desktop Windows key + Ctrl + Right arrow: Switch to the next virtual desktop Windows key + Ctrl + Left arrow: Switch to the previous virtual desktop Windows key + Ctrl + F4: Close the current virtual desktop Windows key + Alt + Tab: Switch between open apps Windows key + Shift + S: Take a screenshot (Windows 10 only) Ctrl + C: Copy Ctrl + X: Cut Ctrl + V: Paste Ctrl + A: Select all Ctrl + Z: Undo Ctrl + Y: Redo Ctrl + N: Open a new window or document Ctrl + W: Close the current window or document Alt + Tab: Switch between open windows or programs Windows key + D: Show the desktop Windows key + E: ...
Post a Comment
Read more