excel - Excel VBA,如何使用条件将单元格/单元格从 sheet1 复制到 sheet2
问题描述
我有两张纸,发票和客户。在发票表上,当我在单元格 A11 中写下客户的姓名时,客户的其余信息会出现在 A12 到 A15 中。但是,我想从发票表中更改此信息并将其复制到客户表上。我的代码适用于客户。但我需要为每个客户提供此代码。我需要你的想法,请
Private Sub Worksheet_Change(ByVal Target As Range)
Set sh1 = ThisWorkbook.Sheets("Customer")
Set sh4 = ThisWorkbook.Sheets("Invoice")
If Not Intersect(Target, sh4.Range("A12:A15")) Is Nothing Then
If sh4.Range("A11").Value = sh1.Range("B2").Value Then
sh1.Range("F2").Value = sh4.Range("A12").Value
sh1.Range("G2").Value = sh4.Range("A13").Value
End If
End If
End Sub
解决方案
您可以使用字典实现这种“匹配时更改”。
Option Explicit
Sub DictMatch()
Dim dict As Object, sh1 As Worksheet, sh4 As Worksheet 'you should declare your vars
Set dict = CreateObject("Scripting.Dictionary") 'create dictionary lateB
Set sh1 = ThisWorkbook.Sheets("Customer") 'adds no value, you could just use Sheet1
Set sh4 = ThisWorkbook.Sheets("Invoice") 'adds no value, you could just use Sheet4
Dim arr, arr2, LastRowSh1 As Long, LastRowSh4 As Long
LastRowSh1 = sh1.Cells(sh4.Rows.Count, "B").End(xlUp).Row 'count rows from last row
LastRowSh4 = sh4.Cells(sh4.Rows.Count, "A").End(xlUp).Row 'count rows from last row
arr = sh4.Range(sh4.Cells(1, 1), sh4.Cells(LastRowSh4, 1)).Value2 'load col A of invoices
arr2 = sh1.Range(sh1.Cells(1, 2), sh1.Cells(LastRowSh1, 7)).Value2 'load col B to G of cust
Dim j As Long
With dict 'used because I'm to lazy to retype dict everywhere :)
.CompareMode = 1 'textcompare
For j = 1 To UBound(arr) 'traverse invoices
If Not .Exists(arr(j, 1)) Then 'set key if I don't have it yet in dict
.Add Key:=arr(j, 1), Item:=j
End If
Next j
For j = 1 To UBound(arr2) 'traverse customers
If .Exists(arr2(j, 1)) Then 'matching happens here, compare data from target with dictionary
arr2(j, 5) = arr(dict(arr2(j, 1)) + 1, 1) 'write to target array if match
arr2(j, 6) = arr(dict(arr2(j, 1)) + 2, 1) 'we could make this more dynamic if you have more rows
End If
Next j
End With
With sh1
.Range(.Cells(1, 2), .Cells(LastRowSh1, 7)) = arr2 'dump updated array to customer sheet
End With
End Sub
根据您的输入,它应该做您想做的事而无需更改,但如果您遇到任何问题,请告诉我。如果不是,则接受为“答案”。
一些想法:
- 最佳做法是在代码顶部添加“选项显式”,这将迫使您声明所有变量,这将为您节省一天的麻烦:)。
- 尽管您可以将此代码粘贴到您的相交中,但我建议将其添加到“保存”事件(或按钮)中。否则,每次编辑字段时都会运行完整的代码,而没有任何附加值。
推荐阅读
- java - 在一个查询中加载两个不相关的实体(没有任何命令)[Spring Data JPA]
- javascript - 如何根据网格排序对图表进行排序,对于相同的数据
- python - 字典的行为方式
- amazon-s3 - 有人可以让我的 AWS S3 账单爆炸吗?
- c# - 使用 ASP.Net 核心和 Angular 6 构建损坏的 zip 文件
- css - 如何在转发器中应用动态类?
- c# - 使用openxml从excel读取时获取浮点数而不是精确字符串
- powerbi - 幂查询 M IN 算子
- html - 将 div 宽度调整为内容 CSS
- git - 在 git push 上将 BitBucket 同步到 VSTS Online