CODEWITHSUNDEEP
excelvba
luke
luke

Reputation: 482

Find variable and store values

I need to find a text string and store the item names below the text string to put in a different location in the sheet

Example I want to find "Description" and store all the items below it to use later in the macro

enter image description here

And place them in B1 for example

enter image description here

Here's the code im trying to use but I don't know how to store the Active Range

Sub test()

'find description
Cells.Find(What:="Description", After:=ActiveCell, LookIn:=xlValues, _
  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False).Activate


Selection.Offset(1, 0).Select 'Offset after find
Range(Selection, Selection.End(xlDown)).Select 'Selects to end

Dim DescriptionValues As Range
DescriptionValues = Active.Range

ActiveSheet.Range("B10") = DescriptionValues 'put stored text starting in B1

End Sub

Upvotes: 0

Views: 34

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Sub test()
Dim rng As Range

Set rng = ActiveSheet.Cells.Find(What:="Description", After:=ActiveSheet.Range("A1"), LookIn:=xlValues, _
  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False)

If Not rng Is Nothing Then
    Set rng = ActiveSheet.Range(rng.Offset(1, 0), rng.End(xlDown))
    ActiveSheet.Range("B1").Resize(rng.Rows, 1).Value = rng.Value 'put stored text starting in B1
End If



End Sub

Upvotes: 1

Related Questions