Assign Range into Arrray and then use array to find text to make new range and seach word

Hi Guys,

Thanks in advance.

I need a code in below sequence.

1) Assign a Dynamic range in "Proposal Data" worksheet to array.

2) using array, find from array value from "Spec Landing" Worksheet and also find text "Stocks and Inks" below that array vale cell to form new range. now from this new range find "Comment: " Text

3) All the Text after "Comments: " should be copy and paste in worksheet "Proposal Data" Column X again that particular Array


Note :- in Worksheet "Spec Landing" data is saved in Unicode Text in A:A column.

Untitled.png

Untitled1.png


i used the below code but failed.

Dim Rng As Range, rRange As Range
Worksheets("Proposal Data").Active
Dim Ram As Worksheet
Dim Sham As Worksheet
Dim LastRow As Integer
Dim vArray As Variant
'Dim WArray As Variant
Dim i As Long

Set Ram = Worksheets("Proposal Data")
Set Sham = Worksheets("Spec Landing")

LastRow = Ram.Cells(Rows.Count, "Q").End(xlUp).Row
vArray = Ram.Range("Q5:A" & LastRow)

For i = LBound(vArray) To UBound(vArray)

FirstRow = sht.Range("A:A").Find(vArray(i), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastRow = sht.Range("A:A").Find(" Stocks and Inks ", After:=FirstRow, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
xan = sht.Range(FirstRow & LastRow).Select

With xan
Tan = Range(xan).Find(What:="Comments:", After:=FirstRow, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'
If Not Tan Is Nothing Then
End If
End With
Next

End Sub

its very urgent. Hope someone can help me?