excel - 如何在循环中使用文本框名称中的变量?
问题描述
在一个Do Until
循环中,如果我更改
TextBox69.Value
为Controls("TextBox" & X).Value
,则循环会被中断。
使用文本框时它会正确运行多次,但是一旦我将其更改为带有变量以替换文本框编号的控件解决方案,它只会运行整个循环一次。
Workbooks("Excel Stock System.xlsm").Worksheets("News Check").Controls("TextBox" & X).Value = _
Workbooks("Excel Stock System.xlsm").Worksheets("DataNews_News").Cells(i, 4)
这是完整的代码
i = Workbooks("Excel Stock System.xlsm").Worksheets("DataNews_News").Cells(Rows.Count, 1).End(xlUp).Row
Boxnumber = 14
X = 69
'This loops until all the lines are full
Do Until Boxnumber > 30 Or i < 4
If Workbooks("Excel Stock System.xlsm").Worksheets("TESTSURFACE").Range("B29") = _
Workbooks("Excel Stock System.xlsm").Worksheets("DataNews_News").Cells(i, 2) Then
Workbooks("Excel Stock System.xlsm").Worksheets("News Check").Cells(Boxnumber, 3).Value = _
Workbooks("Excel Stock System.xlsm").Worksheets("DataNews_News").Cells(i, 4)
Workbooks("Excel Stock System.xlsm").Worksheets("News Check").Cells(Boxnumber, 2).Value = _
Workbooks("Excel Stock System.xlsm").Worksheets("DataNews_News").Cells(i, 1)
Boxnumber = Boxnumber + 1
MsgBox ("does it just stop looping?" & Boxnumber)
'why does this stop the loop from running in a loop??
Workbooks("Excel Stock System.xlsm").Worksheets("News Check").Controls("TextBox" & X).Value = _
Workbooks("Excel Stock System.xlsm").Worksheets("DataNews_News").Cells(i, 4)
X = X + 1
End If
i = i - 1
Loop
解决方案
我重构了您的代码,以便它可以利用定义的变量,使其更具可读性和更易于调试。
在变量更改变量方面发生了很多事情,因此必须明确查看本地窗口
我无法使用该Controls
集合使其工作,而是使用了OLEObjects
(我不知道我是否遗漏了您的设置)
Option Explicit
Sub LoopThroughTextBoxes()
Dim stockSysWorkbook As Workbook
Set stockSysWorkbook = ThisWorkbook 'Workbooks("Excel Stock System.xlsm")
Dim dataNewsSheet As Worksheet
Set dataNewsSheet = stockSysWorkbook.Worksheets("DataNews_News")
Dim newsCheckSheet As Worksheet
Set newsCheckSheet = stockSysWorkbook.Worksheets("News Check")
Dim testSurfaceSheet As Worksheet
Set testSurfaceSheet = stockSysWorkbook.Worksheets("TESTSURFACE")
Dim boxNumber As Long
boxNumber = 14
Dim textBoxCounter As Long
textBoxCounter = 69
Dim rowNumber As Long
rowNumber = dataNewsSheet.Cells(dataNewsSheet.Rows.Count, "A").End(xlUp).Row
Do Until boxNumber > 30 Or rowNumber < 4
If testSurfaceSheet.Range("B29").Value = dataNewsSheet.Cells(rowNumber, "B").Value Then
newsCheckSheet.Cells(boxNumber, "C").Value = dataNewsSheet.Cells(rowNumber, "D").Value
newsCheckSheet.Cells(boxNumber, "B").Value = dataNewsSheet.Cells(rowNumber, "A").Value
boxNumber = boxNumber + 1
Dim textObj As Object
Set textObj = newsCheckSheet.OLEObjects("TextBox" & textBoxCounter).Object
textObj.Value = dataNewsSheet.Cells(rowNumber, "D").Value
End If
rowNumber = rowNumber - 1
Loop
End Sub
让我知道事情的后续
PS。您对以前的问题有有用的答案,因此请记住标记它们,以便其他人也可以找到它们。
推荐阅读
- c# - ASP.Net MVC5 检查类是否已经在内存中
- php - Wordpress 仅显示 1 个自定义帖子类型而不是 2 个
- javascript - React - Redux 在渲染组件之前等待获取结果
- imagemagick - 如何在imageMagick中更改带水印的图像背景
- intellij-idea - 我的项目开始在构建时抛出错误 NegativeArraySizeException
- java - Rally Rest API - 将集会模板复制到功能
- gnuplot - 聚集直方图中的误差线
- python - python中的ulam距离
- sql - 如何授予用户访问权限以查看/编辑 SQL 作业中的高级选项?
- angular - 如何以编程方式触发所有垫波纹?