酷徒LOGO

vba查找日期并输入用户表单文本框


问题:

我有一个列表,vba需要在工作表中查找日期和计算机名字,然后粘贴到用户窗体文本框。

用户表单可以工作,但不能粘贴任何信息。

我已经玩了几天,无法想象如何使它工作,我真的希望这里的人可以帮助解决这个问题

不工作的代码在这里:


Private Sub CommandButton1_Click()


Dim arr, Dat As Date, Mon$, rng As Range, mch As Range, mch1$, clm&


arr = Array("Januar","Februar","Mars","April","Mai","Juni","Juli","August","September","Oktober","November","Desember")


Dat = Sheets("Dayplan").Cells(r, 3)


mch1 = Sheets("Dayplan").Cells(r, 4)


Mon = arr(Month(Dat) - 1)



With Sheets(Mon)


Set rng =. Rows(3).Find(Dat, lookat:=xlWhole)


If Not rng Is Nothing Then


Set mch =. Columns(1).Find(mch1, lookat:=xlWhole)


If Not mch Is Nothing Then


TextBox1 =. Cells(mch.Row, rng.Column + 1)


TextBox2 = Format(.Cells(mch.Row, rng.Column + 2),"d/m/yy")


TextBox3 =. Cells(mch.Row, rng.Column + 3)


End If


MsgBox"can not find machine"


End If


MsgBox"can not find date"


End With


Me.Hide


End Sub



这是主要工作表。

vba运行时的结果

我也有文件,但无法在这里上传

谢谢你


回答 1:

感谢 Christofer web er


. Cells(mch.Row, rng.Column + 1) = TextBox1


. Cells(mch.Row, rng.Column + 2) = TextBox2 = Format("dd-mm-yyyy")


. Cells(mch.Row, rng.Column + 3) = TextBox3



但是我只得到了不真实的/非 true的( 不确定 englich excel所说的是什么)

我的文件在这里:14天 avalible

rushfiles.one/client/publiclink.aspx?id=VZEITMAxCi

选择要下载的右边的箭头

格式无效,如何修复?


回答 2:

看来你找到了重要的部分。 我做了一些小修改,比如复制问题,并将错误消息放在 if的另一部分。


Private Sub CommandButton1_Click()


Dim arr, Dat As Date, Mon$, rng As Range, mch As Range, mch1$, clm&, issue$


arr = Array("Januar","Februar","Mars","April","Mai","Juni","Juli","August","September","Oktober","November","Desember")


Dat = Sheets("Dayplan").Cells(r, 3)


mch1 = Sheets("Dayplan").Cells(r, 4)


issue = Sheets("Dayplan").Cells(r, 5)


Mon = arr(Month(Dat) - 1)



With Sheets(Mon)


 Set rng =. Rows(3).Find(Dat, lookat:=xlWhole)


 If Not rng Is Nothing Then


 Set mch =. Columns(1).Find(mch1, lookat:=xlWhole)


 If Not mch Is Nothing Then


. Cells(mch.Row, rng.Column) = issue


. Cells(mch.Row, rng.Column + 1) = TextBox1


. Cells(mch.Row, rng.Column + 2) = Format(TextBox2,"dd-mm-yyyy")


. Cells(mch.Row, rng.Column + 3) = TextBox3


 Else


 MsgBox"can not find machine"


 End If


 Else


 MsgBox"can not find date"


 End If


End With


Me.Hide



End Sub



或者,没有嵌套的IFs


With Sheets(Mon)


 Set rng =. Rows(3).Find(Dat, lookat:=xlWhole)


 Set mch =. Columns(1).Find(mch1, lookat:=xlWhole)


 If rng Is Nothing Then


 MsgBox"can not find date"


 ElseIf mch Is Nothing Then


 MsgBox"can not find machine"


 Else


. Cells(mch.Row, rng.Column) = issue


. Cells(mch.Row, rng.Column + 1) = TextBox1


. Cells(mch.Row, rng.Column + 2) = Format(TextBox2,"dd-mm-yyyy")


. Cells(mch.Row, rng.Column + 3) = TextBox3


 End If


End With








Copyright © 2011 HelpLib All rights reserved.    知识分享协议 京ICP备17041772号-2  |  如果智培  |  酷兔英语  |  帮酷