Defining ranges in vba : Syntax error help

[kuda25]

Dear experts

I am trying to wirte a could that uses labels in columns to define the start and the end of a range. So far I have the following code. Running into some trouble when I try to define the range , v in the code below. v is meant to be the range define using the start and end indexes found.

Please help, been at this for awhile

Kuda

'Defines the range, based on column labels
Sub findrange()
Dim level As Integer
Dim levels As Integer
Dim levelrange As Variant



levelrange = Worksheets("Sheet1").Range("a4:z4")

'Crange = Sheets("Sheet1").Range("b5:j15")

levels = 4
i = 1

levelstart = i
'Range Start

Worksheets("Sheet1").Range("a4:z4").Select

Selection.Find(What:=levelstart, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
    With Selection
        srow = .Row
        scol = .Column
    End With
    
levelend = i + 1
'range end
Worksheets("Sheet1").Range("a4:z4").Select
Selection.Find(What:=levelend, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
    
    With Selection
        eRow = .Row
        ecol = .Column - 1
    End With
    
    v = "range" & i = ".range(.cells(" & scol & "," & srow+1 & "),.cells(" &scol& ",65000)"
    
End Sub

Tags:
vba

 

[SkipVought]

hi,

In plain words, not code language, explain your sheet structure and WHAT you are trying to accomplish, not HOW you think you need to do it, please.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kuda25]

@SkipVought,

I have a tables divide into n sections in excel. At the top of the table there is an identifier tell which section the column belongs too. The numbers of columns in each section and the number of sections will defer depending on the table. I am need to do different tasks on each of these sections. In the code I am trying to use the code to identify each section and define it's range using the column identifier at the top of each column. The columns for each section are grouped together.

Hope that makes sense.

Kuda

 

[xlbo]

v = range(cells(scol , srow+1 ),cells(scol,65000))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

So do you have ONE section identifier at the beginning of each section or the section identifier in each column of the section?

This sounds to me like it could be a simple spreadsheet solution, as long as the section identifier is in each column. Then a SUMPRODUCT() function might work for you.

Why do you have 65000 as a row limit? There does seem to be a better way.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nodrog77]

Hey Kuda,

The Bit:
'-----------------------------
Dim levelrange As Variant

levelrange = Worksheets("Sheet1").Range("a4:z4")
'-------------------------------

maybe could be replaced by:

Dim LevelRange as Range

set LevelRange = Worksheets("Sheet1").Range("a4:z4")

I'd also start with using 'Option Explicit' up the top of your code so you can see what you are doing/defining a little more easily.

Regards,

Lea