RT Error 424 - Object Required, when UNIONing 2 ranges in order to sync 2 columns

[new2code]

Hello All,

In a nutshell, I'm trying to filter one column ("Emp Cont Type") for two values ("Regular" and "Term"), and then have those values copied over to a second column ("Employment Type Indicator"), for the exact same set of filtered data.

Now, we all know that doing a straight "copy and paste" of filtered data does/will not work.

So, I found out that one way to achieve the required results is as follows:

• Apply the required filter
• Select the entire source column
• Then select the target column
• Then perform a "Fill Right" or "Fill Left" depending on which side the target column lies

My code below is doing exactly the above, however I find that when I run the code from a sheet "other than the one that contains the data" it bombs out with a "Runtime error 424 - Object required". The error message is against the line:

Set rngToCopy = .Range(colLetter & "2:" & colLetter & lastRow)

On the other hand, if I physically activate/select the data sheet and run the code it works just fine (no error, and data gets copied across correctly)
What I do want to achieve is to have the end-user click a button from a menu and have the code do its job (transparent to the user/in the background)...without the user having to see that a data sheet is being selected, and all kinds of things happening to the data on that sheet, and then being returned to the "menu" sheet.

Is there any way I can either fix the error, or perhaps even achieve my desired result a different way? Perhaps I'm over-complicating things!

Thanks.

Sub syncTwoColumns()
    Dim i, columnCount As Integer
    
    Dim aCell1, aCell2, aCell3 As Range
    Dim col1, col2, col3 As Long, Lrow As Long
    Dim ColName1, ColName2, ColName3 As String
    Dim colNumber1, colNumber2, colNumber3 As Integer
    
    Dim colNumber As Integer
    Dim colLetter As String
    Dim rngToCopy, rngToUpdate, unionedRange As Range
    Dim SearchRng As Range, myCell As Range
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim lastRow, lastColumnNumber As Long
    lastRow = ActiveSheet.UsedRange.Rows.Count
    lastColumnNumber = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim lastColumnLetter As String
    lastColumnLetter = ReturnName(lastColumnNumber)
    
    On Error Resume Next
    ws.Range("A1:" & lastColumnLetter & "1").SpecialCells(xlCellTypeBlanks).Value = "*** TEMP ***"
    On Error GoTo 0
    
    With ws
        .Range("A1").AutoFilter
        
        Set SearchRng = .Range("A1:" & lastColumnLetter & "1")
    
        For Each myCell In SearchRng
            If InStr(1, UCase(myCell.Value), UCase("emp cont type")) > 0 Then
                colLetter = Split(myCell.Address(1, 0), "$")(0)
                
                colNumber1 = wColNum(colLetter)   '<-- Call function to return Column Number of found cell
                
                ' Now filtering on the column
                .UsedRange.AutoFilter field:=colNumber1, Criteria1:= _
                    "=Regular Seasonal", Operator:=xlOr, Criteria2:="=Term PWU Referral"
            
                Set rngToCopy = .Range(colLetter & "2:" & colLetter & lastRow)
            
                Exit For
            End If
        Next myCell
        For Each myCell In SearchRng
            If InStr(1, UCase(myCell.Value), UCase("employment type indicator")) > 0 Then
                colLetter = Split(myCell.Address(1, 0), "$")(0)
                
                colNumber1 = wColNum(colLetter)   '<-- Call function to return Column Number of found cell
                
                Exit For
            End If
        Next myCell
        On Error Resume Next
        Set rngToUpdate = .Range(colLetter & "2:" & colLetter & lastRow)
        On Error GoTo 0
            
        '--------------------------------------------------------------------------
        ' Setting and selecting the two non-contiguous cell ranges,
        ' and then copying the filtered contents over from column named "emp cont type" to
        ' column named "employment type indicator"
        
        If unionedRange Is Nothing Then
            Set unionedRange = Union(rngToCopy, rngToUpdate)
        End If
        unionedRange.FillRight      ' This (FillRight) is a hack/work-around to copying/pasting from-and-to a filtered list, which doesn't work
        
        '''unionedRange.Select
        '''Selection.FillRight     ' This is a hack/work-around to copying/pasting from-and-to a filtered list, which doesn't work
        Range("A1").Select
        
        .ShowAllData
    End With
    
'    Call settingsON
End Sub

[SamT]

Read all my posts below before proceeding.

lastRow = ActiveSheet.UsedRange.Rows.Count <<<

Set ws = ThisWorkbook.Sheets("Sheet1") 

With Ws

colLetter = Split(myCell.Address(1, 0), "$")(0)

Set rngToCopy = .Range(colLetter & "2:" & colLetter & lastRow) 'LastRow of Active sheet, not of Ws

End With

BTW

colNumber1 = wColNum(colLetter) '<-- Call function to return Column Number of found cell

can be replaced with

colNumber1 = myCell.Column

.

.

I would replace

      Set SearchRng = .Range("A1:" & lastColumnLetter & "1") 
         
        For Each myCell In SearchRng 
            If InStr(1, UCase(myCell.Value), UCase("emp cont type")) > 0 Then 
                colLetter = Split(myCell.Address(1, 0), "$")(0) 
                 
                colNumber1 = wColNum(colLetter) '<-- Call function to return Column Number of found cell
                 
                 ' Now filtering on the column
                .UsedRange.AutoFilter field:=colNumber1, Criteria1:= _ 
                "=Regular Seasonal", Operator:=xlOr, Criteria2:="=Term PWU Referral" 
                 
                Set rngToCopy = .Range(colLetter & "2:" & colLetter & lastRow) 
                 
                Exit For 
            End If 
        Next myCell

With

Set rngToCopy = .Rows(1).Find("emp cont type").EntireColumn
' And repeat for rngToUpdate
Set rngToUpdate = .Rows(1).Find("employment type indicator").EntireColumn

Finally, replace the Fill Right part with

If Not rngToCopy is Nothing And Not rngToUpdate Is Nothing Then _
Union(rngToCopy, rngToUpdate).FillRight

[SamT]

In regards to your Declarations

Dim aCell1, aCell2, aCell3 As Range 
    Dim col1, col2, col3 As Long, Lrow As Long
    Dim ColName1, ColName2, ColName3 As String
    Dim colNumber1, colNumber2, colNumber3 As Integer

All the above, except the last variable in each line is a Variant Type Variable.
If you use one line declarations, you must still Type each variable

Dim aCell1 As Range , aCell2 As Range , aCell3 As Range

Always Type Row and Column Numbers as Longs. Intgers can't handle the large numbers

[SamT]

This does the same as your code. Whether it does what you want or not, well . . .

Option Explicit

Sub VBAX_syncTwoColumns()
    Dim i, columnCount As Integer
     
   Dim rngToCopy As Range, rngToUpdate As Range
     
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
     
     
    On Error Resume Next
    ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = "*** TEMP ***"
    On Error GoTo 0
     
    With ws
        .Range("A1").AutoFilter
        Set rngToCopy = .Rows(1).Find("emp cont type").EntireColumn
        Set rngToUpdate = .Rows(1).Find("employment type indicator").EntireColumn
         
        If Not rngToCopy Is Nothing And Not rngToUpdate Is Nothing Then _
        Union(rngToCopy, rngToUpdate).FillRight
        
        Range("A1").Select
         
        .ShowAllData
    End With
End Sub

[new2code]

Wow SamT!!!

Thank you so much for your quick (and very comprehensive and helpful reply). I'm truly amazed at your skill and knowledge with VBA. I'm not such an expert, as you can see :-)

I'm at home now (left early for the long weekend - Thanksgiving in Canada), so I will have to try this out on Tuesday, and I'll post back if I have any issues or further questions.

Later.

[new2code]

OH MY GOSH!!!

I tried this on a small mock-up/simulated worksheet at home....and IT ACTUALLY WORKS...IT REALLY DOES WORK!!!

I cannot believe how much you pared down/optimized the code and it still works so well!

Wow, you truly are a genius.

Thank you Sir! Appreciate you help and knowledge-transfer.

Run-time error 91 - Object variable or With block variable not set

[SamT]

Run-time error 91 - Object variable or With block variable not set

???

[new2code]

???

Sorry...please ignore that "run-time error 91..." line, that somehow crept into my reply.

But anyways, I do have one more thing to point-out and ask:

So, when I run your code as-is, the entire column (source) is being copied - including the column title/header - (by means of the "fill-right" statement). I would like for only the data (starting from row 2, and going down to the very last row) to be copied, from source column to target column.

Now, one way I can easily/temporarily resolve that is by:

• creating a memvar to store the existing value of the target header/title to it
• copying the existing value to the memvar, just before the "fill-right" statement
• moving the stored value back to the row 1 cell of the target column after the "fill-right" statement

But that means 3 lines of extra code + and additional memvar...which I'm sure can be easily avoided, but for the life of me I cannot figure out how to achieve it.

I believe the solution might be something simple enough, and perhaps have something to do with using OFFSET (or something similar)...which I've tried but coluldn't get it to work.

Your help in this regard would once again be greatly appreciated.

Thanks again.

[SamT]

I KNEW I forgot something.

 
Union(rngToCopy, rngToUpdate).Offset(1).FillRight

When I read the Specs, I knew then I had to offset the Range.

You can restrict it to just the used range of data + 1 empty Row

 
Intersect(Union(rngToCopy, rngToUpdate), UsedRange).Offset(1).FillRight

But the extra operation didn't seem necessary to me.

You can lose the empty row by Resizing the Intersection with .Resize(UsedRange.RowsCount - 1). Whoopie!

[new2code]

I KNEW I forgot something.

 
Union(rngToCopy, rngToUpdate).Offset(1).FillRight

You can restrict it to just the used range of data + 1 empty Row

 
Intersect(Union(rngToCopy, rngToUpdate), UsedRange).Offset(1).FillRight

So, I tried both of the above suggestions verbatim, and I seem to be getting an error with the first one, and the second one doesn't seem to be doing anything i.e. no data is being copied from the source column to the target column. I guess I'm just too thick today.

So the first suggestion gives me a Run-time error 1004 - Application-defined or object-defined error...against the line "Union(rngToCopy, rngToUpdate).Offset(1).FillRight". The Offset(1) bit seems to be the issue, since without that it works fine.

Is there something I'm missing?

[SamT]

I think I missed a ")" before ".Offset"

Go to the VBA Editor menu; Tools >> Options >> Editor Tab and check every box in the "Code Settings" Frame.

Then, on the "General Tab, check "Show Tool Tips," "Break On All Errors," and "Compile On Demand."

Then Click the Menu item Debug >> Compile.

[new2code]

I think I missed a ")" before ".Offset"
.

It doesn't look like it's that (i.e. a missing closing round bracket)...since the editor itself is not allowing me to add an extra ")" after either of the two suggestions.

[new2code]

Never mind....I figured it out....and you know what the problem was?

Your second suggestion (code below) of "restricting it to just the used range of data + 1 empty Row" had a bit of a "typo", in that the "UsedRange" portion was missing a "." before it.

Intersect(Union(rngToCopy, rngToUpdate), UsedRange).Offset(1).FillRight

Once I added in the "." it works like a charm.

Thanks (a million times over) for your time, patience, and help in guiding me to put this issue to bed, and for teaching me a few things along the way as well.

I hope I won't have any issues in implementing this code in my real file at work.

Anyways, you have a good one!

[SamT]

I am a terrible typist.

On the poli-jive forum I go to, it takes me as long to typo-proof my posts as it does to write them.

Finding typos in my code is good practice fer ya.

If I wrote code in the VBA editor, you wouldn't get any practice, would you?