Changing information based on cell change (VBA complex)

[Excelnoub]

Good day,
I am having trouble with a code that I have been trying to accomplish. I am as my name, noob.

I have a report sheet that I have completed and now I would be missing a final code to make it a masterpiece.

Like I said, it is a report sheet with steps. Therefore each Tab (6 total) has it's own process but will link to each other.

I have a userform on this final sheet, let's call it sheets("Report"). When I double click the project number I have a userform with some option that my users can chose (Command Buttons). once the selection is made, the user will be introduced to another UserForm. I have uploaded a demo to demonstrate what it is supposed to do.

Explanation (Also in workbook): I need to make a sort of vblookup or a match code to change the highest number inserted in a certain cell to change the content of other matching rows.

I will try to explain this best to my knowledge. I have a worksheet that will work as a report. This report will be based on Contracts. In one file (Physical) I can have multiple contracts. All linked to the same file number.

Example if file number 1234-45678 as 2 contracts then my code will automatically add the information required, adding 2 contracts for file number 1234-45678.

Therefore my initial row 5 (Example only) will add a new row underneath and will add the following information:

A5-1234-45678 B5-002 C5-1234-45678/001/HS
A6-1234-45678 B6-002 C6-1234-45678/002/HS

So my column A will always be the same linking the 2 contracts. 1234-45678/001/HS and 1234-45678/002/HS belong to file number 1234-45678.

If I need to make an amendment, in house an amendment is to make modification to a contract. In my column B I will always have the amount of contracts. In the example above the amount for Contracts is 002.

If I make an amendment and the reason of the amendment is a Termination, therefore cancelling one contract to create another one, I need to be able to change all information in my Column B vs All information from Column A. So for Example:

If
A5-1234-45678 B5-002 C5-1234-45678/001/HS
A6-1234-45678 B6-002 C6-1234-45678/002/HS

And I need to make the above modification then I need to add another Contract but change all information from my Column B (Made in a userform, already establish). So the above example will become:

A5-1234-45678 B5-003 C5-1234-45678/001/HS
A6-1234-45678 B6-003 C6-1234-45678/002/HS/T01 < - - - this line will be deleted
A7-1234-45678 B6-003 C6-1234-45678/003/HS

I have included a Demo with some userform to see what I need. You will see that when you add a number in Column M.

I only need this code to finally complete this Workbook...

I have been looking for something but nothing up to now.

Please help me solve this problem...

Thank you in advance, Excelnoub

 

[dan_l]

I get "can't find project or library". What version of excel is this for?

 

[Excelnoub]

2007

 

[Excelnoub]

I have deleted some stuff that was probably making this error.

 

[dan_l]

Aha! It's a reference to Microsoft Calendar control 2007 which is not present on my win7/office 2013 thing. But it's in my xp/07 vbox.

I'm still not clear on what you're trying to do. But, I think whatever it is will be much easier if you database your information on another worksheet and just use the report file as either a user interface or a display.

 

[Excelnoub]

I am still trying to figure out but the following code was provided to me:

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim row, lastrow, chng_row As Integer
Dim rng, chng_rng As Range
Dim newval, refval, tstring As String
Dim ws As Worksheet
 
Application.EnableEvents = False
tstring = Replace(Target.Address, "$", "")
Set chng_rng = Range(tstring)
Set ws = Worksheets("Sheet1")
lastrow = Range("A" & Rows.Count).End(xlUp).row
Set rng = Range("M1:M" & lastrow)
If Not Intersect(Target, rng) Is Nothing Then
    chng_row = chng_rng.row
    refval = ws.Cells(chng_row, 1)
    newval = ws.Cells(chng_row, 13)
    For row = 1 To lastrow
        If ws.Cells(row, 1).Value = refval Then ws.Cells(row, 13).Value = newval
    Next row
End If
Application.EnableEvents = True
End Sub

 

[Excelnoub]

Problem now, is that it is interfering with another code in the worksheet.