CODEWITHSUNDEEP
vbaexcel
barciewicz
barciewicz

Reputation: 3783

Convert string back to range

In the code below, I am converting range to string in the line:

dCostCenter = Right(dCostCenter, Len(dCostCenter) - 1)

However, I need to use dCostCenter as range again in the line:

Set DetailsTargetCell = forecastMain.Cells(dCostCenter.Row, dMonth.Column)

otherwise I get an "Object required" error.

But is it possible to convert string to range? Or maybe there is another way to solve this?

Public Function SetDetailsTarget(monthNumber As Integer, CCNumber As String)

Call SetWorkbooks
Call SetRanges

For Each dMonth In DetailsMonths 
    For Each dCostCenter In DetailsCostCenters
        If Left(dCostCenter, 1) = "0" And Not dCostCenter Like "*/*" Then
                dCostCenter = Right(dCostCenter, Len(dCostCenter) - 1)'range to string
        End If
        If monthNumber <> "0" Then
            If dMonth = MonthName(monthNumber) And dCostCenter = CCNumber Then
                Set DetailsTargetCell = forecastMain.Cells(dCostCenter.Row, dMonth.Column)' I need string to range here
            End If
        End If
    Next dCostCenter
Next dMonth

Upvotes: 1

Views: 237

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

It is possible to overload a variable, but it is very confusing to do so. Say cell B9 contains abc. Then this code:

Sub OverLoad()
    Dim B9 As Range
    Set B9 = Range("B9")
    B9 = Right(B9, Len(B9) - 1)
    MsgBox B9 & vbCrLf & B9.Address
End Sub

produces:

enter image description here

NOTE:

  • the contents of the cell does get clipped (the a gets removed)
  • the Range variable B9 still exists and can be used as a range
  • the String variable B9 exists and contains the clipped string

I suggest using separate variables instead.

Upvotes: 0

David wyatt
David wyatt

Reputation: 318

Add another variable, like dCostCenterString. Then use this when you convert your range to a string, leaving the original range available to use. Also by not declaring your variables e.g 

dim dCostCenterString as string

You are using variants, which can slow down your code and create errors as the vba has to guess what the variable is.

Upvotes: 1

Related Questions