更新每个循环变量

瓦卡斯

在此处输入图片说明

目的是找到列c中的值与从更新的“ firstvalue”变量获得的所有值之间的圆度,这些值以逗号分隔并存储在“ M”列中。

Sub circular()
Dim rng As Range, rng2 As Range, firstvalue As String, secondvalue As String
Set sh = ThisWorkbook.Worksheets("Sheet1")
lr = sh.Range("C" & Rows.Count).End(xlUp).Row
For Each rng In sh.Range("C5:C" & lr) 'iterating over each cell in column "c" from C5 till lastrow "lr". 
        firstvalue = rng.Offset(0, 10).value 'Corresponding cell value which is comma seperated in column 
                                              "M" i:e after 10 columns from "C".    
        Dim n As Variant
        For Each n In Split(firstvalue, ",")   'Looping through each value obtained from split function.
        Set rng2 = sh.Range("C5:C" & lr).Find(Trim(n), LookIn:=xlValues)  'Finding that split value again 
                                                                          in column "C".
        If Not rng2 Is Nothing Then                                      'if exists in column c then get. 
        secondvalue = rng2.Offset(0, 10).value                           'corresponding cell values.    
        firstvalue = firstvalue & "," & secondvalue                      'now first value is concatnated 
                                                                          with initial firstvalue
        End If
        Next n                        
        MsgBox firstvalue
        'Now i want to itterate over updated "firstvalue" in split function and this goes on in circular 
         fashion until rng value exists in firstvalue. 
Next rng            'then change next rng and continue the above whole process for this value and so on.
End Sub

该代码适用于初始firstvalue,可以有人建议采用任何方法迭代更新后的firstvalue。

约翰·孙

我不确定我是否完全了解您的目标,但是此代码应找到每个任务的所有前身:

Sub circular()
Dim sh As Worksheet
Dim rTask As Range
Dim oCell As Range
Dim oFound As Range
Dim lr As Long, j As Long
Dim aPredecessors As Variant
Dim sCurTask As String
Dim secondValue As String
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    lr = sh.Range("C" & Rows.Count).End(xlUp).Row
    Set rTask = sh.Range("C5:C" & lr)
    
    For Each oCell In rTask
        sCurTask = Trim(oCell.Text)
        aPredecessors = getPredecessors(Trim(oCell.Offset(0, 10).Text))
        j = LBound(aPredecessors)
        Do Until j > UBound(aPredecessors)
            secondValue = aPredecessors(j)
            If sCurTask = secondValue Then
                ReDim Preserve aPredecessors(j)
                Debug.Print "Task '" & sCurTask & "': Cyclic link '" & secondValue & "' for '" & Join(aPredecessors, ",") & "'!"
                aPredecessors(j) = aPredecessors(j) & " !!!"
            Else
                If secondValue <> vbNullString Then
                    Set oFound = rTask.Find(secondValue, LookIn:=xlValues)
                    If oFound Is Nothing Then
                        ReDim Preserve aPredecessors(j)
                        Debug.Print "Task '" & sCurTask & "': Task '" & secondValue & "' for '" & Join(aPredecessors, ",") & "' not found!"
                        aPredecessors(j) = aPredecessors(j) & " ???"
                    Else
                        Call addNewTasks(aPredecessors, Trim(oFound.Offset(0, 10).Text))
                    End If
                End If
            End If
            j = j + 1
        Loop
        oCell.Offset(0, 11).Value = Join(aPredecessors, ",")
    Next oCell
End Sub

Function getPredecessors(sPredecessors As String)
Dim i As Long
Dim aTemp As Variant, sRes As String
Dim sTest As String
    sRes = vbNullString
    aTemp = Split(sPredecessors, ",")
    For i = LBound(aTemp) To UBound(aTemp)
        sTest = Trim(aTemp(i))
        If InStr("," & sRes & ",", "," & sTest & ",") = 0 Then sRes = sRes & sTest & ","
    Next i
    If Len(sRes) > 1 Then sRes = Left(sRes, Len(sRes) - 1)
    getPredecessors = Split(sRes, ",")
End Function

Sub addNewTasks(aData As Variant, sPredecessors As String)
Dim i As Long, uB As Long
Dim aTemp As Variant
Dim sTest As String, sValid As String
    aTemp = Split(sPredecessors, ",")
    If UBound(aTemp) >= 0 Then ' Not empty
        sValid = "," & Join(aData, ",") & ","
        For i = LBound(aTemp) To UBound(aTemp)
            sTest = Trim(aTemp(i))
            If sTest <> vbNullString Then
                If InStr(sValid, "," & sTest & ",") = 0 Then
                    uB = UBound(aData) + 1
                    ReDim Preserve aData(uB)
                    aData(uB) = sTest
                    sValid = "," & Join(aData, ",") & ","
                End If
            End If
        Next i
    End If
End Sub

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

在循环中更新变量

来自分类Dev

更新ForEach循环内的变量

来自分类Dev

tkinter标签或“循环更新变量”

来自分类Dev

变量未从While循环更新

来自分类Dev

变量在For循环后不会更新

来自分类Dev

JavaScript变量未循环更新

来自分类Dev

在 For 循环中动态更新变量?

来自分类Dev

for循环中的循环变量何时更新?

来自分类常见问题

无法在每个循环内设置变量数据

来自分类Dev

如何在每个循环中添加变量

来自分类Dev

if语句的每个循环,保存到不同的变量

来自分类Dev

为for循环中的每个循环创建新的变量名

来自分类Dev

使用循环变量在每个循环中获取单词

来自分类Dev

每个函数调用变量更新4次

来自分类Dev

循环内的角度更新$ scope变量

来自分类Dev

如何更新for循环中更改的变量?

来自分类Dev

R中的For循环更新的动态变量

来自分类Dev

while循环迭代后未更新变量

来自分类Dev

变量不更新通过csv文件循环

来自分类Dev

在forEach循环中更新角度$ scope变量

来自分类Dev

更新循环内对象内部的变量

来自分类Dev

循环变量在 angular 函数内部未更新

来自分类Dev

循环中引用的 Python 更新变量

来自分类Dev

从循环内部在while循环测试中更新bash变量?

来自分类Dev

在循环外设置变量,并在循环的每个项目中获取变量

来自分类Dev

每个循环具有多个变量的C ++ 11

来自分类Dev

用do循环变量连接每个图的标题

来自分类Dev

Java为什么不能为每个循环解析变量?

来自分类Dev

Xpath:从文档节点变量中为每个循环选择节点