PDA

View Full Version : VB Code help on Worksheets



VNouBA
05-17-2012, 10:55 AM
Something is not adding up to my Sheet4.

I would copy and paste all codes but could take a long time.

Sheet1 = main entry (Some files dont get to go on Sheet2 until complete information has been inserted and that is on Column E. Once this is complete then the user will go to the next sheet...

Sheet2 = Time frame, all code in here are almost perfect but doesnt copy well on Sheet4

Sheet3 = Short process but necessary (Only column C is transfered) all code in here are almost perfect but doesnt copy well on Sheet4

If you play a little with the sheets you will see the process... but to keep in mind that each files are different from on another... Some file can take up to 1 month to process and never move from sheet1. This is why it's not adding up to my sheets4 and 5.

VNouBA
05-17-2012, 10:58 AM
Details of what is required from each sheets are comming soon.

Omg if someone finds this problem... I will freak out of joy.

VNouBA
05-17-2012, 11:58 AM
Sheet1 is the main entry. All project received by the user will be imputed in this Sheet.
The user will need to insert the title the date he received the file and so on.
The user cannot continue the process until column E is completed.

This is the best explanation:

The user will receive a file. A number is attached to it. A date and an amount.
The user will then insert the information on the first line. In sheet1 the first cell is A5.
Example used is, project name “Testing”

Sheet1
A5 = Project number (“Testing”)
B5 = Date received (“2012-05-17)
C5 = Amount of the file (“100$”)
D5 = Relative information (Drop down menu with options)
E5 = Name of the person that will do the review. (Drop down menu with reviewer names)

A5 will be transferred to all sheets, except sheet6 as this is only the archives.
Also
A5, B5, C5 D5 and E5 will be transferred to sheet5 on the row of the project number.

Sheet2
You will see A5 is already populated from Sheet1’s A5.
Therefore on the same row the user will add more information that will be transferred to Sheet5 only… Same for Sheet3 and sheet4.

The problem I am having is the following:

If 4 projects is pending in Sheet1… Sheet4 will not respond to the next project

If project 5 is inserted in sheet1 then the user continues on sheet2 then sheet3 on sheet 4 that user will be able to do the data entry but will transfer on sheet5 on the next available cell / or row and this is where it gets complicated.

So info on Sheet1 will be the following

A5 = Project1
A6 = Project2
A7 = Project3
A8 = Project4
A9 = Project5

Sheet4 will be the following

A5 = Project1
A6 = Project2
A7 = Project3
A8 = Project4
A9 = Project5 (user is ready to add the contracts therefore B9 = 004) the VBA code will do it’s functions.

BUT

If the user goes and continue Project2 from Sheet1 the information will not go in the right cells vs that project.

This Workbook is a little complicated but only need tweeking on some codes. Most of the functions works kinda good.

VNouBA
05-17-2012, 06:58 PM
Let me know if I anyone needs more explanation.

VNouBA
05-18-2012, 09:28 AM
Any thoughts if I would need to go to a Consultation with this project?


I mean most of the codes are present but kind of out of my league to tweek it to the right commands.

Tinbendr
05-18-2012, 11:09 AM
This seems to be working.

VNouBA
05-18-2012, 05:49 PM
You guys are awsome

Thank you for also fixing my codes. Less traffic and clean.

VNouBA
05-18-2012, 06:00 PM
In the same workbook, is there a way to:

If a project started and ended on sheet2 so it's pending. The user will have another project and will be ready to continue, surpases sheet2 all the way the sheet5 (talking about the process).

Is there a way for the information to look-up "A" (project number) and to insert the info on the same line of that project number?

Ex:

if you add one project Sheet1 and then go in sheet2. But stop there. The project number will be in A5.

If you add a second project in Sheet1 then you insert the full process till you get to sheet5.

Lets say that the user continues project1. it will go in the next available cell in Project2 instead of project1.

Could this be fixed?

Plus the copy and paste in Sheet4 has a bug and only copies and paste I5 and J5 even if I select row 8

VNouBA
05-18-2012, 06:06 PM
I got the Sheet4 fixed:


If (Target.Address = "$I$" & TCol Or Target.Address = "$J$" & TCol) And Target.Value <> "" Then
Range("I" & TCol & ":J" & TCol).Copy
If Sheets("Report").Range("N" & TCol).Value = "" Then
Sheets("Report").Range("N" & TCol).PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
End If

Tinbendr
05-18-2012, 06:42 PM
Is there a way for the information to look-up "A" (project number) and to insert the info on the same line of that project number?
When I only need a short lookup, i use the worksheetfunction function.

Since you only need the row number;
Sub test()
MsgBox Application.WorksheetFunction.Match(Range("A5"), Worksheets("MERX").Range("A5:A30"))
End Sub
This will return the row # on Merx using Sheet1!A5 as the lookup. Of course, you'll have to pass the Column A value dynamically. E.g. Range ("A" & TCol) (And don't forget to add 5 since you start on row 5.)

I'm still not following the process all that well. Give it a shot. If you can't get the code to work, post again and give detailed processes for me and I'll try to help.


... Sheet4 has a bug and only copies and paste I5 and J5 even if I select row 8 I missed that one!

VNouBA
05-20-2012, 07:53 AM
I will explain the process as detailed as possible. By the way thank you so much forthe time you have helped me.

The workbook will be use by users to input information on all of the files that arein their possession.

There are four steps in this process.
Not all processes are at the same time.
The userwill be given a file to work on. When all is done, the user will then post this file on a system for a minimum of 40 calendar days.
When the 40 days are up the file is brought back for a review and to issue contracts.
This file can have multiple contracts.
Then a processfor delivery is watched until the user obtains all delivery invoices to then close the file.
The user will get about one file a week. Not all the files have the same timeframe. Some could take up to one year to be posted on the system, some in less than a day.

As an example, I have over 50 files, each with maybe 4 or more contracts attached to this same file.

I will give you the process of one file

We have 4 processes to the report. Each file is its own process.
Step one isthe pre-requisition stage. The user will build the file based on the request.So therefore this is why I have the files name, Date, amount, and the strategy.I also have a comment for each file if there is pending issues with the file.No files are the same. So each file will have a different name.
Step two is the posting on the system. The posting is a minimum of 40 days but could beextended depending if the users are asked for an amendment on this file. In the MERX sheet, I have added a section that the user will be able to test when itshould come out (40 days) then decide if they want this particular date. A postingcan never land on a weekend or holiday even. Then the user will have a view ofwhen the file will close from this system and also the file full date limit (90days).

Step threeis the evaluation period. The user will evaluate the file once it has returnand construct contracts based on how many winning companies. Each company willhave the same file number but different codes at the end. You will see insheet4 when you add a number in column B, it will add the file name and the /00?/HSfor the extensions. All must be linked to this same file.
Step fouris all the maintenance of the file and the contracts links to the file. Each contractare different but always belonging to the main file.
I have areport of when the user will have complete a delivery status, he will then addit to the last page, “Archives”. This will be for records only.
The managerwill be able to go in each user’s workbook and view the status of each filesfor his report. I have saved and locked each workbook with a password and themanager has a excel with hyperlink that he can view all his staff workload.

Now here is the tricky part. The function of the workbook will be the following. Everysheets starts at row5

Sheet1: (Iwill take example file “Project1” as a file name)
The userwill type in “Project1” in A5”. This will copy itself to each sheet, so sheet2,3, 4 and 5. This will help the user identify where the file stand. And it’slife cycle process.

Only ColumnA will be copied to each sheets. The other cells in that row will be copied tothe report section. But all informationfrom the other cells must be linked with “Project1”.

Extra: I amtrying to figure out a code that will transfer my comments to sheet5’s commentcell vs “Project1”.

This is thehard part to explain. Each sheet has acomment column. So if the user adds acomment to sheet1 it will be transferred to sheet5’s comment. Always staying onthe same row of “Project1”.

If the useradds a comment in Sheet2, it will copy and adds itself to Sheet5’s comment butafter the comment of sheet1. So on and so on. But always in a flow startingfrom sheet1 to sheet4.

Example:
Sheet1= Row(Project1) Column (Thecomment)= Hello
Sheet2= Row(Project1) Column (Thecomment)=How are you
Sheet3= Row(Project1) Column (Thecomment)=Fine you
Sheet4=Row(Project1) Column (Thecomment)=Good
Sheet5= Row(Project1) Column (Thecomment)=Hello. How are you. Fine you. Good.



Not all projects have the sametimeline. Some project could take month even year to start. Some will take onlyone day.

But all projects must be its own rowand must be able to stay its own row.

If there is 10 projects in Sheet1 that are pending and 5 on contracts, when you continue one that is pending itneeds to be always link to his file number.

Tinbendr
05-20-2012, 01:34 PM
OK, I'm to the testing stage, but I've run out of time.

Give it a whirl and report.

VNouBA
05-21-2012, 02:22 PM
Not at my workstation but will make more explanation tomorrow.

I cannot thank you enough for helping me.

So far so good but I don't quiet understand the Column A (requisition number) vs Column F (Comments).

Will report back tomorrow :D

VNouBA
05-22-2012, 05:19 AM
I will explain the functionality of one project life cycle. I use one project and Row 5 as an example. (Sheet2 will be posted shortly)


Sheet1

A5 = File number; Needs to be copied and paste (permanently) to Sheet2, 3, 4 and 5 on the next available row. This could also be done on the “yes” function at column G. Therefore if “yes” is selected, then copy and paste (permanently) to Sheet2, 3, 4 and 5 on the next available row.

*The look for next available row should start looking in row5 then look if there is a next available row.

So column A would be copied to Sheet2, 3, 4 and 5 on selecting “yes” from the drop down menu.

B5 = The date of the file received; Needs to be copied to sheet5 on the same row of the file number. B5=Sheet5 B5

C5 = Value; Needs to be copied to sheet5 on the same row of the file number. C5 =Sheet5 C5.

D5 = Information; Needs to be copied to sheet5 on the same row of the file number. D5 =Sheet5 D5.

E5 = Information; Needs to be copied to sheet5 on the same row of the file number. E5 =Sheet5 E5.

F5 = Comments; Needs to be copied to sheet5 on the same row of the file number. D5 =Sheet5 P5.

G5 = If “yes” is selected then clear content; or; copy A5 Sheet2, 3, 4 and 5 on the next available row *The look for next available row should start looking in row5 then look if there is a next available row.

VNouBA
05-22-2012, 05:25 AM
Sheet2

A5 = Equals the information transferred from Sheet1. (Already transferred)

B5; C5; D5 = Calculation only this information will not be transferred.

E5; F5; G5 = Date; Needs to be copied to sheet5 on the same row of the file number. E5, F5, G5 will be copied to F5, G5, H5.

H5 = Comments; Needs to be copied to sheet5 on the same row of the file number in the comments. H5 =Sheet5 P5 but after the information from sheet1’s comments.


Column I will clear the content from that row.

VNouBA
05-22-2012, 05:31 AM
Sheet3

A5 = Equals the information transferred from Sheet1. (Already transferred)

B5 = Information; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 I5

C5 = Comments; Needs to be copied to sheet5 on the same row of the file number in the comments. C5 = Sheet5 P5 but after the information from sheet1 and sheet2’s comments.



Sheet4 is complicated and longer to explain therefore could take up more time to explain.

VNouBA
05-22-2012, 06:01 AM
Sheet4 Functionality Example of one contract only.

A5 = Equals the information transferred from Sheet1. (Already transferred)

B5 = Number of contracts; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 J5.

C5 = Contracts number; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 K5

D5 = Information; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 K5

H5 = Number of contracts; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 M5.

I5 = Contracts number; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 N5

J5 = Information; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 L5

M5 = Comments; Needs to be copied to sheet5 on the same row of the file number in the comments. M5 = Sheet5 P5 but after the information from sheet1, sheet2 and sheet3’s comments.

K5 and L5 will be incorporated in later stage.

Column N will clear the content from that row.

VNouBA
05-22-2012, 06:07 AM
Question:

Would it be easier if Column A would be copied to sheet5 only but also transferred to sheet2 first?

Example:

Sheet1
A5 = If column G is selected "yes" then Copy to sheet2 and Sheet5's next available row.

Sheet2 = If column I is selected "yes" then Copy A5 to sheet3 next available row.

Sheet3 = If column D is selected "yes" then Copy A5 to sheet4 next available row.

VNouBA
05-22-2012, 06:34 AM
Would this make sense for Sheet1?



If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count = 1 Then ' stops the code looping
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Copy
Sheets("MERX").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Copy
Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Range(Cells(Target.Row, 1), Cells(Target.Row, 7)).ClearContents
End If
End If
End If



**Sheet2 would be more complicated cause it would need to lookup sheet5's file number information before being transferred.

VNouBA
05-23-2012, 06:42 AM
Should have more code today.
Trying a new approach.

VNouBA
05-23-2012, 06:54 AM
Here is another code from you that I manage to change a little but is still missing a lookup from Sheet5 "Report"




If Not Intersect(Target, Range("I:I")) Is Nothing Then
If Target.Cells.Count = 1 Then ' stops the code looping
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Copy
Sheets("Evaluation").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("E" & TCol & ":G" & TCol).Copy
Sheets("Report").Range("F" & TCol).PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Range(Cells(Target.Row, 1), Cells(Target.Row, 9)).ClearContents
End If
End If
End If


When it the transfer is done, it needs to look-up A to make sure that it goes vs the same project number. is there a way to do this? then I should be ok for awhile.

VNouBA
05-23-2012, 07:16 AM
Example of the transfer:

Sheet “MERX”
A5 = Test
E5, F5 and G5 = Dates
If I5 has “yes” then Copy A5 to next sheet
Else
Copy E5, F5 and G5 then paste but lookup for “Test” in Column A in Sheet “Report” then paste E5, F5 and G5 to F*G*H*

Sheet “Report”

A5 = Run test
A6 = Tryout
A7 = Test… E5, F5 and G5 from sheet “MERX” will be copied to F7,G7 and H7.

Tinbendr
05-23-2012, 08:38 AM
I must have uploaded the wrong file. I d/l the file at work to look at it and some of the code was 'missing'.:dunno

I'll have another look this evening when I get home.

VNouBA
05-23-2012, 08:42 AM
You are the best...

If i find something that could help I will post it here.

VNouBA
05-23-2012, 12:14 PM
I have the formula that would help with the comments.

Problem is to incorporate it in a VB code...

I have the following for Row 5 only.

Sheet"Report" Cell P5



=CONCATENATE(VLOOKUP(A5,'Pre-Requisition'!A5:F30,6,FALSE)," ",VLOOKUP(A5,MERX!A5:H30,8,FALSE)," ",VLOOKUP(A5,Evaluation!A5:C30,3,FALSE))

Tinbendr
05-24-2012, 04:18 AM
OK, I DID u/l the right one. It was just full of potholes. :whistle:

Give it a test run.

Question. What about multiple contracts on the Contract page? When I enter a number > 1, the code inserts that number of contracts. How will 002 end up on the reports page? Or will it?

Another thing. The comments. If you need the comments in order, but the pages are edited out of order, we may have to have a update button or something to iterate through all the comments and put them in order for the Reports page.

VNouBA
05-24-2012, 05:17 AM
Doing tests right now...

My page "MERX"; Column G stopped working. Will try to fix it.


If Target.Column = 6 Then
Cells(Target.Row, "G").FormulaR1C1 = "=IF(RC6="""","""",RC6+90)"
End If


When I get to Sheet "Evaluation" and add a comment, it returns an error.


Question about the contract sheet. Each contract has it own row therefore becomes it's own but always attached to the Project number. When a delivery is completed it will go in the "Archives". I could always do a sort macro afterwards but for now it is not important.


By the way... Good work :D

I did also a couple of tweaks I will post the workbook this afternoon for you to look at when and if you have time. I appreciate your time.

Tinbendr
05-24-2012, 05:59 AM
Question about the contract sheet. Each contract has it own row therefore becomes it's own but always attached to the Project number.That may pose a problem since there will be multiple project numbers with different contract numbers. How will we match up which line to move to the Reports? :dunno

Tinbendr
05-24-2012, 06:07 AM
My page "MERX"; Column G stopped working. Will try to fix it.
When I get to Sheet "Evaluation" and add a comment, it returns an error.
Just tried both of these, but no problems.
If you've had to start/stop with a break, make sure you rerun without any to see if it's ok.

VNouBA
05-24-2012, 06:13 AM
I am trying to fix the original code from Sheet1 to the new code I have arrange and also to incorporate your code...



TCol = Target.Row
If TCol > 4 Or TCol < 19 Then

If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count = 1 Then ' stops the code looping
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Copy
Sheets("MERX").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Copy
Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Range(Cells(Target.Row, 1), Cells(Target.Row, 7)).ClearContents
End If
End If
End If


With this


If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count = 1 Then ' stops the code looping
If LCase(Target.Value) = "yes" Then
'Returns the row number of the same Project number on Reports Sheet.
RptProjRowNum = Application.WorksheetFunction.Match( _
ActiveSheet.Range("A" & TRow).Value, _
Worksheets("Report").Range("A5:A30"), 0) + 4

'Add Comments to report
Worksheets("Report").Range("P" & RptProjRowNum).Value = _
Worksheets("Report").Range("P" & RptProjRowNum).Value & "-" & _
Range("F" & TRow).Value
Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).ClearContents
End If
End If
End If
Application.EnableEvents = True
End Sub


So that when I hit G "yes" it will only tranfer the file number to the next sheet and "Report" and also transfer the other relevant info to the "Report" sheet.

Tinbendr
05-24-2012, 06:22 AM
I and also transfer the other relevant info to the "Report" sheet.You're already doing that eariler in the code.

If Target.Address = "$B$" & TRow Then
If Sheets("Report").Range("B" & TRow) = "" Then
Sheets("Report").Range("B" & TRow) = Target.Value
Else
Sheets("Report").Cells(Last(1, Sheets("Report").UsedRange), "B") = Target.Value
End If
Etc...

Do you NOT want to copy it until Complete?

VNouBA
05-24-2012, 06:29 AM
I guess it would be easier if Sheet1 would be transfer if complete yes.

This would elliminate the copy and pasting from other cells from sheet1


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.EnableEvents = False
If Not Intersect(Target, Range("B5:B100")) Is Nothing Then
frmCalendar.Show
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim TCol As Long
TCol = Target.Row
If TCol > 4 Or TCol < 19 Then

If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count = 1 Then ' stops the code looping
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Copy
Sheets("MERX").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Copy
Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Range(Cells(Target.Row, 1), Cells(Target.Row, 7)).ClearContents
End If
End If
End If
End Sub


I guess this way it's more clean and you do not get confusion with the other sheets if you see the file number somewhere else.

If we transfer projects to sheet5 when it's complete then it's more of a flow with the projects.

Some will always stay on sheet1 for a long period of time. So this will help the flow form other files also?

What do you think?

I didn't think this was going to be a head rush HAHAHA but you are mind blowing me with your help

VNouBA
05-24-2012, 06:35 AM
This code elliminates the cell by cell copy and paste… it takes the full row from column 1 to 6 then tranfer (copy) to sheet5 but also takes the Sheet1 project number and tranfer it to Sheet2 to continue the process.

So no need for the:


If Target.Address = "$B$" & TRow Then
If Sheets("Report").Range("B" & TRow) = "" Then
Sheets("Report").Range("B" & TRow) = Target.Value
Else
Sheets("Report").Cells(Last(1, Sheets("Report").UsedRange), "B") = Target.Value
End If
End If
If Target.Address = "$C$" & TRow Then
If Sheets("Report").Range("C" & TRow) = "" Then
Sheets("Report").Range("C" & TRow) = Target.Value
Else
Sheets("Report").Cells(Last(1, Sheets("Report").UsedRange), "C") = Target.Value
End If
End If
If Target.Address = "$D$" & TRow Then
If Sheets("Report").Range("D" & TRow) = "" Then
Sheets("Report").Range("D" & TRow) = Target.Value
Else
Sheets("Report").Cells(Last(1, Sheets("Report").UsedRange), "D") = Target.Value
End If
End If
If Target.Address = "$E$" & TRow Then
If Sheets("Report").Range("E" & TRow) = "" Then
Sheets("Report").Range("E" & TRow) = Target.Value
Else
Sheets("Report").Cells(Last(1, Sheets("Report").UsedRange), "E") = Target.Value
End If
End If
End If



What do you think?

Tinbendr
05-24-2012, 07:04 AM
What do you think?Yes, I agree. That makes the code of transfer from each page upon completion the same.

VNouBA
05-24-2012, 07:27 AM
I'll try to incoprorate the "comment" code in the "Pre-Requisition" sheet.

Now everything will change lol.

I'll start working on the Sheet2 code... this will maybe cause some problem as when the "MERX" sheet will transfer information it will go on the next available row and not vs the projects number on sheet5 "Report".

VNouBA
05-24-2012, 09:37 AM
Completed the code for Sheet "Pre-Requisition"


Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim TRow As Long
Dim RptProjRowNum As Long
TRow = Target.Row
If TRow > 4 Or TRow < 19 Then

If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count = 1 Then ' stops the code looping
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Copy
Sheets("MERX").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Copy
Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
If LCase(Target.Value) = "yes" Then

'Returns the row number of the same Project number on Reports Sheet.
RptProjRowNum = Application.WorksheetFunction.Match( _
ActiveSheet.Range("A" & TRow).Value, _
Worksheets("Report").Range("A5:A1000"), 0) + 4

'Add Comments to report
Worksheets("Report").Range("P" & RptProjRowNum).Value = _
Worksheets("Report").Range("P" & RptProjRowNum).Value & "-" & _
Range("F" & TRow).Value


End If
Range(Cells(Target.Row, 1), Cells(Target.Row, 7)).ClearContents
End If
End If
End If
End If
End Sub

VNouBA
05-25-2012, 09:53 AM
Like you said... I'm stuck at Sheet4 "Contracts"

I have modified the workbook as per my manager's request but I think I managed to clean it up :D

If you dont mind having a look...

VNouBA
05-25-2012, 12:06 PM
I think I have it and the Workbook will be completed...

I just dont know where to change or modify the last information...

The information is in the "Contracts"

If you need explanation let me know but it's only missing the transfer to the right row vs the contract number. I have modified the Vlookup information.

Instead of taking it from the Main project number it will take the information from the Contract Number...

I have attached my latest Workbook

VNouBA
05-25-2012, 12:08 PM
That may pose a problem since there will be multiple project numbers with different contract numbers. How will we match up which line to move to the Reports? :dunno

From the "Number of contract(s)" instead of the "Requisition Number"

: pray2:

Once the contract is ready to be issued we do not need the Comments from the previous steps... Trying to figure out a code to clear the "comment" in Sheet "Report" when we add information in Column B in the "Contracts" Sheet.

Tinbendr
05-25-2012, 07:38 PM
How about this approach? When you insert the rows for the number of contracts, also copy the same contract number to the Reports page. (Just like you were doing with the project number.) This way, you will have a way to look up the contract when copying the rest of the information from the Contracts upon completion.

In regards to the comments. If I understand you correctly, once the contracts are completed, all the previous comments from the other pages on the Reports are now obsolete? And they need to be replaced with the comments from the Contracts line to the Reports? If so, then

Worksheets("Report").Range("R" & RptProjRowNum).Value = _
Range("M" & TRow).Value

VNouBA
05-28-2012, 10:23 AM
Yes, once the user will be on the "Contract" sheet, the Comment will become useless as all contracts will be ready.

It would be nice to have a code that when the user will add a number in Cell B (Example 003) that the "comment" column clears the content and the user will be able to insert a new comments.

The way I modified the "lookup" the code will now search for the contract number and not the requisition number...

...

One more thing:

I don't quiet get why the formulas in the previous pages do not clear when the "yes" function is activated?

If you do the test on Sheet1 sheet2, the formula stays in the cell? But i looked at my functions, and it clears the content from columns A to the column that has the "yes" inserted in.

what am I missing?

Tinbendr
05-28-2012, 11:06 AM
It would be nice to have a code that when the user will add a number in Cell B (Example 003) that the "comment" column clears the content and the user will be able to insert a new comments. Can you do this when the number of contracts are entered? Would the process be far enough along to remove the comments?


I don't quiet get why the formulas in the previous pages do not clear when the "yes" function is activated?
Hmm. don't know. I can't replicate this.

I'm using Application.EnableEvents = False at the beginning of the Sub, and True at the end to prevent the recursive calling of the Change Event until the code has finished changing everything.

VNouBA
05-29-2012, 09:17 AM
Can you do this when the number of contracts are entered? Would the process be far enough along to remove the comments?

Yes the process of the comments from sheet 1,2 and 3 will be for managements only. They will be able to see where is the files are and why. Once the contracts has been awarded, then, the comments will become useless. When the contracts are awarded the comments are user to add comments for the deliveries. Each contracts will have it's own comments if needed.

So to delete the previous comments from adding information from Column B would be perfect cause now the file number will become "Kind of" useless.

If "B" equal number/information then also Clear Column "R" from Sheet5 "Report" of that same row. But to keep the code from the main function. If "B" equal number/information then add rows...

Then with the upper code I modified from your "Lookup" code... It would add the comment from "M" sheet4 to the "Report" sheet in Column "R"

VNouBA
05-30-2012, 09:43 AM
What we could do is the following but my transfer code in my "Contracts" sheet is not working anymore. I will also add my latest Workbook.

We keep the transfer of the comments. When the user is to the point of the contracts issuing, then the comment in the “Report” could be deleted and info can be inserted on top of it.

The problem I am having is the transferring of the information from the “Contracts” sheet going on the same row of the project number on the “Report” sheet so that column “B” from the “Contracts” sheet copies and paste automatically the info on the “Report” sheet in Column “J”

I’m trying to play around with the codes but nothing… :(

I changed the Vlookup to look for the contract number instead of the project number. Plus I have some offset in my "Report" sheet that will automatically add the original file number to the other added rows in Column "A"

Let me know if you could help

Tinbendr
05-30-2012, 03:33 PM
Just time for a quick look.

A couple/three things.

1. You have declared TCol, but you haven't defined it.
TCol = Target.Column
2. You're testing these values against TCol AND TRow? See #3.
If TCol > 4 Or TCol < 19 Then
If TRow > 4 Or TRow < 19 Then

3. And about half way down, you testing it again, even though you're inside the test already.
If TCol > 4 Or TCol < 19 Then
'If yes is inserted in Column N the following will...
If Not Intersect(Target, Range("N:N")) Is Nothing Then

VNouBA
06-01-2012, 08:53 AM
I think this is the last tweeks:

When doing the following in "MERX" the code doesn't clear. Dont know why...


If Target.Column = 1 Then
Cells(Target.Row, "D").FormulaR1C1 = "=IF(RC3="""","""",RC3+90)"
End If


When:


Range(Cells(Target.Row, 1), Cells(Target.Row, 6)).ClearContents


Column D doesn't clear, the code still stays...

After this everything looks A ok Even the "Contracts" sheet

VNouBA
06-01-2012, 09:23 AM
Another little thing would be on the "Contracts" sheet.

If info is in row 5, 6 and 8, therefore information is cleared on 7, when I select yes from the "Evaluation" sheet the info will add itself to Row 9...

Same thing happends on Sheet "Contracts" going to "Report"

Should be Next available row from row 5...

Actually it does this too all the sheets

VNouBA
06-05-2012, 07:58 AM
I think this workbook is almost completed...
I have cleaned up each sheets.

I need to know one more little detail:

In the "Contracts" sheet... where would I add


Application.EnableEvents = False

and

Application.EnableEvents = True


To not have it search or loop when I insert 001 in Column "B"

When 002 or higher is inserted it doesnt "Stall" but 001 will kinda glitch before inserting the info...

Tinbendr
06-05-2012, 05:30 PM
In the "Contracts" sheet... where would I add
Application.EnableEvents = False
Just after the Dims
Application.EnableEvents = True
Just before the Exit Sub.

BTW, if you're working with the code and stop it before the True, you'll have to enter the True in the immediate window to get the exit routine working again.


To not have it search or loop when I insert 001 in Column "B" NumRows = Target.Value - 1
For R = 1 To NumRows
Target.Offset(1, 0).EntireRow.Insert
I could not replicate this. This code is preventing that because when NumRows is 0, the loop never runs.



When 002 or higher is inserted it doesnt "Stall" but 001 will kinda glitch before inserting the info...You're probably seeing the screen update. It may look bad, but it's shouldn't be a problem.