populate a parameter

[asmenut]

I have a part that I am extracting parameters from an embedded spreadsheet. I am able to extract the multi-value parameters easy enough, but I am having issues trying to extract a single value from the 2nd column of a  2 column range based upon a given parameter value. I have tried using GoExcel.NamedRangeValue and also FindRow and CurrentRowvalue methods, but at best I get an error that it cant find a column name.  Below is the code and I am attaching a copy of the embedded spreadsheet.  Plz help. 

GoExcel.Open("3rd Party:Embedding 2", "Sheet1")

MultiValue.List("BSeries") = GoExcel.CellValues("3rd Party:Embedding 2", "Sheet1", "B4", "")

Series = Parameter("BSeries")

rangeArray = GoExcel.NamedRangeValue("BS_" & BSeries)

Dim oList As ArrayList
oList = New ArrayList

Dim oRows
oRows = rangeArray.GetLength(0)

Dim oRowIndex As Integer

For oRowIndex = 1 To oRows
oList.Add(rangeArray(oRowIndex,1))
Next
MultiValue.List("BStyle")=oList

bn1 = GoExcel.FindRow("3rd Party:Embedding 2", "Sheet1","BELT_STYLE", "=", Parameter("BStyle"))
Parameter("B_Name")= GoExcel.CurrentRowValue("STYLE_NAME")

GoExcel.close

iLogicVb.UpdateWhenDone = True

[asmenut]

Okay, I got it to work (Sort of).  I was able to correct my coding error to allow for the cell value to load into my parameter, but it is not updating as I change the value of my BStyle Parameter.  Apparently the BS_Name parameter is not linking to my rule dynamically.  Any ideas?

GoExcel.Open("3rd Party:Embedding 2", "Sheet1")

MultiValue.List("BSeries") = GoExcel.CellValues("3rd Party:Embedding 2", "Sheet1", "B4", "")

Series = Parameter("BSeries")

rangeArray = GoExcel.NamedRangeValue("BS_" & BSeries)

Dim oList As ArrayList
oList = New ArrayList

Dim oRows
oRows = rangeArray.GetLength(0)

Dim oRowIndex As Integer

For oRowIndex = 3 To oRows
oList.Add(rangeArray(oRowIndex,1))
Next
MultiValue.List("BStyle")=oList

Dim nRow As Integer

For nRow = 4 To 100
If GoExcel.CellValue("o" & nRow) = Parameter("BStyle")
Parameter("BS_Name") = GoExcel.CellValue("P" & nRow)
End If
Next

GoExcel.Save
GoExcel.close

iLogicVb.UpdateWhenDone = True

[chandra.shekar.g · Accepted solution]

@asmenut ,

To work with GoExcel.NamedRangeValue, try suggestions provided in the below link.

https://adndevblog.typepad.com/manufacturing/2013/11/setting-multi-value-parameter-from-excel-named-...

To update "BS_name" parameter dynamically on changing "BStyle" parameter, need to write one more iLogic which is triggered on "Any Model Parameter Change" event.

Thanks and regards,