CODEWITHSUNDEEP
excelvbastringrange
codeLearner
codeLearner

Reputation: 86

Convert a string so it can be used as a range

I would like to convert the string to a range.

Dim operation As Integer
operation = 3
Dim contaminant As Integer
contaminant = 3
Dim countL2 As Integer 'Initial row
countL2 = 13
Dim countC2 As Integer 'Initial column
countC2 = 3

Dim maxC As String
maxC = (countC2+operation) & (countL2) & ":" & (countC2+operation) & (countL2 + contaminant - 1)
Dim maxRange As Range
Set maxRange = sh2.Range(maxC)

It is not working. I guess it is because it is not converting the string to Range.

Upvotes: 1

Views: 687

Answers (1)

YowE3K
YowE3K

Reputation: 23974

If you are trying to specify a range using a string, you can't specify the columns as a number (unless you use R1C1 notation). The string you are generating equates to "613:615" which makes no sense to Excel.

Use the following

Set maxRange = sh2.Range(sh2.Cells(countL2, countC2 + operation), _
                         sh2.Cells(countL2 + contaminant - 1, countC2 + operation))

Upvotes: 1

Related Questions