Question about RANGE (easy one, or at least it should be)
 

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.

Question about RANGE (easy one, or at least it should be)
 

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

Question about RANGE (easy one, or at least it should be)
 

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