当我为vba使用相同的代码，相同的数据但使用不同的笔记本电脑时，为什么会得到不同的输出? [英] Why do i get different output when i used the same code, same data but different laptops for vba?

问题描述

当单元格位于标题"Vbd"下方时，我试图使单元格的整个列都是绝对的，我遇到了这个问题:当我在不同的笔记本电脑上使用相同的代码和相同的数据时(一个是excel 2010，另一个是一个是excel 2016)，它提供了不同的输出.例如:

I am trying to make an entire columns of cells absolute when the cells are below the header "Vbd" and I faced this problem: when I use the same code and same data on different laptops (one is excel 2010 and the other one is excel 2016), it gives different output. For instance:

在我按下代码之前，就是这样.在装有excel 2010的笔记本电脑上按下该键后，将显示以下输出:

Before I press the code, it was like this. After pressing it with the laptop with excel 2010, it gives this output:

但是，当我使用具有excel 2016的笔记本电脑时，它会为我提供理想的输出，如下所示:

However when I used the laptop that has excel 2016 it gives me the ideal output which is something like this:

问题是我为这两台笔记本电脑使用了相同的数据和相同的代码(我已经多次检查代码和数据是否相同)，而且我非常困惑为什么输出不同.下面是我的代码 并且可以在此处找到该工作簿. (Dropbox)

The thing is I used the same data and same code for these two laptops (I have checked several times that both codes and data are the same) and I am super confused why the output is different. Below is my code and the workbook can be found here. (Dropbox)

Option Explicit

Sub testing1()
Dim i As Long
Dim LastColumn As Long
Dim sht As Worksheet
Dim rngToAbs As Range
Dim lastrow As Long



Set sht = ThisWorkbook.Sheets("Sheet1")
LastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
lastrow = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row
 For i = 1 To LastColumn
     With sht
         If sht.Cells(1, i).Value = "Vbd" Then
             Set rngToAbs = .Range(sht.Cells(2, i), sht.Cells(lastrow, i))
             rngToAbs.Value = .Evaluate("=abs(" & rngToAbs.Address & ")")
         End If
     End With
 Next


End Sub

推荐答案

@ScottCraner提供的答案，但会发布以供将来参考.

Answer provided by @ScottCraner but posting for future reference.

更改

.Evaluate("=abs(" & rngToAbs.Address & ")")

到

.Evaluate("=INDEX(abs(" & rngToAbs.Address & "),)")

当前版本有效，因为如果您具有最新版本的Excel，则该公式将作为数组公式求值.

The current version works because the formula is evaluated as an array formula, if you have the latest version of Excel.

对于早期版本的Excel，必须添加INDEX才能评估该范围内每个单元格的ABS.

Adding the INDEX is necessary for older versions of Excel to evaluate the ABS of each cell in the range.

也在原始问题中添加了此评论

这篇关于当我为vba使用相同的代码，相同的数据但使用不同的笔记本电脑时，为什么会得到不同的输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！