Passing text from sheet to sheet in VBA dosn't work when using a UserForm CommandButton

Question

I have this code right here and for some reason it doesn't work

Public Sub CommandButton3_Click()
'agregar'

ComboBox2.Value = sel
For i = 2 To 200
celda = ActiveCell.Row
Add = Worksheets("Programación").Cells(i, 2)
If sel = Add Then

    Sheets("Programación").Activate
    Sheets("Programación").Select
    Tipo = Sheets("Programación").Cells(i, 3).Text
    Expl = Sheets("Programación").Cells(i, 4).Text
    Recom = Sheets("Programación").Cells(i, 5).Text
    Vul = Sheets("Programación").Cells(i, 6).Text
    Ame = Sheets("Programación").Cells(i, 7).Text
    Rie = Sheets("Programación").Cells(i, 8).Text
    HA = Sheets("Programación").Cells(i, 2).Text

    Sheets("Matriz_de_Hallazgos").Activate
    Sheets("Matriz_de_Hallazgos").Select
    Sheets("Matriz_de_Hallazgos").Cells(celda, 2) = Tipo
    Sheets("Matriz_de_Hallazgos").Cells(celda, 4) = HA
    Sheets("Matriz_de_Hallazgos").Cells(celda, 5) = Expl
    Sheets("Matriz_de_Hallazgos").Cells(celda, 6) = Vul
    Sheets("Matriz_de_Hallazgos").Cells(celda, 7) = Ame
    Sheets("Matriz_de_Hallazgos").Cells(celda, 8) = Rie
    Sheets("Matriz_de_Hallazgos").Cells(celda, 9) = Recom

  celda = celda + 1
Sheets("Matriz_de_Hallazgos").Cells(celda, 4).Select

i = 201
End If

Next i


ComboBox2.Clear

End Sub

The thing that I'm trying to do is that the macro searches for the same combobox text inside the "Programación" worksheet, if the text is the same then it saves some text categories and pastes them on the "Matriz de Hallazgos" sheet, just like search, look if equal, copy and paste. I would be of great help if you guys could give me some advice

[Work with objects](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [Avoid using .Text](https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) — Siddharth Rout, Feb 24 '20 at 04:49
And yes. [Declare your variables](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-explicit-statement) — Siddharth Rout, Feb 24 '20 at 04:54

score 0 · Answer 1 · answered Feb 24 '20 at 04:50

Since you want to compare the combobox to the values in cells, I think that the line

ComboBox2.Value = sel

should be

sel = ComboBox2.Value

to retrieve the value so that it is compared correctly. Right now, the first line as it currently stands is setting the combobox to whatever is stored in sel, which will be nothing if the variable hasn't been used yet. Sel then remains empty when comparing to cell values.

Passing text from sheet to sheet in VBA dosn't work when using a UserForm CommandButton

1 Answers1