VBA Error

[mgirvin]

Dear Excel Team,

I am new to VBA. I am reading the Mr Excel VBA book. I have defined a variable:

FinalRow = Cells(65530, 2).End(x1Up).Row

Every time I hit F8 and Execute, I get Error "Run time error 1004, Application-defined or object-defined error". I am pretty sure the problem is with the ".End(x1up)", however, this is how it is typed in the book.

The whole code is this:

'Test FinalRow
'by Gel 8/25/06
Sub TestFinalRow()
FinalRow = Cells(65530, 2).End(x1Up).Row

For i = 2 To FinalRow
If Cells(i, 2).Value > 0 Then
Cells(i, 2).Interior.ColorIndex = 6
End If
Next i

End Sub

Sincerely, Mike Gel Girvin

 

[jindon]

Looks like 1 instead of L

xlUp Not x1Up

 

[HalfAce]

Hello mgirvin,
You're right as to the problem being the bit '.End(x1up)'

It should be .End(xlUp) - as in lower case XL instead of X1

If you change that your code should run fine.

 

[Smitty]

Also note that when Bill wrote that book X1 I2 wasn't on the horizon yet, so if you're doomed to getting it soon, you might want to change:

FinalRow = Cells(65530, 2).End(xlUp).Row

to:

FinalRow = Cells(Rows.Count, 2).End(xlUp).Row

As v.I2 will have more than 65536 rows.

Smitty

(Heya fellas!)

 

[mgirvin]

Dear jindon, HalfAce, pennysaver,

Thanks! The MrExcel Excel community is great. You guys are awesome!

The Rows.Count is cool ==> this is a much better way than Cells(65536,2) or Cells(1048576,2)

I love the "so if you're doomed to getting it soon". What do you mean? I have been using the Beta version and I am not sure that in the short to middle term people will find it more user-friendly. The PivotTable formatting is a bit better, SUMIFS is cool... How about you guys? What do you think?

Sincerely, Mike Gel Girvin

 

[Smitty]

There's already a pretty lively discussion going on about that:

1.1 Million Rows - A Discussion About Excel 12

For me I don't worry so much about folks who know how to use Excel; it's the ones who don't who are going to cause their IS departments lots of problems by thinking Access can replace a true relational database, like Access.

I've seen people fubar a wb with only 15,000 rows of data, let alone 1.1 million!

It's kind of like my analogy of new cars: the smarter they get, the dumber drivers get.

Smitty

 

[mgirvin]

Dear pennysaver,

64 levels of IF? Realy?

What does wb's and FUBAR'd mean?

Sincerely, Mike Gel Girvin

 

[Smitty]

64 levels of IF? Realy?

What does wb's and FUBAR'd mean?

I believe that's referring to 64 levels of Conditional Formatting. Currently there are only 3 (4 if you can fake it).

WB refers to Workbook. It's what anyone else would call a "file". An Excel Workbook (wb) is made up of Worksheets (ws, sheets, etc.).

FUBAR goes back to WWII and essentially stands for "Fouled Up Beyond All Recognition", although it's somewhat different nowadays. .

See: What is FUBAR?

Another WWII addage is SNAFU. "Situation Normal All Fouled Up". The focus on both F words being the "F" word.

Smitty

 

[HalfAce]

For what it's worth, (which admittadly ain't much since I haven't even checked
out the new version yet) I understand it is capable of both 64 conditional
format conditions and IF formulas containing up to 64 nested IFs.

As weak as I am with formulas to begin with, it seems I'll be roughly 9 times as weak
at them in the future.

 

[mgirvin]

Dear Excel Team,

I just tried 60 nested IFs in the Beta Version and it worked! I have occasionally exceeded the 7 nested function limit and had to use concatenation (&). So this is a welcome improvement!

Woo Hoo!, for the new Excel 12!

Sincerely, Mike Gel Girvin