CODEWITHSUNDEEP
excelvba
PVL
PVL

Reputation: 15

using a "found" value to define a new range

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)

Upvotes: 1

Views: 327

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 1

Related Questions