Find B20 in column A using VLOOKUP function in Table 2

VLOOKUPA2 table 2 A B 2 0

If you don’t know how to generate buttons and associated programs, ask again. I won’t answer this part here.

Because your expression is ambiguous, here are three methods.

'1. Write a formula for the specified cell (slightly modify the specified cell or it can be a variable):

Sub writes formula () to cell

Range(＂B2＂).Select

ActiveCell.Formula = "=VLOOKUP(A2, Table 2!A:B,2,0)"

'ActiveCell.Formula = "=VLOOKUP(A:A, Table 2!A:B,2,0)"

'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1], Table 2!C[-1]:C[0],2,0)"

'ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-1], table 2!C[-1]:C[0],2,0)"

End Sub

'2. Explain the VLOOKUP function and find the value in the specified cell reference:

Sub Specify cell search reference()

Dim m

With Sheets（＂Table 2＂）

For m = 1 To .[A65536].End(3).Row

If .Cells(m, 1) = Sheets(1).Cells(2, 1) Then

Sheets(1).Cells(2, 2) = .Cells(m, 2)

End If

Next

End With

End Sub

'2. Explain the VLOOKUP function, which refers to the search value in the specified column. The above code is slightly optimized here and has universality:

Sub All Find References()

Dim i, j

With Sheets（＂Table 2＂）

For i = 1 To .[A65536].End(3).Row

For j = 1 To Sheets(1).[A65536].End(3).Row

If .Cells(i, 1) = Sheets(1).Cells(j, 1) Then

Sheets(1).Cells(j, 2) = .Cells(i, 2)

End If

Next

Next

End With

End Sub

How to call Excel functions in VBA instructions

Method/Step

Open Microsoft Office Excel 2007, click "Start" -> "Excel Options".

In the pop-up dialog box, select the "General" tab, and click "Show the 'Development Tools' tab in the ribbon" on the right, and click "OK".

Enter "radians" in cell A1, enter "angle" in cell B1, enter "3.14" in cell A2, then enter the formula "=DEGREES(A2)" in cell B2 and press Return After the car, the output result of this cell is 179.9087477.

Note: The DEGREES function is used to convert radians into angles.

Next, we use VBA to implement the same function. Click the "Development Tools" tab, click "Visual Basic" to start the Visual Basic Editor.

Right-click on "Microsoft Excel Object", click "Insert" -> "Module", create "Module 1", and enter the content in the module:

Sub Convert()

MsgBox Application.WorksheetFunction.Degrees(3.14)

End Sub

Click the "Run Subprocess/UserForm" button, select "Convert" in the pop-up dialog box, and then click the "Run" button.

The program pops up a dialog box, and the result is the same as the result output by the formula in Excel, except that the number of significant digits is different, which can be chosen as needed.

Summary: When doing VBA programming, you can use the WorkSheetFunction method to implement references to Excel functions. This can facilitate our reference to functions and avoid spending more time on custom functions, which effectively improves efficiency. Work efficiency.

The above is the detailed content of Find B20 in column A using VLOOKUP function in Table 2. For more information, please follow other related articles on the PHP Chinese website!