| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Easy Question | Excel Discussion (Misc queries) | |||
| An easy question... Maybe? | Excel Programming | |||
| new user with easy question? not easy for me | New Users to Excel | |||
| Easy VBA question: range control | Excel Programming | |||
| Easy Question | Excel Programming | |||
Linear Mode
