Difficulty with using a variable in the offset function

ZenJelly

New Member
Joined
Apr 28, 2023
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
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

1682681850046.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
How about either of these 2 options:

VBA Code:
    Source.Range("A" & c.Row).Copy Target.Rows(j)
    Source.Cells(c.Row, "A").Copy Target.Rows(j)
 
Upvote 0
Solution
How about either of these 2 options:

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

Forum statistics

Threads
1,213,489
Messages
6,113,949
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