Edit this code to continuously run Macro? : )

[jassyphee]

Current, what i'm doing is i want Cell A6 in Worksheet("Compensation") to be show up looking like "less Items # 1,5,4,6." Those numbers actually correspond to cell values in Column A in another worksheet known as "Cap. Rec." when Column E holds value R.

What I actually got was the formula only returning the LAST value in Column A with condition R in "Cap. Rec.". As well, I'm not sure how to continuously run so that Cell A6 from Compensation will automatically list the numbers out.

Here's the code so far.

Sub cals()Dim i As Long


For i = 5 To 500
y = Worksheets("Cap. Rec.").Cells(i, 1).Value
    If Worksheets("Cap. Rec.").Cells(i, 5) = "R" Then


    Sheets("Compensation").Range("A6").Value = "less Items #" & y
    Sheets("Compensation").Range("A6").Value = "less Items #" & y & ","
    End If


Next i


End Sub

Here's the excel pictures:

GREATLY APPRECIATE THE HELP! THANK YOU

 

[nigelk]

You could always use a temp string and build it as you go, adding the cells required.

Something along the lines of

Sub cals()

Dim i As Long
Dim str As String
Dim comma As String

str = ""
comma = ","


For i = 5 To 500


    If Worksheets("Cap. Rec.").Cells(i, 5) = "R" Then
    
    str = str & Cells(i, 1)
    str = str & comma
    End If


Next i


 Sheets("Compensation").Range("A6").Value = "less Items #" & str


End Sub

 

[jassyphee]

Thank you so much! ; ) I was able to use this as my starting ground and build on my excel to work exactly the way i wanted it to! This was really helpful. Greatly appreciate it!

You could always use a temp string and build it as you go, adding the cells required.

Something along the lines of

Sub cals()

Dim i As Long
Dim str As String
Dim comma As String

str = ""
comma = ","


For i = 5 To 500


    If Worksheets("Cap. Rec.").Cells(i, 5) = "R" Then
    
    str = str & Cells(i, 1)
    str = str & comma
    End If


Next i


 Sheets("Compensation").Range("A6").Value = "less Items #" & str


End Sub