Use match to define range

[Emperor]

Hi all,

I'm trying to create a dynamic range but am failing somehow.

Worksheets("data").Activate
Dim Colmn
Colmn = Application.Match("Eindtotaal", Range("2:2"), False)
 
Dim lastr As Range
Set lastr = ActiveSheet.Cells(2, Colmn)
 
Dim KGrng
KGrng = Worksheets("data").Range("G2:" & lastr)

So what I need is to look in row 2 for "Eindtotaal", and then create a range from G2 to the cell where "Eindtotaal" is found.

What am I doing wrong?

Mathijs.

 

[Andrew Poulsom]

Try removing Set from:

Set lastr = ActiveSheet.Cells(2, Colmn)

The Set keyword is only used for assigning an object to a variable. You need it in this line:

Set KGrng = Worksheets("data").Range("G2:" & lastr)

 

[Jon von der Heyden]

Hi Mathijs

If you are looking for that exact string then I like to use the Find method. See example below:

Sub Demo()
    Dim KGrng As Range
    
    With Sheets("data")
        On Error Resume Next
            Set KGrng = .Range(.Range("G2"), .Rows(2).Find(What:="Eindtotaal", LookAt:=xlWhole, MatchCase:=True))
        On Error GoTo 0
        If KGrng Is Nothing Then
            MsgBox "Eindtotaal not found in row 2", vbExclamation
        Else
            MsgBox "Eindtotaal: " & KGrng.Address
        End If
    End With
End Sub

 

[Emperor]

Andrew and Jon, thanks for the quick replies!

Andrew;
When I remove the set from the first part Excel stops saying it needs a 'with', could this be correct? If yes, where? don't get it.

Jon;
When I use the find statement, Excel states there is now "Eindtotaal" in row 2, well there is...how could this be?
I use this formula to get "Eindtotaal" in row 2;

=IF(I8="Eindtotaal",I8,IF(I8="","",(I8& " "& VLOOKUP(I8,data2!$D$24:$E$56,2,FALSE))))

(formula is in I2)
When I link to row 8, your code does work, but not in row2, very weird, at least for me...

Gr. Mathijs

 

[mirabeau]

What am I doing wrong?

Mathijs.

Did you want to know what's wrong with your own code?
Try it with the indicated modifications

Dim Colmn
Colmn = Application.Match("Eindtotaal", Range("2:2"), False)
 
Dim lastr As Range
Set lastr = ActiveSheet.Cells(2, Colmn)
 
Dim KGrng as Range
Set KGrng = Worksheets("data").Range("G2", lastr)

Although it could usefully do with a bit of tidying up in other ways too.