Forum Discussion

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, t...

excel

Formulas and Functions

Macros and VBA

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.

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

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.

nested if.xlsm17 KB

kimmie2430

Copper Contributor

Oct 28, 2022

Hi OliverScheurich,
I think i gave a wrong information,
actually i would like to have the whole column instead of only the cell.
If any of the cell in column I is empty, then the same row of M is empty, if i insert a digit on any cell in column I is <1, then then same row of column M=K, otherwise M=K+I-1.
Thanks for your help.

kimmie2430

Copper Contributor

Oct 28, 2022

Hi @Quadruple_Pawn,

I have tried below formula but it seems not working, could you please help?

Thank you very much.

'view_range.Select
    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
            End If
            If Range("I" & Target.Row).Value <= 1 Then
                Range("M" & Target.Row).Value = ((Range("K" & Target.Row).Value) + (Range("I" & Target.Row).Value) - 1)
            End If
        End If
    End If

kimmie2430

OliverScheurich

Gold Contributor

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.