Simple vba program in Excel

Question

Sub TEST()
    If cells(i, "R").Value <> "UK" Then
        cells(i, "R").Interior.ColorIndex = 3
    End If
End Sub

If I run this program it throws application defined error \

I am new to Excel (beginner)

How to correct this error!!!

Thanks In advance

Add some explanation in your question on what you're trying to do with this code. — jmstoker
– jmstoker, Commented Sep 8, 2013 at 9:35

Bob · Accepted Answer · 2013-09-08 09:58:35Z

2

I think the issue is "R" that I know of the cells method takes 2 parameters one is rows the other is columns (in that order) but this is done by number not letter so if you change it to cell(1,18) then the code above works fine.

This link may also be useful to learn more, among other things it describes how you would normally select a range first as I believe your code above will assume the currently selected page, however you might want to run in on a button click from another page or as soon as the spreadsheet opens.

http://msdn.microsoft.com/en-us/library/office/ff196273.aspx

answered Sep 8, 2013 at 9:58

Bob

3031 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2013-09-08 12:11:03Z

2

The problem is that the variable i has not been assigned a value. VBA assumes that it is zero. Since i is used to determine the row of the cell, Excel throws an exception because there is no row 0!

edited Sep 8, 2013 at 12:11

answered Sep 8, 2013 at 11:02

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Mahdi Jazini · Accepted Answer · 2013-09-08 12:08:21Z

1

First you have to define i variable

for example: Dim i as variant

answered Sep 8, 2013 at 12:08

Mahdi Jazini

81112 silver badges17 bronze badges

1 Comment

barrowc Over a year ago

If step 1 is declaring the variable i then step 0 is using Option Explicit to require variable declaration

Collectives™ on Stack Overflow

Simple vba program in Excel

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related