热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

从另一个表更新表值-UpdatetablevaluesfromanotherTable

Ihaveaworkbookwithatablethatisroughly20000rowsinsizeand52columns.Attimes,Ineedt

I have a workbook with a table that is roughly 20000 rows in size and 52 columns. At times, I need to update a percentage of select rows at once. I'm hoping to use a macro to update the select cells based on a value in the row, mapped out by a second smaller table with the updated values to be entered in to table 1. Almost like a VLOOKUP function, but one that doesn't erase the cell if the entry isn't found. For example, change the Phone Number according to the Host ID.

我有一个工作簿,其表大小约为20000行,52列。有时,我需要一次更新选定行的百分比。我希望使用宏来根据行中的值更新选择单元格,由第二个较小的表格映射出来,并将更新的值输入到表1中。几乎像VLOOKUP函数,但不是如果找不到条目,​​则擦除单元格。例如,根据主机ID更改电话号码。

I tried to do this with an Array in the code below for a specfic set of the values in Table 1, but my values didn't update. My VBA is a bit rusty, so if someone can review and assist with getting this to function, it would be appreciated. I would like to make it update any entry in the table based on the table headers eventually.

我尝试在下面的代码中使用数组执行此操作,以获得表1中的一组特定值,但我的值未更新。我的VBA有点生疏,所以如果有人可以查看并协助使其正常运行,我们将不胜感激。我想最后根据表头更新表中的任何条目。

Sub NewNameandCostCenter()
Dim myList, myRange
Dim sht As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim LastRow2 As Long
Set sht = Worksheets("NewNameMacro")
Set sht2 = Worksheets("ALL")
Set StartCell = Range("A2")

'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
'set myList array
Set myList = sht.Range(StartCell, sht.Cells(LastRow, LastColumn))
LastRow2 = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'set myRange array
Set myRange = Sheets("ALL").Range("J2:M" & LastRow2)
'Update values of cells adjacent
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 2).Value, LookAt:=xlWhole
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 3).Value, LookAt:=xlWhole
Next cel
End Sub

Thanks, JD

谢谢,JD

1 个解决方案

#1


1  

If I understand your question correctly, you're effectively running an UPDATE query against your data, based on the values in your mapping table.

如果我正确理解了您的问题,那么您将根据映射表中的值有效地对数据运行UPDATE查询。

I've assumed the following:

我假设如下:

  • The "key" column is the first column in your data table and in your mapping table.

    “key”列是数据表和映射表中的第一列。

  • The columns in your mapping table are in the same order and relative position as the columns in the data table (although this could easily be adjusted.

    映射表中的列与数据表中的列具有相同的顺序和相对位置(尽管可以轻松调整。

  • The order of the keys in the mapping table and the data table is unsorted. If you can ensure that the keys are sorted (ideally in both sheets), then you could achieve substantially better performance with some slight modifications.

    映射表和数据表中键的顺序未排序。如果您可以确保按键排序(理想情况下在两张纸中),那么只需稍作修改即可获得更好的性能。

I've hard-coded the ranges in my example, but you can reinstate the last row and last column approach if you need to.

我在我的示例中对范围进行了硬编码,但如果需要,可以恢复最后一行和最后一列方法。

I've done all of my comparisons between arrays instead of ranges, and I've done away with the Find approach. You'll find that this works, and works much more efficiently.

我已经完成了数组之间的所有比较而不是范围,并且我已经完成了Find方法。您会发现这种方法有效,并且效率更高。

Option Explicit

Sub NewNameandCostCenter()

  Dim start As Double
  start = Timer

  Dim countOfChangedRows As Long

  'set rngMap array
  Dim rngMap As Range
  Set rngMap = Worksheets("Map").Range("A1:D51")

  'set rngData array
  Dim rngData As Range
  Set rngData = Worksheets("Data").Range("J2:M20001")

  Dim aMap As Variant
  aMap = rngMap.Value

  Dim aData As Variant
  aData = rngData.Value

  Dim mapRow As Long
  Dim datarow As Long
  Dim mapcol As Long

  For mapRow = LBound(aMap, 1) To UBound(aMap, 1)
    For datarow = LBound(aData) To UBound(aData)
      'Check the key matches in both tables
      If aData(datarow, 1) = aMap(mapRow, 1) Then
        countOfChangedRows = countOfChangedRows + 1
        'Assumes the columns in map and data match
        For mapcol = LBound(aMap, 2) + 1 To UBound(aMap, 2)
          aData(datarow, mapcol) = aMap(mapRow, mapcol)
        Next mapcol
      End If
    Next datarow
  Next mapRow

  rngData.Value = aData

  Debug.Print countOfChangedRows & " of "; UBound(aData, 1) & " rows updated in " & Timer - start & " seconds"

End Sub

The performance is reasonable for 50 updated rows:

50个更新行的性能合理:

50 of 20000 rows updated in 0.23828125 seconds

20000行中的50行在0.23828125秒内更新

But if you need to start updating thousands of rows, then you would benefit greatly from ensuring the data is sorted and tweaking the code accordingly.

但是,如果您需要开始更新数千行,那么您将从确保数据排序和相应调整代码中受益匪浅。


推荐阅读
  • 本文详细介绍了如何使用 Yii2 的 GridView 组件在列表页面实现数据的直接编辑功能。通过具体的代码示例和步骤,帮助开发者快速掌握这一实用技巧。 ... [详细]
  • 深入理解Redis的数据结构与对象系统
    本文详细探讨了Redis中的数据结构和对象系统的实现,包括字符串、列表、集合、哈希表和有序集合等五种核心对象类型,以及它们所使用的底层数据结构。通过分析源码和相关文献,帮助读者更好地理解Redis的设计原理。 ... [详细]
  • 本文介绍了如何使用JQuery实现省市二级联动和表单验证。首先,通过change事件监听用户选择的省份,并动态加载对应的城市列表。其次,详细讲解了使用Validation插件进行表单验证的方法,包括内置规则、自定义规则及实时验证功能。 ... [详细]
  • 本文探讨了如何在 React 和 TypeScript 中使用高阶组件(HOC)来消耗上下文,并详细解释了相关类型定义和实现细节。 ... [详细]
  • 从 .NET 转 Java 的自学之路:IO 流基础篇
    本文详细介绍了 Java 中的 IO 流,包括字节流和字符流的基本概念及其操作方式。探讨了如何处理不同类型的文件数据,并结合编码机制确保字符数据的正确读写。同时,文中还涵盖了装饰设计模式的应用,以及多种常见的 IO 操作实例。 ... [详细]
  • PHP数组平均值计算方法详解
    本文详细介绍了如何在PHP中计算数组的平均值,涵盖基本概念、具体实现步骤及示例代码。通过本篇文章,您将掌握使用PHP函数array_sum()和count()来求解数组元素的平均值。 ... [详细]
  • 作为一名专业的Web前端工程师,掌握HTML和CSS的命名规范是至关重要的。良好的命名习惯不仅有助于提高代码的可读性和维护性,还能促进团队协作。本文将详细介绍Web前端开发中常用的HTML和CSS命名规范,并提供实用的建议。 ... [详细]
  • 采用IKE方式建立IPsec安全隧道
    一、【组网和实验环境】按如上的接口ip先作配置,再作ipsec的相关配置,配置文本见文章最后本文实验采用的交换机是H3C模拟器,下载地址如 ... [详细]
  • 本文介绍了如何使用JavaScript的Fetch API与Express服务器进行交互,涵盖了GET、POST、PUT和DELETE请求的实现,并展示了如何处理JSON响应。 ... [详细]
  • 本文详细介绍了如何在Kendo UI for jQuery的数据管理组件中,将行标题字段呈现为锚点(即可点击链接),帮助开发人员更高效地实现这一功能。通过具体的代码示例和解释,即使是新手也能轻松掌握。 ... [详细]
  • 导航栏样式练习:项目实例解析
    本文详细介绍了如何创建一个具有动态效果的导航栏,包括HTML、CSS和JavaScript代码的实现,并附有详细的说明和效果图。 ... [详细]
  • 本文探讨了在通过 API 端点调用时,使用猫鼬(Mongoose)的 findOne 方法总是返回 null 的问题,并提供了详细的解决方案和建议。 ... [详细]
  • 在过去两周中,我们利用 ReportViewer 开发了与生产良率相关的报表,其中每个制程的直通率是所有测试项良率的乘积。由于 ReportViewer 没有内置的累乘函数,因此需要借助自定义代码来实现这一功能。本文将详细介绍实现步骤和相关代码。 ... [详细]
  • 探讨如何正确使用 Fetch API 进行参数传递,分析不同写法的差异及解决方案。 ... [详细]
  • 本文探讨了如何通过预处理器开关选择不同的类实现,并解决在特定情况下遇到的链接器错误。 ... [详细]
author-avatar
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有