我正在使用isnumeric功能吗？ [英] Am I using the isnumeric function correctly?

问题描述

此程序是将一列数据从累积转换为非累积数据。在我的表单上，我分别有A1，B1和C1，文本分别为非累积，累积和转换。我在A1下面有1到10个数字，然后它们累积在B1之下。 C1是我要将列B转换回非累积的地方。

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.

IsNumeric用于使C中的第一行数据等于第一行数据在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（）不断返回为false。是否有不同的功能我应该使用？细胞参考不能在IsNumeric中使用？

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?

这是程序！

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

推荐答案

您编写代码的方法是合乎逻辑的，只是您最初需要的一个较小的语法更改。但是，

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

• 最好检查范围是否为空...


• 然后检查该值是否为数字。


• 如果将Range设置为Range对象并使用 offset

• 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

代码：

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

---

为使您的代码更有活力：

<另一个建议，你可以简单地 Application.WorkSheetFunction.Transpose（）你需要的整个 B列范围验证成变体数组

• 将数组和转置范围与列B和C。


• 通过这样做，您可以手动省略设置循环大小，但使用 Lower 和上限数组的界限;）

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

这篇关于我正在使用isnumeric功能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！