vba 我是否正确使用了 isnumeric 函数？

Am I using the isnumeric function correctly?

提问by TheTreeMan

This program is to convert a column of data from cumulative to non-cumulative. On my sheet I have A1, B1, and C1 with the text Non-Cumulative, Cumulative, and Converted, respectively. I have numbers 1 to 10 beneath A1, then them summed cumulatively beneath B1. C1 is where I want to convert column B back to non-cumulative.

该程序是将一列数据从累积转换为非累积。在我的工作表上，我有 A1、B1 和 C1，分别带有文本 Non-Cumulative、Cumulative 和 Converted。我在 A1 下有 1 到 10 的数字，然后它们在 B1 下累加。C1 是我想将 B 列转换回非累积的地方。

The IsNumeric is used to make the first row of data in C equal to the first row of data in B. It should detect that the title is above the number it is evaluating, thus knowing that no calculations have to be performed. For the rest of them, it'll see that the number above the one it is evaluating is a number, and thus the calculation has to be done.

IsNumeric 用于使 C 中的第一行数据等于 B 中的第一行数据。它应该检测标题高于它正在评估的数字，因此知道不必执行任何计算。对于其余的，它将看到它正在评估的数字上方的数字是一个数字，因此必须进行计算。

My problem is that it isn't working. I think the reason is because IsNumeric() keeps coming back as false. Is there a different function I should be using? Do cell references not work in IsNumeric?

我的问题是它不起作用。我认为原因是因为 IsNumeric() 不断返回为 false。我应该使用不同的功能吗？单元格引用在 IsNumeric 中不起作用吗？

Here's the program!

这是程序！

Option Explicit

Dim i As Variant

Sub Conversion()

Sheets("Test Sheet").Select

For i = 1 To 10
    If IsNumeric("B" & i) = False Then
        Range("C" & i + 1) = Range("B" & i + 1)
    Else: Range("C" & i + 1) = Range("B" & i + 1) - Range("B" & i - 1)
    End If
Next

End Sub

回答by bonCodigo

The way you wrote your code is logical, just a minor syntax changes you need initially. However,

你编写代码的方式是合乎逻辑的，只是你最初需要的一个小的语法更改。然而，

• It's also best to check if the range is empty first...
• Then check on if the value is numeric.
• Better even, if you set the Range into a Range object and use offset

• 最好先检查范围是否为空...
• 然后检查该值是否为数字。
• 甚至更好，如果您将 Range 设置为 Range 对象并使用 offset

Code:

代码：

Option Explicit '-- great that you use explicit declaration :)

Sub Conversion()
  Dim i As Integer '-- integer is good enough
  Dim rngRange as Range

  '-- try not to select anything. And for a cleaner code
  Set rngRange = Sheets("Test Sheet").Range("B1") 

    For i = 1 To 10
      If (rangeRange.Offset(i,0).value) <> "" then '-- check for non-empty
        If IsNumeric(rangeRange.Offset(i,0).value) = False Then
           rangeRange.Offset(i+1,1) = rangeRange.Offset(i+1,0)
        Else
           rangeRange.Offset(i+1,1) = rangeRange.Offset(i+1,0) - rangeRange.Offset(i-1,0)
        End If
      End if
    Next i '-- loop
End Sub

---

---

To make your code more dynamic:

要使您的代码更加动态：

• Another suggestion, you may simply Application.WorkSheetFunction.Transpose()the entire B column rangethat you need to validate into a variant array
• Process the array and Transposeback to the Range with column B and C.
• By doing so, you may omit setting for loop size manually but setting it using Lowerand Upperbound of the array ;)

• 另一个建议，您可以简单地Application.WorkSheetFunction.Transpose()将B column range需要验证的全部内容转换为variant array
• 处理数组并Transpose返回到包含列 B 和 C 的范围。
• 通过这样做，可以省略手动设置为环的大小，但使用它设置Lower和Upper所述阵列的结合;）

回答by scott

You need to check if the range of B i is numeric, not the string "B" & i and rather than selecting the sheet, simply using a parent identifier like:

您需要检查 B i 的范围是否是数字，而不是字符串“B”& i，而不是选择工作表，只需使用父标识符，例如：

sheets("sheet1").range("B" & i)  

This will help you avoid errors in your code

这将帮助您避免代码中的错误

For i = 1 To 10
    If IsNumeric(sheets("test sheet").range("B" & i).value) = False Then
        Range("C" & i + 1) = Range("B" & i + 1)
    Else: Range("C" & i + 1) = Range("B" & i + 1) - Range("B" & i - 1)
    End If
Next