Edit this code to continuously run Macro? : )

jassyphee

New Member
Joined
Jan 8, 2014
Messages
16
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.
Code:
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:
2cdzrrd.png

dfvgc2.png


GREATLY APPRECIATE THE HELP! THANK YOU
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You could always use a temp string and build it as you go, adding the cells required.

Something along the lines of

Code:
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
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top