Converting vba string to a range

dversloot1

Board Regular
Joined
Apr 3, 2013
Messages
113
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Are you sure that is a letter O in the formula and not a Zero?
 
Upvote 0
Try
SearchTermsStr = "F" & Row & ":O & Row
SET SearchTermsRng = Range(SearchTermsStr)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
it doesn't change a thing. Value is a property that means "What is in the cell"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top