Hello everyone
I have this code right here and i want to add another something else to it but i have no clue how to
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)
If you need more info or a better explanation ill try my best to give it to you
Thank you for reading!
I have this code right here and i want to add another something else to it but i have no clue how to
Code:
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
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)
If you need more info or a better explanation ill try my best to give it to you
Thank you for reading!