CODEWITHSUNDEEP
vbaexcel
Mike Mann
Mike Mann

Reputation: 546

How to activate worksheet using set range variable

How can I activate worksheet using set range variable?

For example:

Dim WorkRng1 as Range

Range.WorkRng1.Activate

This code doesn't work if I'm using range variables between two workbooks.

I'm setting these ranges in different workbooks:

Dim xTitleId As String
xTitleId = "Compare Ranges"
Set WorkRng1 = Application.InputBox("Please Select TASK ID Range in **INVOICE REVIEW FILE**", xTitleId, Type:=8)
Set WorkRng2 = Application.InputBox("Please Select TASK ID Range in **BUDGET GRID**", xTitleId, Type:=8)
Set WorkRng3 = Application.InputBox("Please Select **UNIT COST** Range in Budget Grid", xTitleId, Type:=8)

Call CompareRanges
'Error Handler
Whoa:
        Select Case Err.Number
            Case 1004
                MsgBox "Check Your Column Letters!", vbInformation, "Oops!"
            Case 424
                Exit Sub
        End Select

Then running these loops which happen in different workbooks:

'clears color format
WorkRng2.Interior.ColorIndex = xlNone
'finds duplicate values
For Each Rng1 In WorkRng1
    For Each Rng2 In WorkRng2
        If Rng1.Value = Rng2.Value Then
            Rng2.Interior.Color = VBA.RGB(254, 255, 255)
            Exit For
        End If
    Next
Next
'find unique values and highlights red
For Each Rng2 In WorkRng2
    For Each Rng3 In WorkRng3
        If Rng2.Value > 0 And Cells(Rng2.Row, Rng3.Column) <> 0 And Rng2.Interior.Color <> VBA.RGB(254, 255, 255) Then
            Rng2.Interior.Color = VBA.RGB(255, 0, 0)
            Exit For
        End If
    Next
Next
'prompts to select blank row to copy
    Set blkRow = Application.InputBox("Please select the 'BLANK' with formulas", "BLANK ROW SELECTION", Type:=8)
'****NEED HELP HERE
'finds unit id below unique value in range 1 and inserts blank row

'i is range 1
Dim i As Variant
'q is range 2
Dim q As Variant

For i = WorkRng1.Cells.Count To 1 Step -1
    For q = WorkRng2.Cells.Count To 1 Step -1
        If Cells(q, WorkRng2.Column).Interior.Color = VBA.RGB(255, 0, 0) And Cells(q, WorkRng2.Column).Value > 0 Then
            If Cells(i, WorkRng1.Column).Value = Cells(q, WorkRng2.Column).Offset(1, 0).Value Then
                blkRow.Copy
                Cells(i, WorkRng1.Column).EntireRow.Insert Shift:=xlDown
                    Application.CutCopyMode = False
                Exit For
            End If
        End If
    Next
Next

I need to activate the workbook between the groups of loops.

Upvotes: 0

Views: 573

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

If you have the Range, but not the Worksheet for the range, then you can activate it by using the .Parent property of the range.

Dim rng1 As Range
Set rng1 = Worksheets(2).Range("A2")
rng1.Parent.Activate

But you can easily avoid this issue by properly declaring your Worksheet objects and using these before setting your Range objects.

Upvotes: 2

Related Questions