using a "found" value to define a new range

Question

I am trying to create a dynamic macro in VBA by which I can select a new range starting on a column with the title "Position Number". So my "program" ideally goes to the sheet, finds the range where I want to find this cell that says "Position Number", finds it, gives me the column number (as the column might change) and then it starts on that column to mark a new range and compare it with another sheet. I am so far stuck in the part where I am trying to use the column number I have found to define the new range. I have tried lots of things I found online, but cant fix it.

The error is on:

Set Range1 = Range("'C'& ColNum" & "R1")

I tried a few other variants of this but it does not work or gives me a number as output.

Thanks in advance!

Dim FilledRange As Range
Dim Range1 As Range
Dim Rng As Range
Dim ColNum As String

Worksheets("FILLED Today").Activate
Set FilledRange = Range("a1")
FilledRange.CurrentRegion.Select

Selection.Find(What:="Position Number", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

ColNum = ActiveCell.Column
MsgBox (ColNum)

Set Range1 = Range("'C'& ColNum" & "R1")
MsgBox (Range1)

Scott Craner · Accepted Answer · 2017-08-04T17:24:52.607

1

Use Cells:

Set Range1 = Cells(1,Colnum)

One should avoid .Select and .Activate:

Dim FilledRange As Range
Dim Range1 As Range
Dim Rng As Range
Dim findrng As Range
Dim ColNum As Long

With Worksheets("FILLED Today")
    Set FilledRange = .Range("A1").CurrentRegion

    Set findrng = FilledRange.Find(What:="Position Number", After:=.Range("A1"), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)

    If Not findrng Is Nothing Then ' test to ensure it was found.
        ColNum = findrng.Column
        MsgBox ColNum

        Set Range1 = .Cells(1, ColNum)
        MsgBox Range1
    Else
        MsgBox "String not found in Range"
    End If
End With

edited Aug 04 '17 at 17:24

answered Aug 04 '17 at 17:02

Scott Craner

148,073
10
49
81

Thanks Scott - I got an 1004 error and it marked in yellow this area Set Range1 = .Cells(1, ColNum) – PVL Aug 04 '17 at 17:18
@PVL if this worked for you please consider marking as correct by clicking the check mark by the answer. – Scott Craner Aug 04 '17 at 18:30
Scott - thanks! It worked. I love learning and today I decided to join stackoverflow. So you were my first advisor. Cheers! – PVL Aug 04 '17 at 18:36
one additional question - how would the formula you provided me with would work if I want to create a range where Range 3 is Dim as a Range, and ColNum, ColNum2 and LastRow are all Dim as Long. After some tries and research, the last thing I went for is copied below, but gives an error "Select method of Range class failed" Range3 = .Range(.Cells(1, ColNum), .Cells(LastRow, ColNum2)) – PVL Aug 04 '17 at 21:22
You need to `Set` range objects `Set Range3 = .Range(.Cells(1, ColNum), .Cells(LastRow, ColNum2)) ` – Scott Craner Aug 04 '17 at 21:24
Thanks Scott - I think maybe then the mistake is on the way I am trying to select it...this is what I am using: With Worksheets("FILLED Today") Set Range3 = .Range(.Cells(1, ColNum), .Cells(LastRow, ColNum2)) Range3.Select End With I really appreciate the help. I did try for 30 minutes before asking the question. Thanks! – PVL Aug 04 '17 at 21:30
@ScottCraner - I noticed you said "One should avoid .Select and .Activate:" I use these quite often, as some of my scripts don't want to work without them, or the results are on another sheet that I need to view. Since I'm old and a novice at best, what method should I be using. I noticed in you answer you used "With Worksheets()", which I assume does everything without changing. Thanks for any guidance you can provide. **Mitch** – Mitch Aug 05 '17 at 15:12
@mamalmgren there are very few, *if any*, times that one would **need** to use `.Select` or `.Activate`. See [HERE](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for better guidance. Using `.Select` and `.Activate` dirties the code and makes it run slower. The fewer times one refers to Excel in vba the better. That is why using Arrays instead of continual referencing cells on a sheet is quicker. Every reference to an object on Excel will slow the code, so keep them minimal. – Scott Craner Aug 05 '17 at 17:22
Scott thanks for sharing the link - I worked a bit on using range functions instead of .select and I re-wrote all of my code...still working on it but looking better. Cheers! – PVL Aug 07 '17 at 21:01

using a "found" value to define a new range

1 Answers1