Compile Error

[ah2024_2024]

Please do not roast me, I have extensive knowledge in formulas and basic knowledge in VBA (but only by trial and error and lots of Google assistance.

I'm attempting to use the code below to copy and paste data from column F of sheet3 (if A1 on the FTW cell match names in column F of sheet3) to A9, A10 and so forth on the FTW sheet. I do not want to replace or copy over any name that is already there. Issue is I'm getting 2 separate errors. I'm getting a compile error and if I can fix that I'm getting an "out of range" error for WITH Sheets("ftw")

Below is what I'm attempting to use:

Sub MOVE_active_reps2()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim f As Long, cls
cls = Range("F2:F20000")
Dim v As Variant

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("sheet3")
Set targetSheet = ThisWorkbook.Worksheets("ftw")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "f").End(xlUp).Row

' Loop through each row in the source sheet
For i = 6 To lastRow
' Check if cell in column f contains "office name"
If sourceSheet.Cells(i, "f").Value = "fort worth" Then
v = sourceSheet.Cells(i, "a").Value
' Check whether value has already been copied
If targetSheet.Range("a:a").Find(What:=v, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then

End If

With Sheets("ftw")
LR = WorksheetFunction.Max(9, .Range("A" & Rows.Count).End(xlUp).Row + 1)
For f = LBound(cls) To UBound(cls)
Range(cls(i)).Copy Destination:=.Cells(LR, i + 1)
Next f
End With

End If
End Sub

 

[Joe4]

Welcome to the Board!

I see a lot of issues around "cls". It looks like it is supposed to be a range.
So where you have this:

Dim f As Long, cls
cls = Range("F2:F20000")

you should have:

Dim f As Long, cls as Range
Set cls = Range("F2:F20000")

(you always need to use "Set" when setting range variables and other object variables).

And then when you get down here:

With Sheets("ftw")
    For f = LBound(cls) To UBound(cls)
        Range(cls(i)).Copy Destination:=.Cells(LR, i + 1)
    Next f
End With

you should probably do it this way to loop through those cells:

Dim cell as Range
With Sheets("ftw")
    For each cell in cls
        cell.Copy Destination:=.Cells(LR, i + 1)
    Next cell
End With

Note that I have not tried to evaluate the logic of your code, as without seeing what your data looks like and expected result, I am not 100% clear on what you are trying to do.
I am just calling out some coding syntax issues I see.

 

[ah2024_2024]

Lordy, thank you for some clarify and breakdown. You have know idea how helpful that is when trying to continue to learn. I did make those tweaks you suggested and now am getting a FOR WITOUT NEXT compile error with END SUB highlighted. Below is what I updated to along with a few test screenshots.

Sub MOVE_active_reps2()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim f As Long, cls
Set cls = Range("F2:F20000")
Dim v As Variant

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("sheet3")
Set targetSheet = ThisWorkbook.Worksheets("ftw")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "f").End(xlUp).Row

' Loop through each row in the source sheet
For i = 6 To lastRow
' Check if cell in column f contains "office name"
If sourceSheet.Cells(i, "f").Value = "fort worth" Then
v = sourceSheet.Cells(i, "a").Value
' Check whether value has already been copied
If targetSheet.Range("a:a").Find(What:=v, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then

End If
Dim cell As Range
With Sheets("ftw")
For Each cell In cls
cell.Copy Destination:=.Cells(lr, i + 1)
Next cell

End With

End If
End Sub

This is my source sheet (sheet3) and if column F matches A1 of my destination sheet (FTW) I want the cell from column D (OF SOURCE SHEET) to copy into A9 down (ON THE DESTINATION SHEET).

This is my destination sheet

 

[Joe4]

You are missing the

Next i

line you need to close out this line:

For i = 6 To lastRow

The missing line should probably go just before the "End Sub" line.

 

[ah2024_2024]

I'm going to get this at some point. I did close out the line and am no longer getting the compile error. So, thanks for the teaching lesson! Let's see if I can use my words better for what I'm needing because I think I've got something mixed up.

On SHEET3 there is a list of names (column E) along with what city they belong to (column F). If column F if the city name matches cell A1 of the city tab (in this case the tab is called FTW) then I need to copy the names from column E and paste into the first blank cell starting with A9

So, this screen shot is my source sheet (sheet3) and see how in column F the city FORT WORTH appears? Then I need the name in column E to copy and paste to the next blank cell starting with A9 of my destination sheet (FTW - screenshot 2)

Here is what I've updated it to that clearly I've botched and I think it's because I don't have anything that says COPY something or another...........
Sub MOVE_active_reps2()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim f As Long, cls
Set cls = Range("f2:f20000")
Dim v As Variant

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("sheet3")
Set targetSheet = ThisWorkbook.Worksheets("FTW")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "f").End(xlUp).Row

' Loop through each row in the source sheet
For i = 6 To lastRow
' Check if cell in column f contains "office name"
If sourceSheet.Cells(i, "f").Value = "FORT WORTH" Then
v = sourceSheet.Cells(i, "a").Value
' Check whether value has already been copied
If targetSheet.Range("a:a").Find(What:=v, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then

End If
Dim cell As Range
With Sheets("ftw")
For Each cell In cls
cell.Copy Destination:=.Cells(lr, i + 1)
Next cell

End With

End If
Next i
End Sub

 

[Joe4]

When posting your VBA code, please use the VBA code tags! It keeps all formatting and makes VBA code much more readable.
It is quite simple. There are instructions on how to do that here: How to Post Your VBA Code

I see another issue with this line of code:

cell.Copy Destination:=.Cells(lr, i + 1)

The issue is this variable "lr". You have not set it to be anything in your code! So its value is 0.
Excel obviously cannot place any value in row 0, since row 0 does not exist.

I am guessing maybe it is a last row variable, but you have not calculated it anywhere in your code!

However, I don't think VBA is even necessary here. Since you are using Excel 365, you can do what you want with the new FILTER function (see: FILTER Function).

So if Sheet3 looks like this:

You can populate column A of your "ftw" sheet like this:

simply by placing his formula in cell A9 of that sheet:

=FILTER(Sheet3!E2:E7,Sheet3!F2:F7=ftw!A1,"")

 

[ah2024_2024]

I had tried something similar and got a spill error.

 

[Joe4]

Spill formulas automatically fill up the range with all the matches. So you only put the formula in the first cell and then it returns all the answers in that cell and in the cells below it,

If you get a spill error, that means you already have something in on the cells it is trying to return the values in. Clear enough room for all the answers.