热门标签 | 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.

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


推荐阅读
  • 深入理解Redis的数据结构与对象系统
    本文详细探讨了Redis中的数据结构和对象系统的实现,包括字符串、列表、集合、哈希表和有序集合等五种核心对象类型,以及它们所使用的底层数据结构。通过分析源码和相关文献,帮助读者更好地理解Redis的设计原理。 ... [详细]
  • 本文将介绍如何编写一些有趣的VBScript脚本,这些脚本可以在朋友之间进行无害的恶作剧。通过简单的代码示例,帮助您了解VBScript的基本语法和功能。 ... [详细]
  • 优化ListView性能
    本文深入探讨了如何通过多种技术手段优化ListView的性能,包括视图复用、ViewHolder模式、分批加载数据、图片优化及内存管理等。这些方法能够显著提升应用的响应速度和用户体验。 ... [详细]
  • 本文探讨了如何在给定整数N的情况下,找到两个不同的整数a和b,使得它们的和最大,并且满足特定的数学条件。 ... [详细]
  • Splay Tree 区间操作优化
    本文详细介绍了使用Splay Tree进行区间操作的实现方法,包括插入、删除、修改、翻转和求和等操作。通过这些操作,可以高效地处理动态序列问题,并且代码实现具有一定的挑战性,有助于编程能力的提升。 ... [详细]
  • 本教程涵盖OpenGL基础操作及直线光栅化技术,包括点的绘制、简单图形绘制、直线绘制以及DDA和中点画线算法。通过逐步实践,帮助读者掌握OpenGL的基本使用方法。 ... [详细]
  • 本文介绍如何使用 Angular 6 的 HttpClient 模块来获取 HTTP 响应头,包括代码示例和常见问题的解决方案。 ... [详细]
  • 本文探讨了如何通过预处理器开关选择不同的类实现,并解决在特定情况下遇到的链接器错误。 ... [详细]
  • 本文探讨了为何相同的HTTP请求在两台不同操作系统(Windows与Ubuntu)的机器上会分别返回200 OK和429 Too Many Requests的状态码。我们将分析代码、环境差异及可能的影响因素。 ... [详细]
  • 深入解析ESFramework中的AgileTcp组件
    本文详细介绍了ESFramework框架中AgileTcp组件的设计与实现。AgileTcp是ESFramework提供的ITcp接口的高效实现,旨在优化TCP通信的性能和结构清晰度。 ... [详细]
  • 本文详细介绍了 GWT 中 PopupPanel 类的 onKeyDownPreview 方法,提供了多个代码示例及应用场景,帮助开发者更好地理解和使用该方法。 ... [详细]
  • Explore how Matterverse is redefining the metaverse experience, creating immersive and meaningful virtual environments that foster genuine connections and economic opportunities. ... [详细]
  • 技术分享:从动态网站提取站点密钥的解决方案
    本文探讨了如何从动态网站中提取站点密钥,特别是针对验证码(reCAPTCHA)的处理方法。通过结合Selenium和requests库,提供了详细的代码示例和优化建议。 ... [详细]
  • 在编译BSP包过程中,遇到了一个与 'gets' 函数相关的编译错误。该问题通常发生在较新的编译环境中,由于 'gets' 函数已被弃用并视为安全漏洞。本文将详细介绍如何通过修改源代码和配置文件来解决这一问题。 ... [详细]
  • 本文探讨了在 SQL Server 中使用 JDBC 插入数据时遇到的问题。通过详细分析代码和数据库配置,提供了解决方案并解释了潜在的原因。 ... [详细]
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社区 版权所有