Using OFFSET and MATCH Functions in VBA

[mArK_d]

I hope someone could help me with this problem, I haven't included any of the code I've tried as it's so far off the mark you'd probable fall down laughing.

I have a sheet (DDE Sheet) which will contain a table of DDE links. The
table always starts from D7. The number of Columns may go as far as AZ and
the number of rows my go to 107.

The DDE links look like this

=the962|IQVALUE!'L13O011G5(Dv)'

where [G5] is the variable for the columns and [L13O011] is the variable
for the rows. (I already have code for finding and replacing the row
variable).

DDE Sheet AutoSave Master.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1			O
2		UnitReference			Status
3		L	O0		Enable		HTCO1Fault		HTCO2Fault		VAV%
4		Lan	OS	Identifier
5
6					S	2	S	3
7					=the962|IQVALUE!'L14O011S2(V)'		=the962|IQVALUE!'L14O011S3(V)'
8	L11O011	11	11	#REF!
9	L11O012	11	12	#REF!
10	L11O013	11	13	#REF!
11	L11O014	11	14	#REF!
12	L11O015	11	15	#REF!
13	L11O016	11	16	#REF!
DDE Sheet 2

The code for this sheet is currently<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
frmOptions.Show<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> CommandButton4_Click()
frmNetwork.Show<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)
  
  <SPAN style="color:#00007F">Dim</SPAN> myRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> myRng<SPAN style="color:#00007F">As</SPAN> Range
    
  <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
  myRow = Target.Row

  <SPAN style="color:#00007F">If</SPAN> Application.Intersect(Range("b7:B150"), Target)<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>

    <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
  <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
  
  

  <SPAN style="color:#00007F">Set</SPAN> myRng = Me.Range(Cells(myRow, "D"), Cells(myRow, "AT"))

  
  Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
  myRng.Replace what:="L??O???", _
     Replacement:=Me.Cells(Target.Row, "a").Value, _
     LookAt:=xlPart, _
     SearchOrder:=xlByRows, _
     MatchCase:=<SPAN style="color:#00007F">False</SPAN>

  
  Target.Select
  Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN>
  <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

I have another sheet (Link Refs) which contains all the various DDE links as
text strings (not currently active) using the G & 5 as row and column
references ie.

DDE Sheet AutoSave Master.xls
	A	B	C	D
1		(V)'
2	=the962|IQVALUE!'L14O011	S	K	A
3	1	=the962|IQVALUE!'L14O011S1(V)'	=the962|IQVALUE!'L14O011K1(V)'	=the962|IQVALUE!'L14O011A1(V)'
4	2	=the962|IQVALUE!'L14O011S2(V)'	=the962|IQVALUE!'L14O011K2(V)'	=the962|IQVALUE!'L14O011A2(V)'
5	3	=the962|IQVALUE!'L14O011S3(V)'	=the962|IQVALUE!'L14O011K3(V)'	=the962|IQVALUE!'L14O011A3(V)'
6	4	=the962|IQVALUE!'L14O011S4(V)'	=the962|IQVALUE!'L14O011K4(V)'	=the962|IQVALUE!'L14O011A4(V)'
7	5	=the962|IQVALUE!'L14O011S5(V)'	=the962|IQVALUE!'L14O011K5(V)'	=the962|IQVALUE!'L14O011A5(V)'
8	6	=the962|IQVALUE!'L14O011S6(V)'	=the962|IQVALUE!'L14O011K6(V)'	=the962|IQVALUE!'L14O011A6(V)'
9	7	=the962|IQVALUE!'L14O011S7(V)'	=the962|IQVALUE!'L14O011K7(V)'	=the962|IQVALUE!'L14O011A7(V)'
10	8	=the962|IQVALUE!'L14O011S8(V)'	=the962|IQVALUE!'L14O011K8(V)'	=the962|IQVALUE!'L14O011A8(V)'
11	9	=the962|IQVALUE!'L14O011S9(V)'	=the962|IQVALUE!'L14O011K9(V)'	=the962|IQVALUE!'L14O011A9(V)'
Link Refs

What I would like to do is, using the values in E6 and F6 on the DDE Sheet, find the coresponding string in the Link Refs sheet, Copy it then paste special - values to the coreesponding column on the DDE Sheet, in this case E and fill the column from row 7, with the same string down to the last entry in column C.

As the user moves to the next pair of columns, in this case G5 & H6
and enters a value like G and 2 the process is repeated for that pair of columns.

The final process to make these links live is to find and replace the = at
the begining of the string with =, this activates the link. I can probably
figure this bit out but as for the rest I've tried setting some DIM
statements to ranges and then use OFFSET, INDEX, Lookup, MATCH but I don't really know what I'm doing.


Thanks in advance to anyone who responds