Excelnoub
Member
Fill Series – Offset – VBA Excel 2007 help
Hope everyone had wonderful holidays. I would need help and expert advice on a problem I cannot seem to get pass.
I have a sample of my workbook at excelforum.com but right now I can’t seem to get in.
Kind of difficult to explain when you don’t have the big picture in front of you.
Column M will have a contract number EX: Test01/001/HS.
My Column Q has a validation List made up of 3 options. amd,term and option.
If a user triggers a selection from Column Q a msgbox will appear asking them Yes, No, Cancel.
I have the code for amd and part of the term and option.
If a user select “term” and then select “Yes” from the Msgbox. The following code will run:
[pre]
[/pre]
But on the new row I need to change the information of the contract number to the new contract therefore taking the title/contract number/division (Testing/001/HS) and only changing the contract number making the new number the following Testing/002/HS.
I would really need help the explanation is quiet complex.
I could try to have a better explanation, let me know.
Hope everyone had wonderful holidays. I would need help and expert advice on a problem I cannot seem to get pass.
I have a sample of my workbook at excelforum.com but right now I can’t seem to get in.
Kind of difficult to explain when you don’t have the big picture in front of you.
Column M will have a contract number EX: Test01/001/HS.
My Column Q has a validation List made up of 3 options. amd,term and option.
If a user triggers a selection from Column Q a msgbox will appear asking them Yes, No, Cancel.
I have the code for amd and part of the term and option.
If a user select “term” and then select “Yes” from the Msgbox. The following code will run:
[pre]
Code:
Target.Offset(1, 0).EntireRow.Insert
Cells(Target.Row, 18).Value = Cells(Target.Row, 13).Value & "/T01"
Range("Q" & Target.Row).ClearContents
Range("A" & Target.Row + 1).Resize(, 15).Value = Range("A" & Target.Row).Resize(, 15).Value'
I need it to do the follwoing but I don't know where to start. I tried with a fill Series but it's doesn't work.
Need to:
If the user select "term" from the Q column and then select "Yes" from the msgbox option then change the contract number.
Test01/001/HS should be Test01/002/HS when the new row is created.
Lets say that my target.row "M" equals the following:
Testing/001/HS (this will always be the format) title/contract number/division.
This code has already been set up.
Now lets say that a user select from the drop down validation list in column Q the "term" selection then a msgbox will say:
Msg = MsgBox("Select Yes if another contract will be awarded to " & Range("M" & TRow) & "" & vbNewLine & _
vbNewLine & "Select No if no contract will be awarded" & vbNewLine & _
vbNewLine & "Select Cancel to exit", _
vbYesNoCancel + vbQuestion + vbSystemModal, "Termination of Contract. Continue?")`
If the user select "Yes" from the msgbox selection then I need it to
Target.Offset(1, 0).EntireRow.Insert
Cells(Target.Row, 18).Value = Cells(Target.Row, 13).Value & "/T01"
Range("Q" & Target.Row).ClearContents
Range("A" & Target.Row + 1).Resize(, 15).Value = Range("A" & Target.Row).Resize(, 15).ValueBut on the new row I need to change the information of the contract number to the new contract therefore taking the title/contract number/division (Testing/001/HS) and only changing the contract number making the new number the following Testing/002/HS.
I would really need help the explanation is quiet complex.
I could try to have a better explanation, let me know.