Assign a cell a value from another column

[wtom0412]

Hi again, I really can't figure this one out either.

I have a data set (C8:K522).
Each row represents one record.
Each row is sequentially numbered 1 - 514
Column D is "Relates To"
Column G is "Start Date", and
Column H is "Due Date".

Example of what I would like to achieve...

There is Data in row 17 (H17) that has a due date of 1 January, 2016.

A user adds new data into row 25, but types 17 into D25.

I would like a UserForm to pop up when somebody enters a value into a cell in column D that asks the user how many days to add to the due date. It would then take the date value from H17, add the number of days as per the UserForm, and populate G25.

Similarly, this would work in the same way if the user typed a number from any row that contained text (IE not empty)..

Again, I hope this makes sense, and I hope that someone can help.

I have done a sceeen shot of the sheet below if that helps.

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	#N/A
2	Insert Project Name Here										Line Colour
3					Select a name from above then Click HERE to email a PDF or click HERE to view a PDF
4
5	Project Manager:
6	Project Coordinator:
7
8	LINE COLOUR	FOLLOW AFTER LINE #	LINE NUMBER	DELIVERABLES	OWNER	START DATE	DUE DATE	COMPLETED DATE	STATUS	DAYS							New Start Date
9			1	Coffee Machines organised for Delivery to Amberley	AH	Tue 5 Apr	Tue 5 Apr		98 Days Overdue	1
10			2	Finalise Menu Options	AH	Wed 23 Mar	Wed 6 Apr		97 Days Overdue	14
11			3	Discuss with Army setup resources (stocking and setup of container)	AH	Wed 23 Mar	Wed 6 Apr		97 Days Overdue	14
12		2	4	Meet with 2FSB to discuss planning for planning and Hamil	AH	Wed 6 Apr	Wed 6 Apr		97 Days Overdue	1							24/03/2016
13			5	Get Microwave Tagged and Tested	AH	Wed 23 Mar	Thu 7 Apr		96 Days Overdue	15
Data

Cell Formulas
Range	Formula
B1	=VLOOKUP(F1,List!$A$2:$C$35,3,FALSE)
B5	="Project Manager: "
B6	="Project Coordinator: "
J9	=IFERROR(IF(OR(G9="",H9=""),"",IF(I9<>"","Complete",IF($A$2-H9=0,"Due Today",IF(H9>$A$2,"",IF($A$2-H9 = 1,$A$2-H9&" Day Overdue",IF($A$2-H9 > 1,$A$2-H9&" Days Overdue")))))),"")
J10	=IFERROR(IF(OR(G10="",H10=""),"",IF(I10<>"","Complete",IF($A$2-H10=0,"Due Today",IF(H10>$A$2,"",IF($A$2-H10 = 1,$A$2-H10&" Day Overdue",IF($A$2-H10 > 1,$A$2-H10&" Days Overdue")))))),"")
J11	=IFERROR(IF(OR(G11="",H11=""),"",IF(I11<>"","Complete",IF($A$2-H11=0,"Due Today",IF(H11>$A$2,"",IF($A$2-H11 = 1,$A$2-H11&" Day Overdue",IF($A$2-H11 > 1,$A$2-H11&" Days Overdue")))))),"")
J12	=IFERROR(IF(OR(G12="",H12=""),"",IF(I12<>"","Complete",IF($A$2-H12=0,"Due Today",IF(H12>$A$2,"",IF($A$2-H12 = 1,$A$2-H12&" Day Overdue",IF($A$2-H12 > 1,$A$2-H12&" Days Overdue")))))),"")
J13	=IFERROR(IF(OR(G13="",H13=""),"",IF(I13<>"","Complete",IF($A$2-H13=0,"Due Today",IF(H13>$A$2,"",IF($A$2-H13 = 1,$A$2-H13&" Day Overdue",IF($A$2-H13 > 1,$A$2-H13&" Days Overdue")))))),"")
K9	=IFERROR(IF(AND(G9="",H9=""),"",IF(H9-G9=0,"1",H9-G9)),"")
K10	=IFERROR(IF(AND(G10="",H10=""),"",IF(H10-G10=0,"1",H10-G10)),"")
K11	=IFERROR(IF(AND(G11="",H11=""),"",IF(H11-G11=0,"1",H11-G11)),"")
K12	=IFERROR(IF(AND(G12="",H12=""),"",IF(H12-G12=0,"1",H12-G12)),"")
K13	=IFERROR(IF(AND(G13="",H13=""),"",IF(H13-G13=0,"1",H13-G13)),"")
D9	=IF(E9="","","1")
D10	=IF(E10="","",D9+1)
D11	=IF(E11="","",D10+1)
D12	=IF(E12="","",D11+1)
D13	=IF(E13="","",D12+1)
R9	=IFERROR(VLOOKUP(C9,$D$8:$G$522,4,FALSE)+1,"")
R10	=IFERROR(VLOOKUP(C10,$D$8:$G$522,4,FALSE)+1,"")
R11	=IFERROR(VLOOKUP(C11,$D$8:$G$522,4,FALSE)+1,"")
R12	=IFERROR(VLOOKUP(C12,$D$8:$G$522,4,FALSE)+1,"")
R13	=IFERROR(VLOOKUP(C13,$D$8:$G$522,4,FALSE)+1,"")

Cheers

WT

 

[wtom0412]

Hi All,

I figured out how I would do this, and thought I might share...

Sub Lookup_TAB()

Dim ws As Worksheet
    Dim LastRow As Long
    Dim TargetRange As Range
    Dim LookupValue As Long
    Dim RngStart As Range


    On Error GoTo MyErrorHandler:


    Set ws = Sheets("Sheet1")
    Set RngStart = ActiveCell.Offset(0, -1) ' Assumes the user hit TAB and Sets the ActiveCell to the Left so it can be used as the Referance in the Lookup
     
RngStart.Select


    LastRow = ws.Cells(Rows.Count, "L").End(xlUp).Row
    Set TargetRange = ws.Range("C9:X" & LastRow)


    UserInput = InputBox("How many Days would you like to add?")


    ActiveCell.Offset(0, 4) = Application.WorksheetFunction.VLookup(ActiveCell.Value, TargetRange, 6, False) + UserInput
    
MyErrorHandler:
    If Err.Number = 1004 Then
    
    RngStart.Select
    MsgBox ("There is no Line " & RngStart.Value & " in the Range.")
    
    End If






End Sub

Happy to hear from anyone if they have suggestions on how this might be improved!!

Cheers, WT