Pick up the selected value in a cell and subtract with the value of combobox in vba

Question

Pick up the selected value in a cell and subtract with the value of combobox in vba

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

Good afternoon,

I have the cell selected and now I want to get the value of that selected cell and subtract it from the value of the combobox and then pick up that result and put it in the selected cell.

So far I've made 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 variable myvar and the value of the combobox to the comvar variable, and then I created the result variable to get the desired value. So far so good, but after putting the value in the cell changes me the value of several cells and not only the selected cell, can someone help me?

At this point I know what the problem is, but I still do not know how to solve it. The problem is that I am not assigning the value of the cell to the variable myvar, this variable is 0, so it gives me the negative number with the value that I assign to the variable comvar.

vba

asked by anonymous 22.05.2017 / 13:24

3 answers

Error connecting database (Fatal error: Uncaught Error: Call to undefined function mysql_connect () in) [duplicate] Image for base64

score 0 · Answer 1

You said that "putting the value in the cell changes me the value of multiple cells and not just the selected cell", correct?

This is because the excerpt ...

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 assignment of the calculated value to all cells in column C, from row 3 to the last row filled in column C.

I believe you change 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 ActiveCell.FormulaR1C1 = resultado command will only be executed at the end of the loop.

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

Or you can change 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

score 0 · Answer 2

Thanks 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, it removes the value in the last cell with value and I want it to remove the value is 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

Removes the value of the last cell with value, but keeps that cell with the same value and writes the result in the cell below that with no data.

I'm having trouble solving this problem

score 0 · Answer 3

I have now made this change, I already change the value in the selected cell, but it does not give me the correct result. Instead of making the variable myvar (value in the cell) minus the comvar variable (combobox value), it is replacing the value of the selected cell with the value of the second variable (comvar) and places 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