I am fairly new to coding and vba and I am having a hard time trying to assign values to the next empty cell without overwriting the first value before the macro ends.
I have a userform with 48 checkboxes (labeled checkbox 49-106). Depending on what checkboxes the user selects, I want the value associated with that checkbox to be printed in a column in worksheet Failure Modes. The code prints the value to the correct column but if multiple checkboxes are ticked, the value of the last checkbox is the only one displayed.
Dim wsDataTable As Worksheet
Dim wsFM As Worksheet
Dim text1 As String
text1 = TextBox1.Value
Set wsDataTable = Worksheets("Data Table")
Set wsFM = Worksheets("Failure Modes")
iRow = wsDataTable.Cells.Find(What:=text1, SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
hRow = Worksheets("Failure Modes").Cells(Rows.Count, 1).End(xlUp).Row
If CheckBox49.Value = True Then
wsDataTable.Cells(iRow, 59).Value = "A1 "
wsFM.Cells(hRow, 3).End(xlUp).Offset(1, 0) = "A1 "
Else
wsDataTable.Cells(iRow, 59).Value = ""
End If
If CheckBox50.Value = True Then
wsDataTable.Cells(iRow, 60).Value = "B1 "
wsFM.Cells(hRow, 3).End(xlUp).Offset(1, 0) = "B1 "
Else
wsDataTable.Cells(iRow, 60).Value = ""
End If
If CheckBox51.Value = True Then
wsDataTable.Cells(iRow, 61).Value = "C1 "
wsFM.Cells(hRow, 3).End(xlUp).Offset(1, 0) = "C1 "
Else
wsDataTable.Cells(iRow, 61).Value = ""
End If
If CheckBox52.Value = True Then
wsDataTable.Cells(iRow, 62).Value = "D1 "
wsFM.Cells(hRow, 3).End(xlUp).Offset(1, 0) = "D1 "
Else
wsDataTable.Cells(iRow, 62).Value = ""
End If
If checkbox 49 is checked, column 3 in worksheet Failure Mode will display:
A1
If checkbox 49,50,51,52 is checked, column 3 in worksheet Failure Mode will display:
D1
I would like if when checkbox 49,50,51,52 is checked, column 3 in worksheet Failure Mode will display:
A1
B1
C1
D1
I initially thought that using a loop here would help but since I want the value to also be stored in worksheet Data Table, I'm having hard time executing that loop.
Any help will be greatly appreciated!
Aucun commentaire:
Enregistrer un commentaire