If cell value same with upper cell value

excel formula if cell contains text then return value in another cell
if function excel
how to fill blank cells with value above in excel
excel formula reference cell above
vlookup excel
index match
excel previous cell + 1
match function excel

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.

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

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

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

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.

Excel Formula, How do I fill a cell based on another cell value in Excel? You don’t have to repeat the same steps in order to check all the other cells as that would be too much of a hassle. Instead, you can drag the fill handle, which is a small square located in the bottom-right corner of the cell, all the way down. This will copy and apply the formula to all the other cells. After you have done that, you should notice the FALSE value in C6, C9, C11, and C14. The rest of the cells in the C column are marked as TRUE because the formula found a match.

Excel Formula to VBA: Fill Column
Sub FillColumn()

    Const cCol As Variant = "J"    ' Last-Row-Column Letter/Number
    Const cCol1 As Variant = "AD"
    Const cCol2 As Variant = "X"
    Const cCol3 As Variant = "AE"
    Const cFirstR As Long = 1      ' First Row

    Dim rng As Range    ' Last Used Cell in Last-Row-Column
    Dim i As Long       ' Row Counter

    Set rng = Columns(cCol).Find("*", , xlFormulas, , xlByColumns, xlPrevious)
    If rng Is Nothing Then Exit Sub

    For i = cFirstR To rng.Row - 1
        If Cells(i + 1, cCol) <> Cells(i, cCol) Then
            Cells(i + 1, cCol3) = Cells(i + 1, cCol1) - Cells(i + 1, cCol2)
          Else
            Cells(i + 1, cCol3) = Cells(i, cCol3) - Cells(i + 1, cCol2)
        End If
    Next

End Sub

How to fill blank cells with value above / below / left / right in Excel?, How do you repeat a cell value until new value is seen or reached in Excel? If you want to do something specific when a cell equals a certain value, you can use the IF function to test the value, then do something if the result is TRUE, and (optionally) do something else if the result of the test is FALSE. If color is red, mark with "x" In the example shown, we simply want to "mark" or "flag" records where the color is

Private Sub CommandButton12_Click()

    Dim x As Long

    Dim LastRow As Long 

    Sheets("Shipping Schedule").Select


    With Sheets("Shipping Schedule")

        LastRow = .Cells(.Rows.Count, "N").End(xlUp).Row

        For Each r In .Range("N2:N" & LastRow)

            If r.Value <> "" Then

                r.Offset(0, 19).Value = ………………………………….              

            End if      

        Next r      

    End With

End Sub

How to use IF function in Excel: examples for text, numbers, dates , How do you find the upper cell value in Excel? How to fill blank cells ( in the row) using upper values with IF condition. I would like to file the blank cell in row (XXXX) with upper values( in the 1<sup>st</sup> row) just , if there values in adjacent cell ( colored)

Excel conditional formatting formulas, Built-in Functions. INDIRECT - The text string of the contents of a given cell reference. ADDRESS - The cell reference given a row and column  Look up a value and return cell above. Select a blank cell that you want to place the return value, and type this formula =INDEX(A1:A8,MATCH(D1,A1:A8,0)-1,1), press Enter key to return the value. See screenshot: Look up a value and return cell below. Select a blank cell that you want to place the return value, and type this formula =INDEX(A1:A8,MATCH(D1,A1:A8,0)+1,1), press Enter key to get the result. See screenshot:

How to use the Excel EXACT function, When we set up data in Excel, we always leave a blank cell if the data for that cell is Note:This utility also can fill blank cells based on linear values, from top to Open and create multiple documents in new tabs of the same window, rather  Now, you can use the formula to get the value from the above cell and fill in the blank cells in Excel. Follow the following steps to use a formula to copy the value from the cell above: Enter = (equal to sign). by default, this will enter the equal to sign in the active cell only

Excel: Fill Blanks With Value Above, Blank cells, ="", Evaluates to TRUE if a specified cell is Works the same as the above formula, but returns 1 if A1 Time WOB: tons Top Drive RPM: Result of value of time Is it possible to apply conditional formatting to a whole sheet such that whenever the cell above the current cell has the same value (either text or number) as the current cell, the current cell will be formatted yellow fill/blue font? For example, when cell: A1 is blank A2 AAA A3 AAA A4 AAA A5 is blank A6 AAA A7 AAA

Comments
  • Can you provide more information? is this happening over multiple cells? what is the intent? Please read the following: How to Ask
  • Sorry for my lack of information about this case.
  • Do I understand this correctly? The formula is slowing down your already slow worksheet and you want to be able to press a button to fill a column with data according to the formula. There is a drawback that this column will only update when you press the button. BTW, Which column is it?
  • I see you have asked the same question again. There is no need for that. You can edit this question anytime. There is an edit button right below your post. Click it and make changes.
  • I used tu run macro with excel formula =IF(J2<>J1,AD2-X2,AE1-X2). But it's very slow calculation, i think its because running a formula. So i try to simplify the formula to vba code. My column is AE2 =IF(J2<>J1,AD2-X2,AE1-X2)
  • What's up? the comments are not for discussion. you should start a chat for that.
  • Hi can i have a discussion with you by email regarding my matter?
  • Hello, it almost work but, i tried to use else function and fail. How about else for function "AE1-X2"? It almost done
  • OK, I updated it to work with AE1 and X1 as well as AD2 and X2.
  • r.Offset(0, 19).Value = …………………………………. (Convert this formula to vba excel code =IF(J2<>J1,AD2-X2,AE1-X2)