Converting vba string to a range

[dversloot1]

I'm currently trying to copy a dynamic range based on the row number of a selected listbox item. I'm able to get the row number of the selected listbox item from the data tab in my file. I, however, am stuck when it comes to copying a fixed column / varying row range from this tab and copying it over to another tab.

Private Sub CommandButton2_Click()
Dim ID As Long
Dim Row As Long
Dim Cnt As Long
Dim SearchTermsStr As String
Dim SearchTermsRng As Range


'Get Row Number'
Cnt = InStr(1, ListBox1.Value, "-")
ID = Left(ListBox1.Value, Cnt - 1)
On Error Resume Next
Row = Application.WorksheetFunction.Match(ID, Sheets("Data").Range("B1:B200"), 0)
On Error GoTo 0


SearchTermsStr = "F" & Row & ":0" & Row
SearchTermsRng = Range(SearchTermsStr)
Sheets("Data").Select
Range(SearchTermsRng).Copy
Sheets("InputORAdjustNewProduct").Select
Range("B9:K9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


End Sub

SearchTermsRng is returing "Nothing" but the SearchTermsStr returns "F3:O3"


Any ideas what I might be doing wrong here?

 

[Jonmo1]

When you use the syntax

Range(Cells(..),Cells(..))
And you specify the SHEET on the RANGE
Sheets("Data").Range(Cells(..),Cells(..))

Then you must also specify the sheet on both the Cells as well
Sheets("Data").Range(Sheets("Data").Cells(..),Sheets("Data").Cells(..))