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?

 

[par60056]

Are you sure that is a letter O in the formula and not a Zero?

 

[Jonmo1]

Try
SearchTermsStr = "F" & Row & ":O & Row
SET SearchTermsRng = Range(SearchTermsStr)

 

[dversloot1]

Turns out the 'O' was a zero (embarrassing) . I had changed it but it still didn't work.

I took SearchTermsRng out and simply used SearchTermsStr as the range and that worked fine.

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


'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 & ":O" & Row
Sheets("Data").Select
Range(SearchTermsStr).Copy
Sheets("InputORAdjustNewProduct").Select
Range("B9:K9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


End Sub

 

[njimack]

Welcome to the board.

You don't need to select ranges or sheets to perform actions on them. Your entire final section of code could be written as:

Sheets("InputORAdjustNewProduct").Range("B9:K9").Value = Sheets("Data").Range(Cells(Row, 6), Cells(Row, 15)).Value

 

[dversloot1]

Well how about that!?

Thanks Neil. I will definitely use that method rather than my lengthy one.

 

[dversloot1]

If the fields on my data tab are both text and values, how would I alter your suggestion?

 

[njimack]

If the fields on my data tab are both text and values, how would I alter your suggestion?

Why would that make a difference? The code I posted is the equivalent of the below, without making use of the clipboard:
Range("Range1").Copy
Range("Range2").PasteSpecial xlValues

 

[par60056]

it doesn't change a thing. Value is a property that means "What is in the cell"

 

[dversloot1]

Why would that make a difference? The code I posted is the equivalent of the below, without making use of the clipboard:
Range("Range1").Copy
Range("Range2").PasteSpecial xlValues

I replaced my code with yours...It didn't work for some reason, that is why I was wondering if I needed to specify.

Private Sub CommandButton2_Click()
Dim ID As Long
Dim Row As Long
Dim Cnt As Long


'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


Sheets("InputORAdjustNewProduct").Range("B9:K9").Value = Sheets("Data").Range(Cells(Row, 6), Cells(Row, 15)).Value

End Sub