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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.