大佬教程收集整理的这篇文章主要介绍了使用 INDEX MATCH 函数时,我无法在 Excel 的 VB 脚本中从 Sheet2 导入到 Sheet1,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我在 Sheet2 的 A 列和 B 列中有数据,我从 A 列中获取了一些数据并将它们粘贴到 Sheet1 中的 A 列中,现在我想从 Sheet2 中的 B 列导入数据以获取 A 列的匹配数据床单。我使用以下逻辑来执行此操作但出现错误。
For k=2 To 400
Cells(k,2).Value = WorksheetFunction.Index(Sheet2!Range("B2:B1255"),WorksheetFunction.Match(Cells(K,1).Value,Sheet2!Range("A2:A1255")))
Next k
Sheet1
)。代码
Option Explicit
Sub testFormula()
With Range("B2").Resize(400 - Range("B2").Row + 1)
.Formula = "=IFERROR(INDEX(Sheet2!B$2:B$1255," _
& "MATCH(A2,Sheet2!A$2:A$1255,0)),"""")"
.Value = .Value
End With
End Sub
Sub testVBA()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
' Define source Lookup Range.
Dim srg As Range
With wb.Worksheets("Sheet2")
Dim sLast As Long: sLast = .Cells(.Rows.Count,"A").End(xlUp).Row
Set srg = .Range("A2:A" & sLast) ' i.e. '.Range("A2:A1255")'
' Or just:
'Set srg = .Range("A2:A" & .Cells(.Rows.Count,"A").End(xlUp).Row)
End With
' Define DesTination Lookup Range.
Dim DRG As Range
With wb.Worksheets("Sheet1")
Dim dLast As Long: dLast = .Cells(.Rows.Count,"A").End(xlUp).Row
Set DRG = .Range("A2:A" & dLast) ' i.e. '.Range("A2:A400")'
' Or just:
'Set DRG = .Range("A2:A" & .Cells(.Rows.Count,"A").End(xlUp).Row)
End With
' Loop through cells of DesTination Lookup Range...
Dim dCell As Range
Dim cIndex As Variant ' Can be an error value,hence 'Variant'.
' Loop through cells of DesTination Lookup Range.
For Each dCell In DRG.Cells
' Attempt to find a match (the first occurrence) of the value
' in the current cell of DesTination Lookup Range,' in cells of source Lookup Range.
cIndex = Application.Match(dCell.Value,srg,0)
' If a match is found...
If IsNumeric(cIndeX) Then
' Write the value from the cell associated (in the same row)
' to the matched cell (i.e. source column 'B'),to the cell
' associated (in the same row) to the current cell
' (DesTination column 'B').
dCell.offset(,1).Value = srg.Cells(cIndeX).offset(,1).Value
' IF no match found (error value)...
Else
dCell.offset(,1).Value = ""
End If
Next dCell
End Sub
以上是大佬教程为你收集整理的使用 INDEX MATCH 函数时,我无法在 Excel 的 VB 脚本中从 Sheet2 导入到 Sheet1全部内容,希望文章能够帮你解决使用 INDEX MATCH 函数时,我无法在 Excel 的 VB 脚本中从 Sheet2 导入到 Sheet1所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。