Pass concatenated string + variables as Range?

[Dimitris254]

I'm having hard time getting this to work:

Dim lastRow As Long
Dim results_range As Range
Dim column_results As String
Dim wsDATA As Worksheet
Set wsDATA = ThisWorkbook.Worksheets("DATA")

With wsDATA

  Set results_range = .Range(column_results & "2:" & column_results & lastRow) 

End With

the point is that i want a flexible code, where i find the columns and then create the range, rather than having " Set results_range = .Range("K2:K30") " (which works fine btw)

the error i get is:

run-time error 1004:

Method 'Range' of object '_Worksheet' failed

 

[RoryA]

Where are you assigning a value to column_results and lastRow?

 

[Fishboy]

I'm having hard time getting this to work:

Dim lastRow As Long
Dim results_range As Range
Dim column_results As String
Dim wsDATA As Worksheet
Set wsDATA = ThisWorkbook.Worksheets("DATA")

With wsDATA

  Set results_range = .Range(column_results & "2:" & column_results & lastRow) 

End With

the point is that i want a flexible code, where i find the columns and then create the range, rather than having " Set results_range = .Range("K2:K30") " (which works fine btw)

the error i get is:

run-time error 1004:

Method 'Range' of object '_Worksheet' failed

Hi Dimitris254,

What is column_results other than a String? Where does it get this information from?

You are also referencing lastRow but do not have it being defined anywhere in your code?

 

[Dimitris254]

both column_results and lastRow are defined, just forgot to put the code in, as imho the problem is in concatenating the range.

Dim lastRow As LongDim column_results As String

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
column_results  = .Cells.Find(What:="results", SearchOrder:=xlByColumns).Column

 

[RoryA]

Column returns a column number, not a letter. That is your problem. So you should be using:

With wsDATA

  Set results_range = .Range(.Cells(2, column_results), .Cells(lastRow, column_results)) 

End With

 

[Dimitris254]

cheers, that fixed it, although now i get errors with the SumIfs WorksheetFunction

SumSheet = Application.WorksheetFunction.sumifs(results_range, _
           test_range, "test A", _
           name_range, "Peter")

the other ranges are defined in the same way inside the With - End With statement. The SumIfs is outside the With - End With (it's the same error if i put it inside):

Error '1004':

Unable to get the SumIfs property of the WorksheetFuction class

 

[Jonmo1]

Can you show ALL the code where each range is created?

Best guess is that they are not all equal demensions (which is required for sumifs)

 

[Dimitris254]

[SOLVED] Re: Pass concatenated string + variables as Range?

i love you

the problem was that lastRow i had was a common code for the last non-empty row ( e.g. lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ), which is something like 30k, while i was testing the sub from row 2 to 30.

the reason i don't usually put the whole code is that most subs i'm working on are lengthy, so i cut the part of the code with the problem. Next time i'll put more effort into it.

thanks all again

 

[Jonmo1]

Re: [SOLVED] Re: Pass concatenated string + variables as Range?

You're welcome.