2 Replies - 266 Views - Last Post: 14 December 2019 - 05:48 AM Rate Topic: -----

#1 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Getting runtime error in vba code!

Posted 13 December 2019 - 07:56 PM

I've created a simple VBA macro which opens a workbook and calls a function(which unmerges merged cells and fills them with value). But when I run it gives a error on the line Rey (ws)

Quote

Run-time error '438':
Object doesn't support this property or method

my code:
Sub Kylo()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = Application.Workbooks.Open("D:\testbook.xlsx")
Set ws = wb.Sheets(1)
Rey (ws)

End Sub

Function Rey(sht As Worksheet)
For Each x In sht.UsedRange.Cells
  If x.MergeCells Then
    myValue = Empty
    myFormat = Empty
    Set mergedrange = x.MergeArea
    mergedrange.UnMerge
    For Each y In mergedrange.Cells
      If IsEmpty(y.Value) Then
        y.Value = myValue
        y.NumberFormat = myFormat
      Else
        myValue = y.Value
        myFormat = y.NumberFormat
      End If
    Next y
  End If
Next x
End Function



What do I need to do to fix this?

This post has been edited by Whateva_: 13 December 2019 - 07:57 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Getting runtime error in vba code!

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,345
  • Joined: 12-December 12

Re: Getting runtime error in vba code!

Posted 14 December 2019 - 03:11 AM

It doesn't need to be a function because it does not return a value. You might as well switch it to a sub.

(Also note that you would not be able to use such a function in a worksheet cell because it manipulates the Excel environment.)
Was This Post Helpful? 0
  • +
  • -

#3 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Re: Getting runtime error in vba code!

Posted 14 December 2019 - 05:48 AM

If I change it to Sub, it still shows the same error. So what's the solution? I'm trying to avoid the Activate method to select the specific workbooks worksheet and changing the sht to ActiveSheet in the function... :helpsmilie:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1