有没有一种方法可以将所有值从一个数组传输到另一个数组，然后擦除原始数组? [英] Is there a way to transfer all values from one array to another, then erase the original array?

问题描述

我在工作中尝试开发的代码块遇到了问题.本质上，我在excel中创建了一个用户窗体，当人们在特定位置加载铁路车辆时，他们将输入铁路车辆的数据(我们将其称为地点1，地点2，地点3等").

有时候，他们不得不将那辆车移到另一个地点，在这种情况下，我希望他们能够从第一个/原始条目中保留轨道车上的所有信息，然后从原始地点清除数据一旦完成.

为了以更简化的方式完成此操作，我为5个点中的每个点建立了数组，这些点引用了用户在用户表单上输入数据的所有单元格:

 暗点1信息(14)spot1information(0)= UserForm.ProductType1.Valuespot1information(1)= UserForm.ProductID1.Valuespot1information(2)= UserForm.BatchID1.Value等等....昏暗的Spot2信息(14)spot2information(0)= UserForm.ProductType2.Valuespot2information(1)= UserForm.ProductID2.Valuespot2information(2)= UserForm.BatchID2.Value等等.... 

对于所有五个点，依此类推.我不知道这是否会使事情变得更加困难，但是请注意，这些数组值并非全部相同.例如，索引(0)将是一个字符串，而索引(10)将是一个DATETIME，而索引(12)则被定义为Long.

所以说他们要把汽车从1号点移到2号点.简而言之，我希望代码执行以下操作:

• 将spot2信息(当前为空)中的索引0-6的值替换为spot1信息(用户已在用户窗体上填写的)中的索引0-6的值.
• 我只对结转0-6索引感兴趣，因为它们包含了相关的轨道车辆信息
• 将spot1信息的每个值都清空为".

为此，我尝试了以下代码及其一些变体:

 如果OriginalSpot.Value = 1然后如果DestinationSpot.Value = 2，则对于i = 0到6spot2information(i)= spot1information(i)下一个对于Spot1信息中的每个ispot1information(i)=".下一个万一万一 

但是，这总是导致类型不匹配.我认为是因为spot2information数组中的数据为空，而spot1information数组中的数据不是，但是我不确定如何解决这个问题.

---

更新:我做了以下建议，并用 Erase spot1information

替换了 spot1information(i)="

代码现在基本上可以工作了！数组"spot2information"的值被设置为0.现在是"spot1information"的以前的值，而"spot1information"是"spot1information"的值.现在是空的.

以下建议的2D阵列也可以像超级按钮一样工作.我一直面临的新问题是数组值正在更新，但用户窗体不是.(请注意:以后，我将把这种类型的问题作为一个单独的问题发布，我深表歉意！)

解决方案

假定数组的 DataType 与 Index 相同，即 index(0)是所有斑点的 string ， Index(2)是所有斑点的 long ，依此类推.

如果是这种情况，则此部分不应产生任何错误:

 对于i = 0到6spot2information(i)= spot1information(i)下一个 

该部分应该更准确地在标记为#

的行中发生错误

 对于spot1信息中的每个ispot1information(i)=".'#下一个 

以及出现错误的原因似乎是在给定"mismatch" 错误的情况下，尝试将字符串值"" 分配给数字类型

对Spot1信息中的每个i使用表示您要启动" 或

您刚刚更新了数组，然后需要运行用于更新 UserForm 中受两个数组影响的对象的值的过程.

I'm running into a problem with a block of code I'm trying to develop at my job. Essentially, I'm creating a userform in excel where folks will enter data for railcars as they get loaded at a certain location (we'll call these "spot 1, spot 2, spot 3, etc.").

Sometimes they'll have to move that car to a different spot, in which case I want them to be able to keep all the information on the railcar from the first/original entry, and then erase the data from the original spot once that's done.

To accomplish this in a more streamlined fashion, I've established arrays for each of the 5 spots that reference all the cells they're entering data into on the userform:

Dim spot1information(14)
    spot1information(0) = UserForm.ProductType1.Value
    spot1information(1) = UserForm.ProductID1.Value
    spot1information(2) = UserForm.BatchID1.Value
    etc....

Dim spot2information(14)
    spot2information(0) = UserForm.ProductType2.Value
    spot2information(1) = UserForm.ProductID2.Value
    spot2information(2) = UserForm.BatchID2.Value
    etc....

And so forth for all five spots. I don't know if this makes things more difficult, but note that these array values aren't all of the same type. For instance, index (0) will be a string, but index (10) is a DATETIME and index (12) is defined as Long.

So say that they are moving a car from spot 1 to spot 2. In short, I want the code to do the following:

• Replace the values of indices 0 - 6 in spot2information (which is currently empty) with the values of indices 0 - 6 in spot1information (which the user has filled on the userform).
• I'm only interested in carrying over indices 0-6 because they contain the pertinent railcar information
• Empty every value of spot1information to ""

To accomplish this, I tried the following code and a few variations thereof:

If OriginalSpot.Value = 1 Then
    If DestinationSpot.Value = 2 Then
        For i = 0 to 6
           spot2information(i) = spot1information(i)
        Next
        For Each i in spot1information
           spot1information(i) = ""
        Next
     End If
 End If

However, this keeps coming up with a type mismatch. I figure because the data in the spot2information array is empty, and the data in the spot1information array is not, but I'm not entirely sure of a way around this.

---

Update: I did what was suggested below and replaced: spot1information(i) = "" with Erase spot1information

The code now essentially works! The values of array "spot2information" are now the former values of "spot1information", with "spot1information" now empty.

The 2D array suggested below also works like a charm. New problem I've been facing is that array values are updating, but the userform isn't. (note: in the future I'll be posting this type of thing as a separate question, my apologies!)

解决方案

Assuming the DataType of the arrays is the same by Index i.e. index(0) is string for all spots, Index(2) is long for all spots, and so on.

If that is the case then this part should not produce any error:

    For i = 0 to 6
       spot2information(i) = spot1information(i)
    Next

The error should be happening in this part more precisely in the line marked with #

    For Each i in spot1information
       spot1information(i) = ""   '#
    Next

and the reason for the error it seems to be that trying to assign a string value "" to a numeric type, given the "mismatch" error.

Using For Each i in spot1information indicates that you want to "Initiate" or Erase the entire array, therefore I suggest to use this line instead of the For…Next method.

Erase spot1information

In regards this:

But I've now run into a new problem, where the values on the userform haven't updated to reflect the new values stored in the array. Do I need to somehow "refresh" the userform?

You just updated the arrays, then you need to run the procedures used to update the values of the objects affected by both arrays in the UserForm.

这篇关于有没有一种方法可以将所有值从一个数组传输到另一个数组，然后擦除原始数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！