Take the selected value in a cell and subtract with the combobox value in vba

Question

Good afternoon,

I have the selected cell and now I want to take the value of that selected cell and subtract it from the combobox value and then take that result and put it into the selected cell.

So far I’ve done this part of the code:

Private Sub Btn_Eliminar_Click()

Dim I As Long
Dim myvar As Integer
Dim comvar As Integer
Dim resultado As Integer



For I = 3 To Folha2.Range("C65536").End(xlUp).Row
myvar = Range("C" & I).Value
comvar = Total_Stock.Value
resultado = myvar - comvar

Range("C" & I).Select

ActiveCell.FormulaR1C1 = resultado

Next I

End Sub

I assigned the value of the selected cell to the myvar variable and the value of the combobox to the variable comvar and then created the result variable to get the desired value. So far so good, but then by putting the value in the cell changes the value of several cells and not only the selected cell, someone can help me?

Right now I know what the problem is, but I still don’t know how to fix it. The problem is that I am not assigning the cell value to the myvar variable, this variable is 0, so it gives me the negative number with the value I assign to the variable comvar.

Answer 1

You said that "by placing the value in the cell changes the value of several cells and not only the selected cell", correct?

This is because the passage...

For I = 3 To Folha2.Range("C65536").End(xlUp).Row
    myvar = Range("C" & I).Value
    comvar = Total_Stock.Value
    resultado = myvar - comvar
    Range("C" & I).Select
    ActiveCell.FormulaR1C1 = resultado
Next I

... performs the task of assigning the calculated value to all cells in column C from row 3 to the last row filled in column C.

---

I believe that changing to:

For I = 3 To Folha2.Range("C65536").End(xlUp).Row
    myvar = Range("C" & I).Value
    comvar = Total_Stock.Value
    resultado = myvar - comvar
    Range("C" & I).Select
Next I
ActiveCell.FormulaR1C1 = resultado

Only the last selected cell will be changed, since the command ActiveCell.FormulaR1C1 = resultado will only be executed at the end of the loop.

Emphasizing that at the end of the for the selected cell will be the last filled cell in column C.

---

Or you can change it to:

For I = 3 To Folha2.Range("C65536").End(xlUp).Row
    myvar = Range("C" & I).Value
    comvar = Total_Stock.Value
    resultado = myvar - comvar
Next I
Cells(i, 3).Value = resultado

Answer 2

Thank you for trying to help me, but in the first example:

For I = 3 To Folha2.Range("C65536").End(xlUp).Row
    myvar = Range("C" & I).Value
    comvar = Total_Stock.Value
    resultado = myvar - comvar
    Range("C" & I).Select
Next I
ActiveCell.FormulaR1C1 = resultado

instead of removing the value in the selected cell, remove the value in the last value cell and I want you to remove the value in the selected cell.

In the second case:

For I = 3 To Folha2.Range("C65536").End(xlUp).Row
    myvar = Range("C" & I).Value
    comvar = Total_Stock.Value
    resultado = myvar - comvar
Next I
Cells(i, 3).Value = resultado

Take the value of the last cell with value, but keep that cell with the same value and write the result in the cell below the one without data.

I’m really having a hard time solving this problem

Answer 3

I have now made this change, already changes the value in the selected cell, but does not give me the correct result. Instead of doing the variable myvar(value that is in the cell) minus the variable comvar (value of the combobox), it is replacing the value of the selected cell with the value of the second variable (comvar) and puts the negative number:

Private Sub Btn_Eliminar_Click()

Dim I As Long
Dim myvar As Integer
Dim comvar As Integer
Dim resultado As Integer



For I = 2 To Folha2.Range("C65536").End(xlUp).Row
If CB_TipoFralda.Value = Folha1.Range("C" & I).Value Then
   Total_Stock.AddItem Folha1.Range("E" & I).Value

End If

Next I

For I = 2 To Folha2.Range("A65536").End(xlUp).Row
If Me.CB_TipoFralda.Text = Folha2.Range("A" & I).Value Then
Folha2.Range("C" & I).Select

End If

Next I

myvar = Range("C" & I).Value
comvar = Total_Stock.Value
resultado = (myvar - comvar)

ActiveCell.FormulaR1C1 = resultado

End Sub