Referring to worksheet

Question

So I'm having an issue in referring to a worksheet. I intend to open an input box whenever I find an empty cell on column B throughout the workbook, so I can input and change the empty cell value. I am, however, getting an error (First was saying subscription out of range and I changed it, so now it says application/ object defined error) on this line:
For i = 0 To Sheets(j).Cells(Rows.Count, i).End(xlUp).Row

Code:

Dim Country As Variant
Dim Capital As Variant
Dim CapitalValue As Variant
Dim i As Integer
Dim j As Integer

  ' Select *first line of data*
  Range("B1").Select
  ' Loop to stop when an empty cell is reached.
For j = 1 To Worksheets.Count
    j = ActiveSheet.Index

    Range("B1").Select

    For i = 0 To Sheets(j).Cells(Rows.Count, i).End(xlUp).Row
        'Select the Country Cell
        ActiveCell.Offset(i, 0).Select

        CapitalValue = ActiveCell.Value
            'If Country is empty
            If CapitalValue = "" Then
            MsgBox ("No more Capitals")
            Else
            'Input Capital values
            CapitalValue = ActiveCell.Value
            Country = ActiveCell.Offset(0, -1).Value
            Capital = InputBox("Capital of " & Country, "Capital Input")
            CapitalValue = Capital
            End If
    Next i
  Next j

Regards

Answer 1

At the top of your macro, set your sheet to a name like

Dim a as worksheet

Set a = Sheets("yoursheetname")

Then when you want to refer to that specific sheet just use

a.Range("a1").select

Assuming your blank values are in column A, I'd do something like

Sub findBlanks()
    Dim a As Worksheet
    Set a = Sheets("Sheet1")

    For x = 2 To a.Range("a1048576").End(xlUp).Row 'find last row 
        If a.Range("a" & x).Value = "" Then
            MsgBox ("This cell is blank!!!")
        End If
    Next x
End Sub

Answer 2

If you want to repeat this across all worksheets (like the For j = 1 to Worksheets.Count line would suggest) you shouldn't be changing j in the next line to ActiveSheet.Index, especially since your code doesn't actually change worksheets at any point.

Your Range("B1").Select suggests you want to loop over column B looking for these values, so replace your For i = 0 To Sheets(j).Cells(Rows.Count, i).End(xlUp).Row with For i = 1 To Sheets(j).Cells(Sheets(j).Rows.Count, "B").End(xlUp).Row as you need to know where to start with i. I've assumed row 1, but you might need to change that if you have a header row to 2.

You are then selecting the cell i rows below the activecell. For the first time round the loop this will move you from row 2 to row 3. The second time you will jump from 3 to 5 as i has incremented from 1 to 2. Best to avoid Select in general where possible as it slows things down in any case. Since you have noted you want to find blank values then prompt the user for the details, I would suggest the following instead:

For j = 1 to Worksheets.Count
    For i = 1 To Sheets(j).Cells(Sheets(j).Rows.Count, "B").End(xlUp).Row
        If Sheets(j).Range("B" & i).Value = "" Then
            Country = Sheets(j).Range("A" & i).Value
            Sheets(j).Range("B" & i).Value = InputBox("Please enter the capital for country " & Country, "Capital Input")
        End If
    Next
Next