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.

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
    Nov 02, 2022

    Hi OliverScheurich ,

     

    I have tried to follow your step to clear all the formula on column O, but the error msg still popping out...

    Then i look for the circular reference on formula tab but there is no more...

    Could you please check for me please?

     

    Im uploading the excel file for your perusal...

     

    THank you very much...

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 01, 2022

    kimmie2430 

    You are welcome. I'm not sure what you want to do with columns P & Q. Maybe you want to start a new discussion and ask the experts of the Community.

  • kimmie2430's avatar
    kimmie2430
    Copper Contributor
    Nov 01, 2022
    Hi OliverScheurich,

    Thanks for helping on the error... It is helpful...
    Could you please help on the 2 previous formulas on the column P & Q as well?
    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.

     

     

  • 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!!

Resources