VBA Application or object defined error

noobmaster69

New Member
Joined
May 24, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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").
VBA Code:
Sheets("sche").Range("a1").Select
You would need to select/activate the sheet first, then the range, i.e.
VBA Code:
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:
VBA Code:
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.
 
Upvote 0
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
VBA Code:
Cells(row, col)
 
Upvote 0
Solution
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!
 
Upvote 0
Very easy to get hooked into one potential problem & not notice others. We've all been there. :(
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top