Required excel formula or vba code to get updating dynamic values to the fixed text ranges

[Sathish G]

hi all,

i need some experts help to get updating dynamic values to the fixed specific text ranges.

Note: Reference text ranges wont changed that is in sheet1, Only dynamic values will get changes all the time that is in sheet2.

 

[ExceLoki]

you could try something like this:

Book1
	A	B	C	D
1	<section type="site" name="	"/>	THISNAME	<section type="site" name="THISNAME"/>
Sheet2

Cell Formulas
Range	Formula
D1	D1	=CONCAT(A1,C1,B1)

 

[Sathish G]

you could try something like this:

Book1
	A	B	C	D
1	<section type="site" name="	"/>	THISNAME	<section type="site" name="THISNAME"/>
Sheet2

Cell Formulas
Range	Formula
D1	D1	=CONCAT(A1,C1,B1)

Thanks but am looking for code to find "<Section Type="site" Name="">" in entire column A then if text found insert the dynamic value

 

[kvsrinivasamurthy]

Try

Sub InsertValue()
Dim Sh1 As Worksheet, Sh2 As Worksheet, k$
Dim LR1&, LR2&, Ta&, Tb&, T&
Dim Frng As Range
Application.ScreenUpdating = False
Set Sh1 = Sheets("Sheet1"): Set Sh2 = Sheets("Sheet2")
LR1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
ReDim A(2 To LR2, 1 To 2)
For Tb = 2 To LR2
A(Tb, 1) = Sh2.Range("A" & Tb)
A(Tb, 2) = Left(A(Tb, 1), Len(A(Tb, 1)) - 2) & Sh2.Range("B" & Tb) & Right(A(Tb, 1), 2)
Next Tb
    With Sh1
    For Ta = 2 To LR1
    k = .Range("A" & Ta)
        For Tb = 2 To LR2
        If k = A(Tb, 1) Then .Range("A" & Ta) = A(Tb, 2)
        Next Tb
    k = ""
    Next Ta
    End With
Application.ScreenUpdating = True
End Sub

 

[ExceLoki]

or as a formula:

Book2
	A
1	<?xml version="1.0" encoding="UTF-8"?>
2	<Section type="site" Name="dynamic value">
3	<Chapter Name="Node Parameters">
4	<Chapter Name="Project settings">
5	<Parameter Name="ProjectID" Role"PM" value="dynamic value"/>
6	<Parameter Name="EditableMode" Role="PM" Value="dynamic value"/>
Sheet1

----------------

Book2
	A	B	C
1	Refrence test fixed	dynamic value	result
2	<Section type="site" Name="dynamic value">	5GEA1102	<Section type="site" Name="5GEA1102">
3	<Parameter Name="ProjectID" Role"PM" value="dynamic value"/>	AAY-HIS-DFFH-681232	<Parameter Name="ProjectID" Role"PM" value="dynamic value"/>
4	<Parameter Name="EditableMode" Role="PM" Value="dynamic value"/>	Activate	<Parameter Name="EditableMode" Role="PM" Value="dynamic value"/>
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=IFNA(IF(MATCH(A2,Sheet1!A:A,0)>0,SUBSTITUTE(A2,B1,B2),""),"")

 

[Sathish G]

hi Srini,

Thanks a lot it working for me.

But small modification code required

For example in line 5 value imported as ""AAY-HIS-DFFH-681232/> instead of "AAY-HIS-DFFH-681232"/>

""> --- This condition importing fine
""/> --- This condition correction required.

<?xml version="1.0" encoding="UTF-8"?>

<Section Type="site" Name="5GEA1102">

<Chapter Name="Node parameters">

<Chapter Name="Project settings">

<Parameter Name="ProjectID" Role="PM" Value=""AAY-HIS-DFFH-681232/>

<Parameter Name="TPDEditableMode" Role="PM" Value="Activate">

 

[Sathish G]

or as a formula:

Book2
	A
1	<?xml version="1.0" encoding="UTF-8"?>
2	<Section type="site" Name="dynamic value">
3	<Chapter Name="Node Parameters">
4	<Chapter Name="Project settings">
5	<Parameter Name="ProjectID" Role"PM" value="dynamic value"/>
6	<Parameter Name="EditableMode" Role="PM" Value="dynamic value"/>
Sheet1

----------------

Book2
	A	B	C
1	Refrence test fixed	dynamic value	result
2	<Section type="site" Name="dynamic value">	5GEA1102	<Section type="site" Name="5GEA1102">
3	<Parameter Name="ProjectID" Role"PM" value="dynamic value"/>	AAY-HIS-DFFH-681232	<Parameter Name="ProjectID" Role"PM" value="dynamic value"/>
4	<Parameter Name="EditableMode" Role="PM" Value="dynamic value"/>	Activate	<Parameter Name="EditableMode" Role="PM" Value="dynamic value"/>
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=IFNA(IF(MATCH(A2,Sheet1!A:A,0)>0,SUBSTITUTE(A2,B1,B2),""),"")

Thanks a lot for your efforts but actually am looking vba code to update everything automatically.

 

[kvsrinivasamurthy]

Try

Sub InsertValue()
Dim Sh1 As Worksheet, Sh2 As Worksheet, k$
Dim LR1&, LR2&, Ta&, Tb&, T&, m&
Dim Frng As Range
Application.ScreenUpdating = False
Set Sh1 = Sheets("Sheet1"): Set Sh2 = Sheets("Sheet2")
LR1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
ReDim A(2 To LR2, 1 To 2)
For Tb = 2 To LR2
A(Tb, 1) = Sh2.Range("A" & Tb)
If Right(A(T, 1), 2) = "/>" Then m = 3 Else m = 2
A(Tb, 2) = Left(A(Tb, 1), Len(A(Tb, 1)) - m) & Sh2.Range("B" & Tb) & Right(A(Tb, 1), m)
Next Tb
    With Sh1
    For Ta = 2 To LR1
    k = .Range("A" & Ta)
        For Tb = 2 To LR2
        If k = A(Tb, 1) Then .Range("A" & Ta) = A(Tb, 2)
        Next Tb
    k = ""
    Next Ta
    End With
Application.ScreenUpdating = True
End Sub

 

[Sathish G]

script out of range on this line

If Right(A(T, 1), 2) = "/>" Then m = 3 Else m = 2

 

[Sathish G]

script out of range on this line

If Right(A(T, 1), 2) = "/>" Then m = 3 Else m = 2

Many thanks to you!!!

Script worked fine after made this correction

If Right(A(Tb, 1), 2) = "/>" Then m = 3 Else m = 2