更改VBA宏代码以更改编号第2部分 [英] Changing VBA macro code to change number part 2
本文介绍了更改VBA宏代码以更改编号第2部分的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我不得不在上一个问题之后提出一个新问题,我不得不减少并增加一个数字,这个链接位于
I have had to open a new question following a previous question i had to decrease and increment a number which is on the link bellow
这是我正在努力工作的代码,我几乎可以工作,但在某个地方出错。
this is the code that i am trying to work with and i got it almost to work but somewhere its gone wrong.
Bulkwks。 B5]
是 M20
historywks。[a2]
是时间
historywks。[b2]
是名字
historywks。[C2]
是 m201001
Sub bulkON_Click()
Dim trnwkbk As Workbook
Dim Bulkwks As Worksheet
Dim Deswkbk As Workbook
Dim LogNum As Range, LastNum, NewNum,
Dim historywks As Worksheet
Dim nextRow As Long
Dim lOR As Long
Dim myIn As String
Dim myLeft As String
Dim myMid As Integer, myRight As Integer, i As Integer
Dim myOut As String
Set trnwkbk = Workbooks("Transport.xls")
Set Bulkwks = trnwkbk.Worksheets("Bulk")
lOR = MsgBox("Have you selected the right MIS or HUB or PSA number?", vbQuestion + vbYesNo, "Number Order")
If lOR = vbNo Then
MsgBox "Please select right Order Number"
Else
Application.ScreenUpdating = False
' for testing i just made it post in test sheet in same workbook
'Set Deswkbk = Workbooks.Open("\\dunton01\Inspections\TRANSPORT\New_transport\data\Febuary_2013.xls")
'Set historywks = Deswkbk.Worksheets("Data")
Set historywks = Worksheets("test")
Set LogNum = historywks.[C2]
With historywks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
If LogNum(2, 1) = "" Then
LastNum = LogNum
Else
LastNum = LogNum(LogNum.End(xlDown).Row - 1, 1)
End If
NewNum = Bulkwks.[B5] & Val(Mid(LastNum, 2)) + 1
If LogNum(2, 1) = "" Then
LogNum(2, 1) = NewNum
Else
myIn = LogNum
myLeft = Left(myIn, 1)
myMid = CInt(Mid(myIn, 2, 2))
myRight = CInt(Right(myIn, 4))
myOut = myLeft & Format(myMid, "00") & Format(myRight, "0000")
i = 0
Debug.Print "IN: " & myIn
Debug.Print "BROKEN UP: " & myOut
Do Until myMid = -1
Debug.Print "ITERATION " & Format(i, "00") & ": " & myLeft & Format(myMid, "00") & Format(myRight, "0000")
myMid = myMid - 1
myRight = myRight + 1
myOut = myLeft & Format(myMid, "00") & Format(myRight, "0000")
i = i + 1
With historywks
'enter date and time stamp in record
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "B").Value = Application.UserName
.Cells(nextRow, "C").Value = myIn
End With ' for testing i just disabled this Deswkbk.save
Loop
' for testing i just disabled this
'Deswkbk.Close savechanges:=True
Application.ScreenUpdating = True
Bulkwks.[E3] = NewNum
End If
' for testing i just disabled this
'Call File_In_Network_Folder
End If
End Sub
推荐答案
您需要使用 myOut
变量。
.Cells(nextRow, "C").Value = myOut
这篇关于更改VBA宏代码以更改编号第2部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文