The Value method of the Range object failed

Question

Good evening, I’m making the following mistake:

The Value method of the Range object failed

The complete code is found here: By the Ghostbin

Private Sub CommandButton2_Click()

''' Botão "CADASTRAR" código para inserir as informações fornecidas na planilha especificada.

Application.ScreenUpdating = False

MULTIPLICAR = 1

Sheets("CADASTRAMENTO_V").Activate

If Range("D5").Value = "NOME" Then
    Range("D5").ClearContents
End If

Range("D5").Select

''' Faça, se célula ativa não estiver vazia.
Do
    If Not (IsEmpty(ActiveCell)) Then
        ActiveCell.Offset(1, 0).Select
    End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = Me.ComboBoxNOME.Value
ActiveCell.Offset(0, 1).Value = Me.TextBoxSALDO.Value * MULTIPLICAR
ActiveCell.Offset(0, 2).Value = Me.TextBoxDEBITO.Value * MULTIPLICAR
ActiveCell.Offset(0, 3).Value = Me.TextBoxGARANTIA.Value * MULTIPLICAR
ActiveCell.Offset(0, 5).Value = Me.TextBoxPLANO.Value * MULTIPLICAR

When that mistake happens he sends me to this line:

Activecell.Value = Me.ComboBoxNOME.Value

Can someone please help me?

Answer 1

I am not a fan of using "Active" family commands. Because of this type of problem that may or may not occur.

Do
    If Not (IsEmpty(ActiveCell)) Then
        ActiveCell.Offset(1, 0).Select
    End If
Loop Until IsEmpty(ActiveCell) = True

You can sub-post for:

Range("D5").End(xldown).offset(1,0)

And so use directly because this is a command and not loop.

Range("D5").End(xldown).offset(1,0).Value = Me.ComboBoxNOME.Value
with Range("D5").End(xldown).offset(1,0)
.Offset(0, 1).Value = Me.TextBoxSALDO.Value * MULTIPLICAR
.Offset(0, 2).Value = Me.TextBoxDEBITO.Value * MULTIPLICAR
.Offset(0, 3).Value = Me.TextBoxGARANTIA.Value * MULTIPLICAR
.Offset(0, 5).Value = Me.TextBoxPLANO.Value * MULTIPLICAR
end with

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Answering your questions]

"... and this code it will not overwrite the data when I open the form a second time for example?"

No, this code I mentioned shouldn’t overlap anything. Just like the original it looks for the last line and then fills the bottom line with the summarizing information will always adc. a new data line.

"...but is giving error in this code yours, something returns me saying that by the = (equal) The error goes to this line -> Range("D5"). End(xldown). offset(1,0) Could check for me by favour..."

Strange that you gave me this error could you share the file with sample information for me to take a look at and help? But I noticed that there is a logical error on my part in this code that I gave you. In this case this part:

Range("D5").End(xldown).offset(1,0).Value = Me.ComboBoxNOME.Value
with Range("D5").End(xldown).offset(1,0)
.Offset(0, 1).Value = Me.TextBoxSALDO.Value * MULTIPLICAR
.Offset(0, 2).Value = Me.TextBoxDEBITO.Value * MULTIPLICAR
.Offset(0, 3).Value = Me.TextBoxGARANTIA.Value * MULTIPLICAR
.Offset(0, 5).Value = Me.TextBoxPLANO.Value * MULTIPLICAR
end with

It would be better this way:

with Range("D5").End(xldown).offset(1,0)
.Value = Me.ComboBoxNOME.Value
.Offset(0, 1).Value = Me.TextBoxSALDO.Value * MULTIPLICAR
.Offset(0, 2).Value = Me.TextBoxDEBITO.Value * MULTIPLICAR
.Offset(0, 3).Value = Me.TextBoxGARANTIA.Value * MULTIPLICAR
.Offset(0, 5).Value = Me.TextBoxPLANO.Value * MULTIPLICAR
end with

Note that I took the line code:

Range("D5").End(xldown).offset(1,0).Value = Me.ComboBoxNOME.Value

And put it next to the With

with Range("D5").End(xldown).offset(1,0)
.Value = Me.ComboBoxNOME.Value 'ESTA LINHA

I tested here as the example below and this "ok"