VBA without Activate

[AMAS]

Hi,

Can this be done without having to Activate each sheet? I have two sheets "Database" and "RoB".

    For x = 2 To LastRow
        If Range("A" & x) <> Range("A" & x).Offset(-1, 0) Then
            If (Application.CountIf(Range("A:A"), Range("A" & x)) - Application.CountIfs(Range("A:A"), Range("A" & x), Range("Q:Q"), "No RoB")) = 0 Then
              Sheets("Database").Activate
                With Sheets("Database")
                    .Columns("A:A").Find(What:=Worksheets("RoB").Range("A" & x), After:=Range("A1"), _
                        LookAt:=xlValue, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 13).Activate
                End With
                    ActiveCell.Value = "No RoB"
                Sheets("RoB").Activate
            End If
        End If
    Next x

AMAS

 

[Scott Huish]

Yes and your code could also probably be simplified. What are you trying to do exactly?

 

[AMAS]

In Sheets("Database") I have unique reference IDs in Column A. In Sheets("RoB") I have multiple entries for each reference ID. If all the entries for the same ID were marked as "No RoB" then I wanted the label for this ID in Sheets("Database") to reflect this.

To achieve this I first count the number of times the ID appears in RoB sheet and the number of times it is labelled as "No RoB". If they are equal then I go back to the Database sheet and mark this in Column N.

It runs fine but I don't like the fact that I activating the sheets back and forth.

AMAS

 

[Scott Huish]

OK, I would need more info than you have given to improve on your code.

But put something like this at the top of your code:

Dim c As Range

Get rid of the .Activate on your Find and change to this:

Set c= .Columns("A:A").Find(What:=Worksheets("RoB").Range("A" & x), After:=Range("A1"), _
                        LookAt:=xlValue, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 13)

Change the Activecell.value line to and put it in the With

If Not c Is Nothing Then c.Value = "No RoB

Remove both Activate lines and qualify any range references with the sheet names if necessary.

 

[Norie]

AMAS

You can start by removing the 2 lines that Activate.

Then add worksheet references for all the ranges throughout the code.

For example:

If Sheets("Database").Range("A" & x) <> Sheets("Database").Range("A" & x).Offset(-1, 0) Then

Writing out Sheets("Database") or whatever each time might be a bit tedious so you could add references for the worksheets:

Set wsDB = Sheets("Database")
Set wsRoB = Sheets("RoB")

It's not going to cut things down a whole lot, especially for the 'RoB' sheet, but it's a good idea to do this anyway.

You might also want to get rid of ActiveCell and also the way Find is used/works.

Here's how the code might look with those changes.

Dim wsDB As Worksheet
Dim wsRoB As Worksheet
Dim rngFnd As Range
 
    Set wsDB = Worksheets("Database")

    Set wsRoB = Worksheets("RoB")
 
    For X = 2 To LastRow

        If wsDB.Range("A" & X) <> wsDB.Range("A" & X).Offset(-1, 0) Then
 
            If (Application.CountIf(wsDB.Range("A:A"), wsDB.Range("A" & X)) - Application.CountIfs(wsDB.Range("A:A"), wsDB.Range("A" & X), wsDB.Range("Q:Q"), "No RoB")) = 0 Then

                Set rngFnd = wsDB.Columns("A:A").Find(What:=wsRoB.Range("A" & X), After:=wsDB.Range("A1"), _
                                                      LookAt:=xlValue, SearchOrder:=xlByRows, SearchDirection:=xlNext)
 
                If Not rngFnd Is Nothing Then
                    rngFnd.Offset(0, 13).Value = "No RoB"
                End If

            End If
        End If
    Next X
    
End Sub

I might have some of that the wrong way round, it's kind of hard to follow code without worksheet references.

PS Have you checked if this could be done with a formula?

 

[AMAS]

Thanks. It works now with the unnecessary back and forth activation of the sheets. I didn't know how to set up the find as a range so thanks for showing me.

AMAS

 

[lacklogic]

Using the below code, I am having trouble with the Set statement lines of code when I delete (or comment out) the lines of code that handles "activating sheets"

(everything works perfect when I activate the necessary sheet first, using code like in the below example)

example: Worksheets("Sheet1").Activate

Is the SET statement dependent upon the target range existing on an sheet that is currently active?

I am trying to do-away with the code that activates the sheet, but right now the "below code" does not work with out first activating the sheet where the range (that is the target of the set statement) is located. Any help here would be much appreciated!!! Thx!

 For i = 6 To iLast
    
        Worksheets("Follow-Ups").Activate
        IDCode = Worksheets("Follow-Ups").Cells(i, "AD").Value
        If IDCode = 0 Or IDCode = "" Then GoTo FStage2:
        PasteArrA = Worksheets("Follow-Ups").Range(Cells(i, "B"), Cells(i, "H"))
        PasteArrB = Worksheets("Follow-Ups").Range(Cells(i, "J"), Cells(i, "AC"))
        
        Worksheets("All").Activate

        IDmatch = Application.Match(IDCode, Worksheets("All").Range("AC:AC"), 0)
               
                     
        Set PasteRangeA = Worksheets("ALL").Range(Cells(IDmatch, "A"), Cells(IDmatch, "G"))
                     
            PasteRangeA.Value = PasteArrA

        Set PasteRangeB = Worksheets("ALL").Range(Cells(IDmatch, "I"), Cells(IDmatch, "AB"))
                       
            PasteRangeB.Value = PasteArrB
        
    Next i

 

[Norie]

You aren't referencing Cells with a worksheet.

Try this.

With Worksheets("ALL")
  Set PasteRangeA = .Range(.Cells(IDmatch, "A"), .Cells(IDmatch, "G"))
End With

Note the little dots . (dot qualifiers) in front of Range and Cells.

They make sure that they refer back to the worksheet in the With.<!-- / message -->

 

[lacklogic]

You aren't referencing Cells with a worksheet.

Try this.

With Worksheets("ALL")
  Set PasteRangeA = .Range(.Cells(IDmatch, "A"), .Cells(IDmatch, "G"))
End With

Note the little dots . (dot qualifiers) in front of Range and Cells.

They make sure that they refer back to the worksheet in the With.<!-- / message -->

Thanks! I knew it was something like that... great catch!

Best Regards, Steve