将通过输入框选择的列设置为变量

尝试为另一个工作簿(OpenBook_CY)中的值开发代码并将其存储在Comp_Book中。代码运行良好，但是我需要将一些固定的列更改为变量

Dim Conp_Book as Workbook, OpenBook_CY as Workbook   
Dim M_Sheet As Worksheet, CY_Sheet As Worksheet
Dim M_LR As Long, CY_LR As Long, r As Long, SelectE_Column As Long, E_Column As range
Dim CY_Rng As range,

Set M_Sheet = Comp_Book.Worksheets("Sheet1")
Set CY_Sheet = OpenBook_CY.Worksheets("Trial-New")

M_LR = M_Sheet.range("B" & Rows.Count).End(xlUp).Row
CY_LR = CY_Sheet.range("J" & Rows.Count).End(xlUp).Row
SelectE_Column = Application.InputBox(Prompt:="Select your entity ", Title:="Select Entity", Type:=8)
Set E_Column = SelectE_Column.EntireColumn
Set CY_Rng = CY_Sheet.range("B2:J" & CY_LR)

For r = 6 To M_LR
On Error Resume Next
M_Sheet.range("D" & r).Value = Application.WorksheetFunction.VLookup(M_Sheet.range("B" & r).Value, 
CY_Rng, 9, 0)
Next r

我想用用户通过输入框(我暂时将其命名为E_Column )选择的列替换"J“(如CY_Rng中提到的)。现在J是一个固定的列，但是我希望它是用户选择的列(在CY_Rng中)。

Set CY_Rng = CY_Sheet.range("B2:J" & CY_LR)

其次，在vlookup公式中，"9“表示如上所述的第9列，即"J”。同样，我希望将数字"9“替换为上面用户选择的列号。

M_Sheet.range("D" & r).Value = Application.WorksheetFunction.VLookup(M_Sheet.range("B" & r).Value, 
CY_Rng, 9, 0)

转载请注明出处:http://www.bqysc.com/article/20230526/1914387.html

相关阅读