Basically i want to increment the value in cell N19 of my sourcesheet when this value gets sent over to column L of dados.xlsm let's say i write the following in cell N19 "A120" and choose 4 repetitions I want the following numbers to be seen in column L of dados : "A120" and then "A121" (in the cell below) ,"A122" (in the cell below), "A123" (in the cell below), "A124" (in the cell below)
basically i want to increment it the same amount of times as there is repetitions
The types of values cell N19 will be receiving are always something along the lines of "C251" Oh and i would also like to make it so that if the user writes something like "D999" and chooses 2 or more repetitions the values will be sent like "D999","D1","D2"... (Basically 999 is the max number)
I tried the following code but i have no clue on how to implement the function i described earlier
Private Sub CommandButton1_Click()
Dim repeticoes As Integer
repeticoes = Me.ComboBox1.value
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim cavidadeValue As String
Dim targetWorkbook As Workbook
Dim targetCell As Range
Dim tipoDePecaComboBox As Object
Dim tipoDeProblemaComboBox As Object
Dim cavidadesComboBox As Object
Dim semanaComboBox As Object
Dim anoComboBox As Object
Dim tipoAnaliseBox As Object
Dim problemaBox1 As Object
' Definição do sheet source (Sheet1) e do target (Dados)
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set targetWorkbook = Workbooks.Open("W:\Quality\70. Leaks\Leak Files\Teardown YF\YF\teste\dados.xlsm")
Set targetSheet = targetWorkbook.Sheets("Dados")
Set tipoAnaliseBox = sourceSheet.OLEObjects("tipoanaliseBox").Object
Set problemaBox1 = sourceSheet.OLEObjects("problemaComboBox").Object
Dim i As Integer
For i = 1 To repeticoes
Select Case True
'----------------------------------------------------------3/4------------------------------------------------------------------
Case tipoAnaliseBox = "3/4"
sourceSheet.Unprotect password:="567"
' Encontra a próxima linha disponível começando na segunda linha da coluna B no targetSheet
lastRow = targetSheet.Cells(targetSheet.Rows.Count, 3).End(xlUp).row + 1
' A linha abaixo vai buscar o valor do cavidadeBox e transfere-o em formato de texto
' É necessário fazer isto pois como certas cavidades têm o formato de x/x/x, o Excel assume que as cavidades
' deviam estar em formato de data, e nós não queremos isso.
cavidadeValue = sourceSheet.OLEObjects("cavidadeBox").Object.Text
' Transferência dos valores do sourceSheet para o targetSheet no arquivo de destino
targetSheet.Cells(lastRow, "B").NumberFormat = "@"
targetSheet.Cells(lastRow, "B").value = sourceSheet.OLEObjects("tipoAnaliseBox").Object.value
targetSheet.Cells(lastRow, "F").value = sourceSheet.Range("R14").value
targetSheet.Cells(lastRow, "C").NumberFormat = "@"
targetSheet.Cells(lastRow, "C").value = sourceSheet.OLEObjects("genComboBox").Object.value
targetSheet.Cells(lastRow, "D").value = sourceSheet.OLEObjects("modelComboBox").Object.value
targetSheet.Cells(lastRow, "E").value = sourceSheet.OLEObjects("pecaComboBox").Object.value
If sourceSheet.Range("E21").value = "" Then
targetSheet.Cells(lastRow, "H").value = sourceSheet.OLEObjects("semanaBox").Object.value & "-" & sourceSheet.OLEObjects("anoBox").Object.value
Else
targetSheet.Cells(lastRow, "H").value = sourceSheet.Range("E21").value
End If
targetSheet.Cells(lastRow, "G").value = sourceSheet.Range("K14").value
targetSheet.Cells(lastRow, "I").NumberFormat = "@" ' Formatação do valor das cavidades como texto
targetSheet.Cells(lastRow, "I").value = cavidadeValue
targetSheet.Cells(lastRow, "J").value = sourceSheet.Range("L19").value
targetSheet.Cells(lastRow, "K").value = sourceSheet.OLEObjects("problemaComboBox").Object.value
targetSheet.Cells(lastRow, "L").NumberFormat = "@"
targetSheet.Cells(lastRow, "L").value = sourceSheet.Range("N19").value
targetSheet.Cells(lastRow, "M").value = sourceSheet.OLEObjects("tipoamostraBox").Object.value
targetSheet.Cells(lastRow, "N").value = sourceSheet.OLEObjects("turnoBox").Object.value
targetSheet.Cells(lastRow, "O").value = sourceSheet.OLEObjects("comboBoxanalisador").Object.value
targetSheet.Cells(lastRow, "P").NumberFormat = "@"
targetSheet.Cells(lastRow, "P").value = sourceSheet.Range("O12").value
targetSheet.Cells(lastRow, "Q").NumberFormat = "@"
targetSheet.Cells(lastRow, "Q").value = sourceSheet.Range("S19").value
' Salva o arquivo de destino
targetWorkbook.Save
'----------------------------------------------------------Fixture------------------------------------------------------------------
Case tipoAnaliseBox = "Fixture"
sourceSheet.Unprotect password:="567"
' Encontra a próxima linha disponível começando na segunda linha da coluna B no targetSheet
lastRow = targetSheet.Cells(targetSheet.Rows.Count, 3).End(xlUp).row + 1
' A linha abaixo vai buscar o valor do cavidadeBox e transfere-o em formato de texto
' É necessário fazer isto pois como certas cavidades têm o formato de x/x/x, o Excel assume que as cavidades
' deviam estar em formato de data, e nós não queremos isso.
cavidadeValue = sourceSheet.OLEObjects("cavidadeBox").Object.Text
' Transferência dos valores do sourceSheet para o targetSheet no arquivo de destino
targetSheet.Cells(lastRow, "B").NumberFormat = "@"
targetSheet.Cells(lastRow, "B").value = sourceSheet.OLEObjects("tipoAnaliseBox").Object.value
targetSheet.Cells(lastRow, "F").value = sourceSheet.Range("R14").value
targetSheet.Cells(lastRow, "C").NumberFormat = "@"
targetSheet.Cells(lastRow, "C").value = sourceSheet.OLEObjects("genComboBox").Object.value
targetSheet.Cells(lastRow, "D").value = sourceSheet.OLEObjects("modelComboBox").Object.value
targetSheet.Cells(lastRow, "E").value = sourceSheet.OLEObjects("pecaComboBox").Object.value
If sourceSheet.Range("E21").value = "" Then
targetSheet.Cells(lastRow, "H").value = sourceSheet.OLEObjects("semanaBox").Object.value & "-" & sourceSheet.OLEObjects("anoBox").Object.value
Else
targetSheet.Cells(lastRow, "H").value = sourceSheet.Range("E21").value
End If
targetSheet.Cells(lastRow, "G").value = sourceSheet.Range("K14").value
targetSheet.Cells(lastRow, "I").NumberFormat = "@" ' Formatação do valor das cavidades como texto
targetSheet.Cells(lastRow, "I").value = cavidadeValue
targetSheet.Cells(lastRow, "J").value = sourceSheet.Range("L19").value
targetSheet.Cells(lastRow, "K").value = sourceSheet.OLEObjects("problemaComboBox").Object.value
targetSheet.Cells(lastRow, "L").NumberFormat = "@"
targetSheet.Cells(lastRow, "L").value = sourceSheet.Range("N19").value
targetSheet.Cells(lastRow, "M").value = sourceSheet.OLEObjects("tipoamostraBox").Object.value
targetSheet.Cells(lastRow, "N").value = sourceSheet.OLEObjects("turnoBox").Object.value
targetSheet.Cells(lastRow, "O").value = sourceSheet.OLEObjects("comboBoxanalisador").Object.value
targetSheet.Cells(lastRow, "P").NumberFormat = "@"
targetSheet.Cells(lastRow, "P").value = sourceSheet.Range("O12").value
targetSheet.Cells(lastRow, "Q").NumberFormat = "@"
targetSheet.Cells(lastRow, "Q").value = sourceSheet.Range("S19").value
' Salva o arquivo de destino
targetWorkbook.Save
End Select
Next i
' Fecha o arquivo de destino sem exibição
targetWorkbook.Close SaveChanges:=False
' Exibe uma mensagem de sucesso ao usuário
MsgBox "Valores transferidos com sucesso!", vbInformation, "Sucesso"
' Fecha a janela do formulário
Unload Me
End Sub
If you require a better explanation or more information about my issue feel free to ask and ill do my best to give you a better explanation
Thank you for reading.
You can use a function to create a set of sequence values. Eg:
'Return a Collection containing `num` values, each beginning with the
' non-numeric prefix from `txtStart`, with an incrementing numeric suffix
Function Sequence(txtStart As String, num As Long)
Dim i As Long, nStart As Long, prefix As String, c As String
Set Sequence = New Collection
For i = 1 To Len(txtStart)
c = Mid(txtStart, i, 1)
If c Like "#" Then Exit For
prefix = prefix & c 'extract all non-numeric characters from the start of the string
Next i
nStart = CLng(Mid(txtStart, Len(prefix) + 1)) 'first numeric value
For i = nStart To (nStart + num - 1)
Sequence.Add prefix & 1 + ((i - 1) Mod 999) '## cap max numeric part to 999
Next i
End Function
In your code (relevant parts only):
Dim repeticoes As Long, seq As Collection, lastRow As Range
'...
'...
repeticoes = Me.ComboBox1.Value
'generate the sequence
Set seq = Sequence(sourceSheet.Range("N19").Value, repeticoes)
'...
'...
For i = 1 To seq.Count
Set lastRow = targetSheet.Cells(targetSheet.Rows.Count, 3).End(xlUp).Offset(1).EntireRow 'next empty row
'...
'...
lastRow.Columns("L").Value = seq(i)
'...
'...
Next i
FYI - instead of doing something like this repeatedly:
targetSheet.Cells(lastRow, "C").NumberFormat = "@"
targetSheet.Cells(lastRow, "C").value = sourceSheet.OLEObjects("genComboBox").Object.value
You can make a Sub:
Sub SetTextValue(c As Range, v)
c.NumberFormat = "@"
c.Value = v
End Sub
...and then call it like
SetTextValue targetSheet.Cells(lastRow, "C"), sourceSheet.OLEObjects("genComboBox").Object.value