excel - 用户表单 - VLOOKUP 和表单计算
问题描述
我有一个包含多个字段的表单。我在表单上有一个字段,我想在其中进行计算。这需要一个VLOOKUP
辅助列函数。
在常规单元格中,我可以键入以下内容:
=VLOOKUP(B3&C3&D3,Ins_Price_Tbl,5,0)
Ins_Price_Tbl 是一个数据表。
并得到我正在寻找的答案。
我的表格有文本框
txtAdjustmentCountYearA - An integer
txtInsurance - A String
txtAdjustmentYearA - A year like 2018
txtAdjustmentCostYearA - The result field
我需要txtAdjustmentCostYearA
执行VLOOKUP
where 在这种情况下 C3 将等于“调整”或我输入的任何文本并将其乘以txtAdjustmentCountYearA.Value
所以我可能会有类似的东西:
txtAdjustmentCountYearA.Value
等于 2
txtAdjustmentYearA.Value
等于 2018
txtAdjustmentCostYearA
等于:
=VLOOKUP(Me.txtInsurance.Value&"Whatever I type in here"&Me.txtAdjustmentYearA,Ins_Price_Tbl,5,0) * txtAdjustmentCountYearA.Value
解决方案
尽可能靠近您的 OP
Option Explicit ' declaration head of code module
Private Sub CommandButton1_Click()
Dim ws As Worksheet ' provide for range reference
Set ws = ThisWorkbook.Worksheets("MySheet") ' <~~ change to your sheet name
Dim searched As String, found As Variant, factor As Double
searched = Me.txtInsurance.Text & ws.Range("C3").value & Me.txtAdjustmentYearA.Text
' use the Application function VLookup instead of worksheetfunction.VLookup
' allowing to ask a IsError condition (variable found has to be of type Variant!)
found = Application.VLookup(searched, ws.Range("Ins_Price_Tbl"), 5, 0)
' Textbox values are always strings and have to be converted (e.g. to Double)
If IsNumeric(txtAdjustmentCountYearA.Text) Then factor = CDbl(txtAdjustmentCountYearA.Text)
' Check possible errors before assigning result to textbox (again as String!)
If Not IsNumeric(found) Or IsError(found) Then
Me.txtAdjustmentCostYearA.Text = "Nothing found"
Else
Me.txtAdjustmentCostYearA.Text = found * factor
End If
End Sub
推荐阅读
- stackdriver - Which is the api call for retrieving the events of a policy in Google Stackdriver?
- java - Eclipse plugin - issue passing empty command arguments from option preference in plugin.xml
- java - Update of an entity with ttl not working as expected with Datastax Java driver
- node.js - OVH 对象存储,当我尝试上传大文件(超过 100 Ko)时没有任何反应
- python - 回溯数独仅适用于 Python 3
- javascript - Request failed for https://docs.google.com returned code 400. Truncated server response:
- android - Android Studio 在我的发布 apk 名称中附加了“脏”字是什么意思?
- python - ValueError: setting an array element with a sequence directly causes snother error TypeError: only size-1 arrays can be converted to Python scalars
- swift - Firebase 需要很长时间来编写文档并冻结应用程序
- r - 如果我的月、日和年在不同的列中,我可以使用 R 包 lubridate 来解析日期吗?