How can I pass the string value inside the Range as a reference?

Question

I have one Excel Datasheet which consists of details about a particular product. Here I have written a VB script which pinpoints to a particular product using the unique product name which I have assigned for all the individual products. When I run the below code:

Private Sub CommandButton23_Click()
Dim rngX As Range
Dim num As String

Set rngX = Worksheets("Sheet1").Range("A1:A10000").Find("CTPT",     lookat:=xlPart)
num = rngX.Address
Range("$A$13:B" & Range("B2").End(xlDown).Row).Copy
Range("F1").PasteSpecial (xlPasteValues)

End Sub

Here in the above code I am getting where the Product "CTPT" is located, the address of the cell location is stored in "num". How can I assign the "num" cell address $A$13 in the range function so that I can select all the parameters related to that particular product and it will get copied and pasted in F1 cell.

score 2 · Accepted Answer · answered Nov 23 '15 at 03:34

You are correct in that you will start with the rngX that was located with the Range.Find method, but you do not need to resolve that to a Range.Address property that is subsequently used to reference a Range object. Using rngX as a Range.Object can generate the starting cell and rngX.Row can help get the lower left cell of the Range.Copy method.

Private Sub CommandButton23_Click()
    Dim rngX As Range
    Dim num As String

    Set rngX = Worksheets("Sheet1").Range("A1:A10000").Find("CTPT", lookat:=xlPart)
    Range(rngX, Range("B" & rngX.Row).End(xlDown)).Copy
    Range("F1").PasteSpecial (xlPasteValues)
End Sub

Thanks for the answer Jeeped! – Karthik P B Nov 23 '15 at 03:38 — Karthik P B, Nov 23 '15 at 03:38

David Zemens · Answer 2 · 2015-11-23T03:35:59.623

0

Like this?

Range(num & ":B" & Range("B2").End(xlDown).Row).Copy

Or, without the intermediary num variable:

Range(rngX.Address & ":B" & Range("B2").End(xlDown).Row).Copy

I would advise that Range("B2").End(xlDown) may not be ideal, it is particularly unreliable when working on data with non-contiguous areas as in your screenshot.

edited Nov 23 '15 at 03:35

answered Nov 23 '15 at 03:31

David Zemens

53,033
11
81
130

Thanks a ton David! it worked!! Since i am new to the VBA i was struggling with the above problem. Thanks Once again, have a good day ahead! – Karthik P B Nov 23 '15 at 03:35
I'm curious why `Range("B2").End(xlDown).Row` doesn't stop at row 11. – Nov 23 '15 at 03:36
@Jeeped I would expect it to stop at 12 (if not sooner, some rows are hidden in the screenshot), hence my caveat that method is "unreliable" at best. If all OP needs is columns A and B, then I would just do `rngX.Resize(1,2)`, but if OP actually needs "last row", then there are other more reliable approaches: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – David Zemens Nov 23 '15 at 03:38

How can I pass the string value inside the Range as a reference?

2 Answers2