Link events to new block

[Rudi]

Hi,

Please see the attached workbook for details and question...
TX

Test Form.xlsm

[HansV]

You can't rely on fixed cells, nor on named ranges that refer to fixed cells, such as BMark and PType.
Going by the current setup, the role of PType is played by B10, B27, B44, ...
So you could change the line

Code: Select all

    If Not Intersect(Range("PType"), Target) Is Nothing Then

to

Code: Select all

    If Target.Row Mod 17 = 10 And Target.Column = 2 Then

and similar for others. But is it really worth the trouble? It's probably going to be a lot of work to get it correct...

[Rudi]

It is a bit more trouble than what it's worth.
I'll have to chat with the requester and see if I can approach this part of the form differently.

TX for the guidance.

[Rudi]

OK...taking new approach...but now I have a simple issue I cannot seem to resolve???

Please see attached...

[HansV]

You're mixing A1-style and R1C1-style references.
Target.Offset(8).Address returns an A1-style address.
valLists!R2C3:R10C9 is an R1C1-style address.
You could use either R1C1:

Code: Select all

        Target.Offset(9).FormulaR1C1 = "=IFERROR(VLOOKUP(" & _
            Target.Offset(8).Address(ReferenceStyle:=xlR1C1) & _
            ",valLists!R2C3:R10C9,3,0),"""")"

or A1:

Code: Select all

        Target.Offset(9).Formula = "=IFERROR(VLOOKUP(" & _
            Target.Offset(8).Address & ",valLists!C2:I10,3,0),"""")"

and similar for the others.

[Rudi]

Many TX.
This one got me good; not even coffee helped me resolve it!!!!

[HansV]

Perhaps you should change the declaration

Dim sngEspresso As Single

to

Dim dblEspresso As Double

[Rudi]