Excel VBA: 'excel vba run-time error 1004' application-defined or object-defined error

Question

I have this line in a module and it keeps spitting up a run-time error 1004 when I try and run it.

Can anyone help?

I'm guessing it's to do with how the Range is referenced, but i'm not sure. This is all new to me.

rngFirst = ThisWorkbook.Worksheets("Still In Progress").Range("G" & 1 & ":G" & lw)

Many thanks in advance

Do you have a sheet called 'Still In Progress'? - and is that 'ell doubleyew' after ":G" &? — mcalex
– mcalex, Commented Aug 13, 2013 at 9:57

Dave · Accepted Answer · 2013-08-13 10:21:19Z

1

This works for me:

Sub Button1_Click()

Dim rngFirst As Range
Dim int1 As Integer

int1 = 2

Set rngFirst = ThisWorkbook.Worksheets("Sheet1").Range("G" & 1 & ":G" & int1)

rngFirst.Select

End Sub

I was getting the same error as you until I used Dim and Set.

edited Aug 13, 2013 at 10:21

answered Aug 13, 2013 at 10:08

Dave

3161 gold badge2 silver badges9 bronze badges

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

Comments

mcalex · Accepted Answer · 2013-08-13 10:15:11Z

1

Your range is not defined correctly. Essentially, you are setting the range to:

"Still In Progress!G1:G1w".

You need to set the last bit of the rngFirst formula to a number (eg at the bottom right hand corner of the range). Something like:

...Range("G" & 1 & ":G" & 20)

if the bottom of your data is at row 20.

answered Aug 13, 2013 at 10:15

mcalex

6,7985 gold badges53 silver badges86 bronze badges

Collectives™ on Stack Overflow

Excel VBA: 'excel vba run-time error 1004' application-defined or object-defined error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related