convert excel formula to vba code

[kimmie2430]

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.

[#2]

@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.

[#3]

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 :D

[#4]

@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.

[#5]

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.

[#6]

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 

[#7 · Accepted solution]

@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.

[#8]

This code works in my sheet as well.
Thank you very much!

[#9]

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

[#10]

@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.

[#11]

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

[#12]

@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.

[#13]

@OliverScheurich
alright, sure, thanks for your help!

[#14]

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