The 'Value' method of the 'Range' object failed

Question

The 'Value' method of the 'Range' object failed

Navigation

#1 by (2 votes)

0

Good night, I have the following error:

The 'Value' method of the 'Range' object failed

The full code is here: GhostBin Hair

PrivateSubCommandButton2_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 this error happens it sends me to this line:

ActiveCell.Value = Me.ComboBoxNOME.Value

Can someone please help me?

debug excel vba value userform

asked by anonymous 20.11.2018 / 01:20

1 answer

Result division with decimal no rounding php [duplicate] background color in the input with the highest value

score 2 · Accepted Answer

I'm not much fan of using commands from the "Active" family. 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 substitute 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

eur-lex.europa.eu eur-lex.europa.eu = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - =

[Answering your questions]

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

No, this code I mentioned should not overlap anything. Just like the original it searches for the last line and then fills the bottom line with the summary information always goes adc. a new line of data.

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

Strange that you have given 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 passed. In this case:

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 of code:

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

And I placed next to 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"