如果单元格值与较高的单元格值相同 [英] If cell value same with upper cell value

问题描述

我试图在日常工作中使用宏，但是由于我的excel文件中包含太多项，因此我无法使用IF作为公式，因此解决方案是将公式转换为VBA代码.

I tried to make macro for my daily job, but i cannot use IF as formula due to so many item in my excel file, so solution is to convert formula to VBA code.

我需要帮助将以下公式转换为excel中的VBA代码: = IF(J2< J1，AD2-X2，AE1-X2).

I need help to convert if formula to VBA code in excel as below: =IF(J2<>J1,AD2-X2,AE1-X2).

推荐答案

以下是您问题的解答.但是，它仅限于使用OP信息.另外，如果计算花费的时间太长，则应尝试将计算设置为手动"(公式"->计算选项"->手动").

Here is an answer to your question. However, it is limited to only work with OP information. Also, if the calculations are taking too long then, you should try setting your calculation to Manual (Formulas->Calculation Options->Manual).

Option Explicit

Public Sub RunIF()
    Dim vntOut As Variant
    Dim rngSame As Range

    With ActiveSheet

        Set rngSave = .Range("X2")

        If (LCase(Trim(.Range("J2").Value)) <> LCase(Trim(.Range("J1").Value))) Then
            vntOut = .Range("AD2").Value - rngSave.Value
        Else
            vntOut = .Range("AE1").Value - rngSave.Value
        End If

        .Range("AE2").value = vntOut

        Set rngSave = Nothing
    End With

End Sub

这是将您的代码转换为使用列J:

And here is your code converted to use Column J:

Private Sub CommandButton12_Click()
    Dim x As Long
    Dim LastRow As Long
    Dim i as long 

    With Sheets("Shipping Schedule")
        LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row

        For i = 2 to LastRow

            set r = .Range("J" & I)
        'For Each r In .Range("J2:J" & LastRow)
            If LCase(Trim(r.Value)) <> LCase(Trim(r.Offset(-1, 0).Value)) Then
                'ae2 =                      "AD2"             -   "x2"
                r.Offset(0, 21).Value = r.Offset(0, 20).Value - r.Offset(0, 14).Value
            Else
                'ae2 =                      "AE1"         -        "x2"
                r.Offset(0, 21).Value = r.Offset(-1, 21).Value - r.Offset(0, 14).Value
            End If

            set r = nothing 

        Next i
    End With
End Sub

但是，您应该增加I而不是每个值，因为单元格取决于上一行，并且excel可能不会像您希望的那样遍历该范围.

However, you should increment with I instead of for each as the cells are dependent on the previous row and excel may not loop through the range like you would prefer.

这篇关于如果单元格值与较高的单元格值相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！