ColdSpirit
New Member
- Joined
- Sep 30, 2022
- Messages
- 18
- Office Version
- 2010
- Platform
- Windows
Hello eveyone!
I have this sheet:
And i have this VBA macro assigned to one of the cmdbutton:
And i need 3 warnings for when there is left 5 / 3 and 1 position based on this condition on column "J":
Resuming:
Right now i have this sheet for when i click on "Nova AWB" the data on "E" will be copied to "J"!
I need 3 warning based on a condition to warn when there is left 5 / 3 / 1 number.
Is possible? How can i implement this based on the code above?
Many thanks in advance.
I have this sheet:
TESTE.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | NI : | 0806100 | USADAS | ||||||||||
2 | NF : | 0806150 | |||||||||||
3 | TOTAL AWB | 51 | |||||||||||
4 | SEQUÊNCIA | CHECK DIGIT | SEQ + DIGIT | AWB | Coluna1 | AWB | AWB2 | ||||||
5 | 0806100 | 1 | 8061 1001 | 047 - 8061 1001 | 8061001 | ||||||||
6 | 0806101 | 2 | 8061 1012 | 047 - 8061 1012 | 8061012 | ||||||||
7 | 0806102 | 3 | 8061 1023 | 047 - 8061 1023 | 8061023 | ||||||||
8 | 0806103 | 4 | 8061 1034 | 047 - 8061 1034 | 8061034 | ||||||||
9 | 0806104 | 5 | 8061 1045 | 047 - 8061 1045 | 8061045 | ||||||||
10 | 0806105 | 6 | 8061 1056 | 047 - 8061 1056 | 8061056 | ||||||||
047 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3 | D3 | =D2-D1+1 |
C5:C10 | C5 | =IF(B5="","",MOD(B5,7)) |
D5:D10 | D5 | =CONCAT(LEFT(G5,4)&" "&RIGHT(G5,4)) |
E5:E10 | E5 | ="047 - " &D5 |
G5,G7:G10 | G5 | =IFS(B$5="","",MOD(B$5,7),IF(B$5="","",CONCAT(B5,C5))) |
G6 | G6 | =IFS(B$5="","",MOD(B$5,7),IF(B$5="","",CONCAT(B6,C6))) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
And i have this VBA macro assigned to one of the cmdbutton:
VBA Code:
Private Sub CommandButton1_Click()
Dim ni As Range 'determinar o numero inicial
Dim nf As Range 'determinar o numero final
Dim ws As Worksheet 'determinar o valor "ws" como worksheet
Dim i As Integer
Set ws = ThisWorkbook.Sheets("047") 'associar "ws" à folha "047"
Set ni = Range("D1") 'associar ni = Célula J1
Set nf = Range("D2") 'associar fi = Célula J2
Range("B5:B156").Columns(1).ClearContents 'limpar a coluna A(1)
Range("J5:J156").Columns(1).ClearContents 'limpar a coluna H(1)
ni = _
InputBox(Prompt:="Inserir Numero Inicial") 'ni -inserção da numeração inicial (+ criação da inputbox)
nf = _
InputBox(Prompt:="Inserir Numero Final") 'fi -inserção da numeração final (+ criação da inputbox)
With ws.Range("B5") 'range B5 = começo da célula
.value = ws.Range("D1") '
.Resize(ws.Range("D3") + 1).DataSeries '
End With
If ws.Range("c3") > 0 Then
MsgBox "Numeração AWB inserida com sucesso" 'mensagem após inserção de numeração da awb
ElseIf ws.Range("c3") <= 0 Then
MsgBox "Falha na inserção da Numeração AWB. Por favor inserir numero válido" 'mensagem após inserção de numeração da awb
End If
End Sub
Private Sub Nova_awb_Click()
Dim lngRowCurrent As Long
Dim strCopyAddress As String, strPasteAddress As String
answer = MsgBox("Quer emitir uma nova AWB?", vbYesNo)
If answer = vbYes Then
With ThisWorkbook.Sheets("047")
lngRowCurrent = .Range("J" & Rows.Count).End(xlUp).Row + 1
strCopyAddress = "E" & lngRowCurrent & ":E" & lngRowCurrent
strPasteAddress = "J" & lngRowCurrent & ":J" & lngRowCurrent
.Range(strPasteAddress).Value2 = .Range(strCopyAddress).Value2 'execute copy-paste
MsgBox ("AWB emitida com sucesso")
End With
ElseIf answer = vbNo Then
MsgBox ("Cancelado")
Else
Exit Sub
End If
End Sub
And i need 3 warnings for when there is left 5 / 3 and 1 position based on this condition on column "J":
Resuming:
Right now i have this sheet for when i click on "Nova AWB" the data on "E" will be copied to "J"!
I need 3 warning based on a condition to warn when there is left 5 / 3 / 1 number.
Is possible? How can i implement this based on the code above?
Many thanks in advance.