It's pretty difficult to go wrong if you fully qualify your ranges.

Even using the activesheet:

with activesheet
.range("a1").clearcontents
end with

for example.



Omar wrote:

Dear Mr. Peterson,

I appreciate your help, it works just fine.

I didn't know that when a button is defined in a worksheet, all the button's
click events are related to that worksheet.

Thank you.

Happy holidays,

Omar.

"Dave Peterson" wrote:

This looks like it could be the _click event for a commandbutton placed on a
worksheet.

If that's the case, then any unqualified ranges refer to the worksheet that owns
the code--not the activesheet.

So when you do this kind of stuff:

Worksheets(2).Select
Select Case valor
.....
Case 8
celda = "H" & renglon
Range(celda).Select
Range(celda).Value = 1
valor = CInt(Range(celda).Value)

range(celda) will refer to the worksheet with the button (and code). It won't
refer to worksheets(2)--the now active worksheet.

And selecting this range won't work, since worksheets(2) is now active. (You
can only select a cell on an activesheet).

You could do this:

Worksheets(2).Select
Select Case valor
.....
Case 8
celda = "H" & renglon
worksheets(2).Range(celda).Select
worksheets(2).Range(celda).Value = 1
valor = CInt(worksheets(2).Range(celda).Value)

but even better would be to drop the .selects

'don't do this: Worksheets(2).Select
Select Case valor
.....
Case 8
celda = "H" & renglon
with worksheets(2)
'no need to do this: Range(celda).Select
.Range(celda).Value = 1
valor = CInt(.Range(celda).Value)
....

Note the dots in front of the range objects. That means that that
object/property belongs to the object in the previous "with" statement--in this
case, that's Worksheets(2).



Omar wrote:

Hi Mr. Keramidas,

The whole (complete?) code is:

Private Sub btnNext_Click()
'''''''''''''''''''''''''''''''''''
' Declaración de variables
'''''''''''''''''''''''''''''''''''
Dim valor, renglon, valorCelda As Integer
Dim vacio As Boolean
Dim celda As String
'''''''''''''''''''''''''''''''''''
' Asignación de valores
'''''''''''''''''''''''''''''''''''
renglon = 3
vacio = False
celda = "IF" & renglon
'''''''''''''''''''''''''''''''''''
' Determinar renglón disponible
'''''''''''''''''''''''''''''''''''
Worksheets(2).Select
Do While vacio = False
'Range(celda).Select
valorCelda = CInt(Range(celda).Value)
If valorCelda = 0 Then
vacio = True
Else
renglon = renglon + 1
End If
Loop
'''''''''''''''''''''''''''''''''''
' 1.1
'''''''''''''''''''''''''''''''''''
Worksheets(1).Select
valor = CInt(Range("E16").Value)
Worksheets(2).Select
Select Case valor
Case 1
celda = "A" & renglon
Range(celda).Value = 1
...

"renglon" in spanish means row, so I declared this variable to find an empty
row where the data must be placed. It's initiated with value 3 because the 2
firsts rows have some information.

Than you.

"Gary Keramidas" wrote:

i don't see anywhere where renglon is defined, it is empty. it needs to be a
row number


--


Gary


"Omar" wrote in message
...
Hi,

I've got the next code:

Worksheets(1).Select
valor = CInt(Range("E16").Value)
Worksheets(2).Select
Select Case valor
Case 1
celda = "A" & renglon
Range(celda).Value = 1
Case 2
celda = "B" & renglon
Range(celda).Value = 1
Case 3
celda = "C" & renglon
Range(celda).Value = 1
Case 4
celda = "D" & renglon
Range(celda).Value = 1
Case 5
celda = "E" & renglon
Range(celda).Value = 1
Case 6
celda = "F" & renglon
Range(celda).Value = 1
Case 7
celda = "G" & renglon
Range(celda).Value = 1
Case 8
celda = "H" & renglon
Range(celda).Select
Range(celda).Value = 1
valor = CInt(Range(celda).Value)
Case 9
celda = "I" & renglon
Range(celda).Value = 1
Case 10
celda = "J" & renglon
Range(celda).Value = 1
End Select

I've been executing it with "valor" as 8; however, it always returns the
1004 error: an application error. (Amazing, isn't it?!!!)

By the way, it's supposed that "Range(celda).Value = 1" writes, stores,
-whatever- "1" in the cell, right? Well, guess what? It doesn't work. Which
is the correct sentence to do that?

Thanks in advance.

Omar.




--

Dave Peterson


--

Dave Peterson