CODEWITHSUNDEEP
excelvba
rockatheman1
rockatheman1

Reputation: 107

Code only works when on the Active worksheet

So I'm trying to use the following code to fill in the blanks on a worksheet. It fills in the blanks based on the first value above the blank row.

The code works fine when I'm on the specific sheet. However when I try to the code from a different sheet it doesn't work. It will leave certain rows empty and gives the following error: "no cells were found"

Which is weird because it doesn't return any errors when I'm on the specific sheet.

     Sub Sample()
     
      Dim wsOutput As Worksheet
      Set wsOutput = ThisWorkbook.Sheets("Output")
      
      With wsOutput
            With .Range("A2:F" & Range("G" & Rows.Count).End(xlUp).Row)
                 .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
                 .Value = .Value
            End With
        End With
        
     End Sub

Upvotes: 0

Views: 172

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

I suggest that you fully qualify your objects

Sub Sample()
     
    Dim wsOutput As Worksheet
    Set wsOutput = ThisWorkbook.Sheets("Output")
    
    With wsOutput.Range("A2:F" & wsOutput.Range("G" & wsOutput.Rows.Count).End(xlUp).Row)
        .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
       
End Sub

Upvotes: 1

Related Questions