VBA Help

[richlands92]

Hi guys,
1st time posting so bare with me please.

ill explain what I have then what I want to do if anyone can help please.

im Working With Columns: I,J,K,L Rows 3-42

I - Number 0-3 (0=5days, 1=7days, 2=14days, 3=30days)
J - Start date
K - Days Since Start date
L - estimate ready date
J1 - Today Date - Formula "=Today()"

When number 0-3 is entered in a Cell in Column I,
The Below Code Date Stamps the adjacent cell in Column J (Which Also allows me to change the date if needed) - this part is prefect.

I have a Formula in Column K "=IF($J$1*J20=0,"",SUM($J$1-J20,))"
Which Subtracts the current date from the date entered in Column J and displays the difference between the 2 dates in days.

Then Column L has Formula "=IF(J3=0,"",J3+VLOOKUP(J3,Control!$L$5:$M$9,2,FALSE))"
That Looks up a Table in a tab called Control and adds depending on what number is entered in Column I, days to the date entered in Column K to produce a Estimate ready date.



So My Problem is if I want to change the estimate Ready date But I cant as it will Wipe out the V-look formula.

So it is possible to add thee above Formulas into the below code and for the user to be able to change estimate ready date like im able to do with the Start Date.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
       



        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("I3:I42"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                Me.Unprotect Password:="123"
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Me.Protect Password:="123"
                Else
                    Me.Unprotect Password:="123"
                    With .Offset(0, 1)
                        .NumberFormat = "dd-mmm-yy"
                        .Value = Date
               Me.Protect Password:="123"
                End With
                
                End If
                Application.EnableEvents = True

            
        End If
        End With
    
    
    End Sub

Thanks For reading and any Help in This problem!

 

[baitmaster]

welcome to the board

I can't see how this wipes out your Vlookup formula? Your code only runs when you change column I, and only changes .offset(0,1) = column J

If you are saying the formula simply becomes incorrect, then can you not just amend the formula to take account of any additional (+/-) days?

 

[richlands92]

welcome to the board

I can't see how this wipes out your Vlookup formula? Your code only runs when you change column I, and only changes .offset(0,1) = column J

If you are saying the formula simply becomes incorrect, then can you not just amend the formula to take account of any additional (+/-) days?

Hi baitmaster, thanks for reply

Right now the code date stamps Column J (if I want I can change this date if I need another date) then the Vlookup Finds a value depending on what is in Column I then adds that to date produced by the code into Column L (But I cant change the value because I will erase the vlookup formula)

what im wanting to do is basically have the Vlookup incorporated into that code aswell, as currently once the code date stamps Column J, I am able to change the date stamp to a different date if I need to.
but since the Vlookup is a formula in Column L, if I try to change the date in a cell in column L with the vlookup formaula in place, I will erase the formula.

So is it possible to have the Vlookup in that code or another means that will find a value then add the date produced by the code but will also allow a user to change that value?

not sure if im worded that correctly but let me know if that makes sense

thanks for your help.

 

[baitmaster]

I think I'm getting confused because your VLOOKUP isn't looking at column I according to your first post, and I'm probably worrying too much about what your doing rather than just answering your question

Simple answer, yes its possible to write a formula in VBA in various ways. Write your formula, then start recording a macro, enter the cell with the formula, hit Enter and then stop recording your macro. I've done this in Cell L3, containing your formula that looks at cell J3 (from post 1) and got the following text:

ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,"""",RC[-2]+VLOOKUP(RC[-2],Control!R5C12:R9C13,2,FALSE))"

Main points:
RC format is as follows:
RC = same row, same column
R1C2 = row 1, column 2 (uses $ because reference is fixed) = always $B$1
R[1]C[-2] = 1 row below, 2 columns to left of where you are writing formula (does not use $ because reference is relative)

The easiest way to simplify the Vlookup would be to name the Vlookup table to e.g. "controlData"

VLOOKUP(R[-2]C[-5],controlData,2,FALSE)

this should be enough for you to adapt to your needs, let me know if you have questions

 

[richlands92]

I think I'm getting confused because your VLOOKUP isn't looking at column I according to your first post, and I'm probably worrying too much about what your doing rather than just answering your question

Simple answer, yes its possible to write a formula in VBA in various ways. Write your formula, then start recording a macro, enter the cell with the formula, hit Enter and then stop recording your macro. I've done this in Cell L3, containing your formula that looks at cell J3 (from post 1) and got the following text:

ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,"""",RC[-2]+VLOOKUP(RC[-2],Control!R5C12:R9C13,2,FALSE))"

Main points:
RC format is as follows:
RC = same row, same column
R1C2 = row 1, column 2 (uses $ because reference is fixed) = always $B$1
R[1]C[-2] = 1 row below, 2 columns to left of where you are writing formula (does not use $ because reference is relative)

The easiest way to simplify the Vlookup would be to name the Vlookup table to e.g. "controlData"

VLOOKUP(R[-2]C[-5],controlData,2,FALSE)

this should be enough for you to adapt to your needs, let me know if you have questions

Thanks Baitmaster,

I've Re-read what I originally wrote and now I can see why its not making sense to you because of my typo error
the Vlookup should be "=IF(J3=0,"",J3+VLOOKUP(I3,Control!$L$5:$M$9,2,FALSE))"

Does this make more sense now why Column I is involved in the vlookup?

ill play about with what info you've gave me (thanks you very much for your help on that :D) but my vba is very limited and im not too sure on where to place the code into my original code.

thanks for your help again