PDA

View Full Version : Solved: Range Selection error



pepe90
07-08-2011, 09:11 AM
Hi, this is probably a very stupid problem, but I'm new on VBA so it's getting me in a lot of trouble. The program shows me an error defined by the application or the object in the followinf line:

ActiveSheet.Range(Cells(10, 9), Cells(9 + IFINAL, 9)).Select

The problem is not on the IFINAL varaibles, i've changed 9+IFINAL for a number and didn't worked.This code is in a worksheet module. Any idea of why is this error appearing will be gratefully received.

Kenneth Hobs
07-08-2011, 10:32 AM
Welcome to the forum!

I suspect that your error is 1004. This can mean that you have defined a row that does not exist. You can test using this scenario:
Sub t()
Dim IFINAL As Long, r As Range
Set r = Range(Cells(10, "I"), Cells(9 + IFINAL, "I"))
MsgBox r.Address

IFINAL = -9
If 9 + IFINAL >= 1 Then
Set r = Range(Cells(10, "I"), Cells(9 + IFINAL, "I"))
MsgBox r.Address
Else: MsgBox 9 + IFINAL, , "oops"
End If

r.Select
End Sub

pepe90
07-08-2011, 12:12 PM
Thank you for your answer Kenneth,
In fact, the error I get is 1004, but I don't think it is caused by a wrong definition of the row, because actually I changed the line for:

ActiveSheet.Range(Cells(10, 9), Cells(24, 9)).Select

and still get the same error message.

Kenneth Hobs
07-08-2011, 12:39 PM
If you are working in Excel then you have something else going on as your snippet works. Is the reference to the Microsoft Office 14.0 Object Library set in VBE's Tools > References? I have heard of some that had to reinstall MSOffice to fix that sort of thing. Of course that should be your last solution.

CatDaddy
07-08-2011, 12:48 PM
Avtiveworkbook.Sheets(whatever).Activate
Range("A1").Activate
ActiveSheet.Range(Cells(10, 9), Cells(9 + IFINAL, 9)).Select

pepe90
07-08-2011, 01:24 PM
Kenneth and CatDaddy,
Thank you very much for yor help, but it seems there is a strange issue in my code. Using the lines CatDaddy suggested I got this error message on the second line "Error on the Activate method of the Range class".
The reference Microsoft Office 11.0 Object Library is set, maybe I should get a newer version of MS Office.

Thanks again

CatDaddy
07-08-2011, 03:32 PM
can we see how it fits in the rest of your code?

pepe90
07-09-2011, 04:23 PM
Sure, but I have to wait until monday to have access to it.

pepe90
07-11-2011, 07:53 AM
Checking the code, I dont' really see significant lines previous to the problematic ones, they're pretty independent. Here is the code that I think could matters:

ActiveWorkbook.Sheets(REF).Activate
ActiveSheet.Range(Cells(10, 9), Cells(9 + IFINAL, 9)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="INICIO!Q1:Q43"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "Nombre de máquina inválido"
.ErrorMessage = "Ingrese el nombre corto correspondiente a la máquina"
.ShowError = True
End With

Aflatoon
07-11-2011, 08:09 AM
I'd guess your code is in a worksheet module other than the active sheet. It would be better to use:


With ActiveSheet
.Range(.Cells(10, 9), .Cells(9 + IFINAL, 9)).Select
End With

pepe90
07-11-2011, 08:20 AM
It worked! Thank you all very much. Do I have to mark the thread as solved or something like that? Points??

Aflatoon
07-11-2011, 08:29 AM
Not as far as I know, no.

CatDaddy
07-11-2011, 10:26 AM
yes mark solved, no points :)