VBA Application or object defined error

[noobmaster69]

I have created the following code to extract information from an excel table. But I am getting an error exactly at the if statement. I have even tried executing the code from a module and even from the worksheet level. I have read about this issue and it seems that selecting the sheet seems to be the main problem, but I have also tried but in vain I can't seem to find a solution. It would be really great if someone could help me with this. Thank you in advance.

Sub test()
Dim row As Double, col As Double, inc As Double
row = 2
col = 2
inc = 20
'Sheets("sche").Range("a1").Select
For row = 2 To 15
For col = 2 To 52
If (Cells(r, c).Font.Bold Or Left(Cells(r, c).Value, 2) = "BP") Then 'Error is happening here
Sheets("sche").Cells(inc, 2).Value = Sheets("sche").Cells(r, c).Value
inc = inc + 1
GoTo zone
Else: GoTo zone
End If
zone:
Next col
Next row
End sub

 

[Joe4]

Welcome to the Board!

If you are not already on the "sche" sheet when the macro is called, you cannot select a cell on it like this:
Sheets("sche").

Sheets("sche").Range("a1").Select

You would need to select/activate the sheet first, then the range, i.e.

Sheets("sche").Activate
Range("a1").Select

However, if you are just wanting to check the value of the value or formatting in that cell, doing that is unnecessary.
You can just do:

If (Sheets("sche").Cells(r, c).Font.Bold Or Left(Sheets("sche").Cells(r, c).Value, 2) = "BP") Then

Also, though it does not really hurt anything, other than use up unnecessary computer memory, it is advised to use "Long" instead of "Double" when declaring row and column variables in your VBA code, as "Double" allows decimals, and you cannot have decimal row or column numbers. They must be integers/whole numbers.

 

[Fluff]

Along with Joe's comments you are using Cells(r, c) but at no stage have to assigned a value to either r or c.
I suspect that it should be

Cells(row, col)

 

[Joe4]

Along with Joe's comments you are using Cells(r, c) but at no stage have to assigned a value to either r or c.

Good catch, Fluff!
Not sure how I missed that!

 

[Fluff]

Very easy to get hooked into one potential problem & not notice others. We've all been there.

 

[Joe4]

Another tip for the OP.

You shouldn't use reserved words like "row" as the name of variables, procedures, or functions.
It can cause ambiguity, errors, and unexpected results.
Reserved words are words that are already used by Excel/VBA in functions, properties, methods, etc.

So I would definitely recommend using "r" instead of "row" for your variable.