Help Needed to make code lookup target value on another sheet, then right an offset value back to a cell offset from the original target.

[Upex]

Hi All,

Yet another fine dilema I find myself in.... to much screen time I think.

So anyhow, I need my code to (when the target cell is changed - any in row 19) lookup the target value in 'HistoricLog'!a3:a103 and then return the value that is on the matched (same) row, but in column F. I then need it to take this 'found' value and use it to populate the cell that is offset(12,0) from the original target cell on the first sheet.

(e.g. - sheet1 B19 = "test". Look up and match "test" in HistoricLog!A3:a103 (found in A5 say). Offset to F5 (still on HistoricLog) which value is 80%. Return to Sheet1 B19, offset to B31 and input the value 80%.)

I have the following:

  Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SkipIt    
If Target.Count > 8 Then Exit Sub
  If Not Application.Intersect(Target, Rows("19:19")) Is Nothing Then
        If Len(Target.Value) = 0 Then
            Application.EnableEvents = False
            Target.Value = "Select Planned Course"
            Application.EnableEvents = True
        End If
        If Target.Value <> "Select Planned Course" Then
        HistoricEOS = Target.Offset(12, 0).Value
        Temp = Application.Match(Target.Value, Sheets("HistoricLog").Range("A3:A103"), 0)
        HistoricEOS.Value = Sheets("HistoricLog").Range("F" & Temp).Value
        End If
    End If
SkipIt:
End Sub

The default for the cells in 19:19 is "Select Planned Course" - hense the bit of code to re-set them to this, should the contents be cleared.

Anyone able top offer so assistance, as I'm stuck as to why it wont work. I feel maybe the

If Target.Value <> "Select Planned Course" Then

doesnt really work, or I'm way off and should take up gardening instead of this kinda thing!

Thanks in advance, hopefully. Upex.

Forgot to say, I only want it to fire up whan a cell in 19:19 is changed, and only function for the entry in that column.

Thanks.

 

[Upex]

I have formula at present that works fine:
=IF(B$19="Select Planned Course","",VLOOKUP(B$19,Historic_Info,6,0))
But I need it to be a one-off write of the info. The formula refreshes, so when new data is added the old entries (formulas change). I need it to show the figure as it was of time of entry (and not update) hense the need for the macro.

Anyone get any ideas? I've been fettling it for an hour and only gone backwards lol.

Thanks, Upex.