PDA

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



new2code
10-07-2016, 12:05 PM
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
10-07-2016, 02:24 PM
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
10-07-2016, 02:37 PM
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
10-07-2016, 02:45 PM
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
10-07-2016, 03:17 PM
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
10-07-2016, 03:39 PM
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
10-07-2016, 06:24 PM
Run-time error 91 - Object variable or With block variable not set
???

new2code
10-08-2016, 05:19 AM
???

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
10-08-2016, 08:16 AM
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. :jail:

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
10-08-2016, 01:17 PM
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
10-08-2016, 04:03 PM
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
10-09-2016, 08:18 AM
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
10-09-2016, 08:34 AM
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
10-09-2016, 08:42 AM
I am a terrible typist. :crying:

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? :devil2: