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

kimmie2430's avatar
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's avatar
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.

  • kimmie2430's avatar
    kimmie2430
    Copper Contributor
    Oct 28, 2022
    This code works in my sheet as well.
    Thank you very much!
    • kimmie2430's avatar
      kimmie2430
      Copper Contributor
      Oct 31, 2022

      Hi OliverScheurich

      Can u please help me to convert the column P & Q to vba as well? Because the error pop up msg as below is annoying....it come out whenever i open this excel...

       

      Im not sure if the formula is correct, but i would like to have The formula on column P is, IF column R = Completed or Make appointment, column P = column O - column N +1, else, column P is empty.
      which means when column N= 30 oct, column O = 31 Oct, actual duration of day on column P =2 days (include the first day).

       

      For column Q, 
      IF column R = Completed or Make appointment, column Q = column O - column K /7 and round up, else, column P is empty.
      which means when column K= 30 oct, column O = 31 Oct, actual duration of weeks on column Q =1 week (include the first day); when column K= 20 oct, column O = 27 Oct, actual duration of weeks on column Q =1 week; when column K= 20 oct, column O = 28 Oct, actual duration of weeks on column Q =2 weeks.

       

      For the above 2 formulas, No matter how the column R is change after once Completed or Make appointment is selected, column O P Q will remain unchange.

      Attached below with the file for your perusal.

       

      Thank you very much!!

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

        kimmie2430 

         

         

        There are cells with circular references in sheet "CompletedTask". For example in cell O84 there is a formula which contains the cell O84.

        =IF(R84="Completed", IF(O84="",NOW(), O84), "")

        This means the error message is not related to columns P & Q. You only have to correct the formulas in column O which have circular references.

         

         

Resources