- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我在工作簿的一张表中有数据。我想将它分布在另一本书的多张纸上。怎么做,上图。
目前我正在使用以下代码,但它也无法按照设想的方式工作。这对我来说只是一个起点。
Dim row1, row2
Dim i As Integer
Dim cell1 As String
' this is just an example where I am trying to loop through 3 cells but it does not work
' the cells in my example are in G14,G15 and G16
Dim wbk1 As Workbook, wbk2 As Workbook
strFirstFile = "c:\Book1.xls"
strSecondFile = "c:\Book2.xls"
Set wbk1 = Workbooks.Open(strFirstFile)
Set wbk2 = Workbooks.Open(strSecondFile)
For i = 14 To 16
With wbk1.Sheets("Data")
Cells(i, 7).Copy
End With
With wbk2.Sheets("MyData")
Cells(i, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Next i
Book1.xls Book2.xls
sheet1->B3 -> Company->A3
sheet1->C3 -> Address->C3
sheet1->E3 -> Popularity->D3
最佳答案
该解决方案已根据修订后的问题进行了实质性重写。
此解决方案假定宏 SplitSheet 在其自己的工作簿中。它需要两个文件名,在此版本中硬编码为 Source.xls 和 Dest.xls。此版本假定所有三个工作簿都位于或将位于同一文件夹中。在运行宏之前,源工作簿必须存在。目标工作簿不得存在。
问题有四列,但真正的问题有六十列。该解决方案旨在调整为 Sheet1 的尺寸(也是硬编码)。哪些列要移动,到哪里以及如何命名由三个数组控制,这些数组可以从它们当前的三个条目放大。该代码使用这些数组的实际大小。
我希望每一个困难的陈述都得到充分的解释。祝你好运。
Sub SplitSheet()
Dim ColDestCrnt As Integer
Dim ColMapName() As Variant
Dim ColMapDest() As Variant
Dim ColMapSource() As Variant
Dim ColSourceCrnt As Integer
Dim ColSourceMax As Integer
Dim ColWidth() As Single
Dim DataCol() As Variant
Dim DataWSheet() As Variant
Dim FileNameSource As String
Dim FileNameDest As String
Dim InxColMap As Integer
Dim InxWSheet As Integer
Dim Path As String
Dim Rng As Range
Dim RowSourceCrnt As Integer
Dim RowSourceMax As Integer
Dim WBookDest As Workbook
Dim WBookSource As Workbook
' These arrays define the mappings. Column B is to be copied to column A,
' column C to C and column E to D.
ColMapSource = Array("B", "C", "E")
ColMapDest = Array("A", "C", "D")
' The names to be given to the worksheets in the destination worksheet
ColMapName = Array("Company", "Address", "Popularity")
' Additional entries may be added to these array providing they all have
' the same number of entries.
If Workbooks.Count > 1 Then
' It can get complicated if more than one workbook is open
' at the start. I suggest aborting in this situation unless
' there is an important reason for allowing it.
' If this is a one-off transformation, use of Debug.Assert False,
' which will stop execution until you press F5, is adequate if
' unprofessional. If it is to be used repeatedly, you need a
' proper error message for the user.
Debug.Assert False ' execution error
Exit Sub
End If
' This assumes all three workbooks will be in the same folder.
' Change as necessary.
Path = ActiveWorkbook.Path
' You must decide how to assign values to these variables
FileNameSource = "Source.xls"
FileNameDest = "Dest.xls"
If Dir$(Path & "\" & FileNameSource) = "" Then
' Source workbook does not exist
Debug.Assert False ' execution error
Exit Sub
End If
If Dir$(Path & "\" & FileNameDest) <> "" Then
' Dest workbook exists
Debug.Assert False ' execution error
Exit Sub
End If
Set WBookSource = Workbooks.Open(Path & "\" & FileNameSource)
With WBookSource
' Replace "Sheet1" with the name of the source worksheet
With Sheets("Sheet1")
' This determines the highest numbered row and the highest
' number column in the source worksheet
Set Rng = .Range("A1").SpecialCells(xlCellTypeLastCell)
RowSourceMax = Rng.Row
ColSourceMax = Rng.Column
' This copies the values of the entire source worksheet to array SourceWSheet
DataWSheet = .Range(.Cells(1, 1), .Cells(RowSourceMax, ColSourceMax)).Value
' This saves the widths of the source columns
ReDim ColWidth(1 To ColSourceMax)
For ColSourceCrnt = 1 To ColSourceMax
ColWidth(ColSourceCrnt) = .Columns(ColSourceCrnt).ColumnWidth
Next
End With
' We have no further need of the source workbook. Close without saving
.Close False
End With
Set WBookSource = Nothing
' DataWSheet has dimensions (1 to RowSourceMax, 1 to ColSourceMax)
' Normal practice is to have rows as the second dimension. This is not true
' of array loaded from or to a worksheet.
Set WBookDest = Workbooks.Add
With WBookDest
' The factory setting for Excel is to have three sheets
' in a new workbook but that setting may be changed.
' This Do Loop ensures there are enough sheets and that
' any that are added are in sheet name sequence.
' It does not delete any excess Sheets.
Do While UBound(ColMapName) > .Sheets.Count
.Sheets.Add After:=Sheets(.Sheets.Count)
Loop
' Name the sheets with the values in ColMapName() and set the
' width of the destination column to that of the source column.
' The use of lbound (=lower bound) and ubound (=upper bound)
' means this for-loop is controlled by the size of ColmapName.
' Note one index is used for all three ColMap arrays because they match
For InxColMap = LBound(ColMapName) To UBound(ColMapName)
' ColMapName has been loaded with Array. Its lower bound is almost
' certainly zero but the documentation is not 100% clear that it will
' always be zero. The lower bound for sheets is one.
' "InxColMap + 1 - LBound(ColMapName)" performs the necessary adjustment
' regardless of the ColMapName's lower bound
With .Sheets(InxColMap + 1 - LBound(ColMapName))
.Name = ColMapName(InxColMap)
' Convert the column letters in ColMapSource and ColMapDest
' to numbers. Bit of a cheat but it works.
ColSourceCrnt = Range(ColMapSource(InxColMap) & "1").Column
ColDestCrnt = Range(ColMapDest(InxColMap) & "1").Column
.Columns(ColDestCrnt).ColumnWidth = ColWidth(ColSourceCrnt)
End With
Next
' The destination worksheets are now prepared.
' Size the array that will be used to copy data to the destination sheets
ReDim DataCol(1 To RowSourceMax, 1 To 1)
For InxColMap = LBound(ColMapSource) To UBound(ColMapSource)
ColSourceCrnt = Range(ColMapSource(InxColMap) & "1").Column
For RowSourceCrnt = 1 To RowSourceMax
DataCol(RowSourceCrnt, 1) = DataWSheet(RowSourceCrnt, ColSourceCrnt)
Next
With Sheets(ColMapName(InxColMap))
' Copy data to appropriate column in appropriate destination sheet
.Range(ColMapDest(InxColMap) & "1:" & _
ColMapDest(InxColMap) & RowSourceMax).Value = DataCol
End With
Next
.SaveAs (Path & "\" & FileNameDest)
.Close False
End With
Set WBookDest = Nothing
End Sub
关于vba - 将单元格从一张表复制到多张表中 Excel - VBA,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8480504/
我试图要求 test/unit 的新版本(即不是与 ruby 捆绑的版本) .根据 instructions我用 gem i test-unit 安装它.但是现在当我需要 test/unit我似乎
简短版本是: 我有一个 systemd 单元,我想在调用时检查脚本的返回代码: systemctl status service.service 长版本:我有一个 lsb init 脚本正是这样做的,
我正在使用反射动态创建一个类的实例,这工作正常,除非尝试通过单元测试执行此操作 - 我使用的是 MS 测试框架。 我收到熟悉的错误:“无法加载文件或程序集‘Assy’或其依赖项之一。系统找不到指定的文
我想知道测试网站“界面功能”的最佳实践是什么。 我对哪些方法可用于测试界面和动态生成的内容感兴趣。特别是,我很难弄清楚是否可以为需要用户交互的操作创建自动化测试,或者这是否只是浪费时间,我应该让一些
我有一个简单的 Python 服务,其中有一个无限执行某些操作的循环。在各种信号上,sys.exit(0) 被调用,这导致 SystemExit 被引发,然后如果可以的话应该进行一些清理。 在测试中,
我正在使用 OpenCV 2.4.2 这是 OpenCV 文档中的引文 C++: void HoughLinesP(InputArray image, OutputArray lines, doubl
忙于 C# 中的自动化测试用例,我们需要在每次测试后恢复数据库快照。问题是,当运行多个测试时它会失败,因为“其他用户正在使用数据库时无法更改数据库状态。” 我们使用 SqlConnection.Cle
我阅读了 C# 规范并用谷歌搜索了它,但一无所获。 我 99% 肯定 C# 中没有像单元命名空间指令这样的功能,但问题是:为什么?是否有惯用或技术原因? 这很方便,尤其是当我们的大部分文件都由单个命名
我目前正在尝试向我的应用程序(一个非常老的项目......评论说 iOS 2.0)添加单元测试(精确的应用程序测试)并且总是偶然发现 undefined symbols for architectur
我正在使用Delphi 7,并且有一个新单元要在我的项目中使用。我已经编译了新的。当我尝试通过将其添加到uses子句在项目中使用此单元时,出现错误,提示未找到.dcu文件。我还尝试将文件的完整路径放在
场景:我需要编写一个复杂的nHibernate查询,该查询将返回预计的DTO,但是我想使用TDD方法。该方法如下所示: public PrintDTO GetUsersForPrinting(int
您可以通过运行以下命令在事件 html 设置中显示 Jupyter 笔记本: $ jupyter nbconvert untitled.ipynb --to slides --post serve 有
如何在一个网站上拥有多个 AdSense 单元? Google 提供的唯一代码是按单位计算的。 (adsbygoogle = window.adsbygoogle || []).push({})
我刚刚开始为大量代码编写测试。有很多类依赖于文件系统,即读取 CSV 文件、读/写配置文件等。 当前测试文件存储在项目(这是一个 Maven2 项目)的 test 目录中,但由于多种原因该目录并不总是
我对 TDD 还很陌生,在单元测试方面也不是很老练,所以才有这个问题。我有这个用 PHP 编写的遗留函数 function foo(){ x = bar(); y = baz();
我创建了一个程序,在 Swing 窗口的一侧显示结果过滤选项,但默认情况下它们水平相邻显示,这浪费了我在 BorderLayout 的西侧分配的空间。我可以在构造函数或添加语句中传递任何内容来将它们堆
标题不好的借口:如果有人能更好地描述它,请做。 我有一个 WeakList类,它“基本上”是一个 List> (虽然不是字面意义上的派生自列表,但它应该对用户完全透明)。 现在的基本思想是“如果引用的
我正在尝试在 UITableView 上添加两个原型(prototype)单元。但是,我不知道如何验证是否能够为每个原型(prototype)“返回”正确的单元格。你们能帮我一下吗? func ta
我正在使用 CloudKit 作为数据库创建一个简单的待办事项列表应用程序。目前我可以添加和删除对象,但对编辑对象感到困惑。 编辑项目 Controller protocol EditItemCont
我正在针对以下任务训练 RNN:给定一个包含 30 个单词的序列,然后将该序列分类为二进制类。 在我的网络中拥有超过 30 个单元(LSTM、GRU 或普通 RNN)有好处吗?我在网上看到过很多例子,
我是一名优秀的程序员,十分优秀!