Forum Discussion

kimmie2430's avatar
kimmie2430
Copper Contributor
Oct 27, 2022
Solved

convert excel formula to vba code

Hi,

 

appreciate if anyone could help me to convert the formula below to vba code:

 

=IF(ISBLANK(I40),"",IF(I40<1,K40,K40+I40-1))

 

If cell I40 is blank, then M40 is empty, if cell I40 is <1, then M40=K40, otherwise M40=K40+I40-1.

 

Thank you very much.

 

excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Oct 28, 2022

    kimmie2430 

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("I:I"), Target) Is Nothing Then
        
        If Target.Count > 1 Then
            Exit Sub
        End If
        ' clean the value
        Debug.Print Target.Address
        
        If Trim(Target.Value) <> "" Then
        
            If Range("I" & Target.Row).Value < 1 Then
                Range("M" & Target.Row).Value = Range("K" & Target.Row).Value
            Else
                Range("M" & Target.Row).Value = ((Range("K" & Target.Row).Value) + (Range("I" & Target.Row).Value) - 1)
            End If
        Else
        Range("M" & Target.Row).Value = ""
                    
        End If
    End If
End Sub

    This code works in my sheet.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 27, 2022

    kimmie2430 

    Sub Makro1()

Dim i As Long

For i = 40 To 60
    Range("M" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-4]),"""",IF(RC[-4]<1,RC[-2],RC[-2]+RC[-4]-1))"
Next i

End Sub

    Maybe with these lines of code. In the attached file you can click the button in cell P40 to run the macro.

    • kimmie2430's avatar
      kimmie2430
      Copper Contributor
      Oct 27, 2022

       

      Hi OliverScheurich , thanks for your prompt reply, actually i just need a simple vba which is whenever i insert a digit on cell I40, only will run the formula behind, instead of clicking a button to run the macro. I think is using Private Sub.

       

      If cell I40 is empty, then M40 is empty, if i insert a digit on cell I40 is <1, then M40=K40, otherwise M40=K40+I40-1. 

       

      Im newbie to this community wondering how to upload my excel file 😄

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Oct 27, 2022

        kimmie2430 

        Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("I40"), Target) Is Nothing Then

    Range("M40").FormulaR1C1 = _
        "=IF(ISBLANK(RC[-4]),"""",IF(RC[-4]<1,RC[-2],RC[-2]+RC[-4]-1))"
Else
End If

End Sub

        You can try this code in the attached file. The result in cell M40 changes according to the entries in cell I40.

Resources