Can we get the for loop element value and move to a variable

for-each

#1

Hi Team,

Can we get the for loop element value and move to a variable? If there is a way please let me know.

Thanks
Naveen


#2

Hi Naveen, sure, you can use Constant value action for that.
image


#3

Hi Team,

Is there a option to get the value of a cell and read/find the value in other column and get the value of corresponding value of next/left cell value and loop it until it find the corresponding value of next/left cell value as blank.

image

I want to read each cell values of column “A” and get the value of column “B” and read/find the value in column “A” and loop it until find the Column “B” as blank.

Example for the screenshot attached:
Column “A” cell “6” value is “e” get the value of column “B” cell “6” that is “a” and read read/find the Column “A” it is find in cell “2” value is “a” now get the value and compare if we have value or it is blank if blank than it has to come of the loop and write the value of Column “A” which is cell “2” in Column “C” of cell “6”

Sample VBA code FYR is below.

Sub Auto_Open()
Dim myDir As String, fn As String, wb As Workbook
myDir = “C:\Users\nkatamuthyalu\Desktop\Packages”
fn = Dir(myDir & “*.csv”)
Workbooks.Open (myDir & fn)
Range(“C1”).Select
ActiveCell.FormulaR1C1 = “Top_Parent_Key”
j = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
For k = 2 To j
Range(“A” & k).Select
i = ActiveCell.Offset(0, 1).Value
1:
If Len(i) > 0 Then
Columns(“A:A”).Select
Selection.Find(What:=i, after:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
l = ActiveCell.Value
i = ActiveCell.Offset(0, 1).Value
GoTo 1

ElseIf Len(i) = 0 Then
l = ActiveCell.Value
End If
If Len(l) > 0 Then
Range("C" & k).Value = l
End If
Next k
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close False
Application.Quit
For Each wb In Application.Workbooks
wb.Close SaveChanges:=False
Next wb

End Sub