Difficulty with using a variable in the offset function

[ZenJelly]

I spent a few hours searching and trying different things and I'm stumped. What I have are columns with the string "assigned" in them. I'm then needing it to copy from column A to a new sheet in my defined range. I've wrote the code to find each instance of "assigned" however where I am getting stuck is "assigned" is in a variable columns and I need the copy function to target a fixed column. What this means is when it finds "assigned" in C22 then it goes to A on the same row and copies over, then when it finds "assigned" in E it also goes to column A and copies the info over.

I've tried defining my A variable different ways for offset and cannot get this to work.

Sub CreateLists()

Dim c As Range

Dim j As Integer
Dim Source As Worksheet
Dim Target As Range
Dim y As Date
Dim A As Range

y = Date

Set Source = ActiveWorkbook.Worksheets("Sheet2")
Set Target = ActiveWorkbook.Worksheets("Sheet3").Range("E:E")
Set A = Columns(, 1)

j = 2


For Each c In Source.Range("C22:C37")
If InStr(1, c.Text, "Assigned") Then
c.Offset(, A).Copy Target.Rows(j)

j = j + 1

End If
Next c


End Sub

 

[Alex Blakenburg]

How about either of these 2 options:

    Source.Range("A" & c.Row).Copy Target.Rows(j)
    Source.Cells(c.Row, "A").Copy Target.Rows(j)

 

[ZenJelly]

How about either of these 2 options:

    Source.Range("A" & c.Row).Copy Target.Rows(j)
    Source.Cells(c.Row, "A").Copy Target.Rows(j)

Thank you so f'ing much. I had tried source.range earlier but in my haste forgot ""

 

[Alex Blakenburg]

You're welcome. Glad I could help.