Макросы ошибка 438 - Решение и исправление самых разных ошибок на TopOshibok.ru

Andrushka252

0 / 0 / 0

Регистрация: 26.10.2019

Сообщений: 8

Excel

26.10.2019, 16:52. Показов 11327. Ответов 13

Метки нет (Все метки)

Добрый день,написал макрос для копирования информации с одного листа на другой,но он не работает (
Помогите пожалуйста,я в этом деле чайник

Вот сам макрос:

Visual Basic

Sub копи()
 
AllRecs = Application.WorksheetFunction.CountA(Sheets("табл2").Range("B:B"))
cAllRecs = Application.WorksheetFunction.CountA(Sheets("лист3").Range("B:B"))
    For CurRec = Лист3 To AllRecs
    AllCrit = Sheets("табл2").Cells(CurRec, 2)
        For cRecs = Лист3 To cAllRecs
           CheckKrit = Sheets("табл3").Cells(cRecs, 9)
    If AllCrit = CheckKrit Then
      Sheets("лист3").Cells(cRecs, 6) = Sheets("табл2").Cells(CurRec, 1)
      Sheets("лист3").Cells(cRecs, 7) = Sheets("табл2").Cells(CurRec, 3)
    End If
   Next cRecs
 Next CurRec
End Sub

Миниатюры

6919 / 2829 / 543

Регистрация: 19.10.2012

Сообщений: 8,644

26.10.2019, 17:09

Копать отсюда и до обеда?
С какого в коде присутствует Лист3?

0 / 0 / 0

Регистрация: 26.10.2019

Сообщений: 8

26.10.2019, 17:19

[ТС]

Это название страницы

Добавлено через 1 минуту
Hugo121,могу поменять,это важно?

Hugo121

6919 / 2829 / 543

Регистрация: 19.10.2012

Сообщений: 8,644

26.10.2019, 17:22

Поясните — что хотели сказать тут:

Visual Basic

1	For cRecs = Лист3 To cAllRecs

P.S. Вообще это не название страницы. Это теоретически могла бы быть переменная, содержащая название страницы — но это не тот случай…

6919 / 2829 / 543

Регистрация: 19.10.2012

Сообщений: 8,644

26.10.2019, 17:27

Данных вообще сколько строк? Потому что если их много — это всё желательно делать иначе (это я не про ошибку про ошибку, которая правда пока и не особо понятно на какой строке ).

Добавлено через 1 минуту

Сообщение от Andrushka252

могу поменять,это важно?

— да не, пишите код как хотите

0 / 0 / 0

Регистрация: 26.10.2019

Сообщений: 8

26.10.2019, 17:28

[ТС]

3 столбца и там и там,а строк может быть сколько угодно,но да их много

6919 / 2829 / 543

Регистрация: 19.10.2012

Сообщений: 8,644

26.10.2019, 17:32

Сообщение от Andrushka252

я брал за образец макрос из этого форума

— и откуда тогда взялось в коде Лист3 в том месте?

Не по теме:

Я пока пас, ушёл…

0 / 0 / 0

Регистрация: 26.10.2019

Сообщений: 8

26.10.2019, 17:35

[ТС]

Ну если в том случае переносится информация с листа 1 на 2,то в моём с Табл2 на лист3.
Как по мне,всё логично,но я повторюсь,что я чайник.

209 / 184 / 43

Регистрация: 02.08.2019

Сообщений: 586

Записей в блоге: 23

26.10.2019, 17:50

Andrushka252, Привет! выложи файл, и опиши что хочешь сделать?

0 / 0 / 0

Регистрация: 26.10.2019

Сообщений: 8

26.10.2019, 18:57

[ТС]

я хочу совместить эти листы по номеру азс,номеров азс может быть до тысячей,и для ускорения работы мне нужен макрос

0 / 0 / 0

Регистрация: 26.10.2019

Сообщений: 8

26.10.2019, 19:01

[ТС]

art1289, Вот листы

6919 / 2829 / 543

Регистрация: 19.10.2012

Сообщений: 8,644

26.10.2019, 21:39

Сообщение от Andrushka252

Вот листы

— я вернулся. А файла так всё ещё и нет… Есть мутные картинки, с которых кто-то должен перерисовать инфу в файл.
Вообще вроде как должно хватить записи в макрос применения ВПР() (не вникал, ибо описание ТЗ нет), но если и впрямь тысячи — то лучше конечно макрос на словаре.

viktor424

0 / 0 / 0

Регистрация: 31.10.2019

Сообщений: 1

31.10.2019, 20:03

Visual Basic

' Кто автор не помню но макрос работает 
 
Option Explicit
Option Base 1
 
Sub СамиМыНеМестные() ' в смысле - сбор данных с нескольких листов '
    Const ColName As String = "A"
    Const FirstRow As Long = 1
    Const M As Long = 2 ' Columns.Count '
    '
    Dim Sh As Worksheet, Rng As Range
    Dim Arr1, TranspArr(), TotalArr()
    Dim LastRow As Long
    Dim N As Long, N1 As Long
    Dim I As Long, J As Long, K As Long
    Dim A As String
    '
    Application.ScreenUpdating = False
    For Each Sh In ThisWorkbook.Worksheets
        GoSub RoutineGetRng
        A = Rng.Address(0, 0)
        If Sh.Name = "Сборка" Then
            Rng.ClearContents
        Else
            Arr1 = Rng.Value
            N = N + N1
            ReDim Preserve TranspArr(M, N)
            For I = 1 To N1
                K = N - N1 + I
                For J = 1 To M
                    TranspArr(J, K) = Arr1(I, J)
                Next
            Next
        End If
    Next
    ReDim TotalArr(N, M)
    For J = 1 To M
        For I = 1 To N
            TotalArr(I, J) = TranspArr(J, I)
        Next
    Next
    Set Sh = Sheets("Сборка")
    Sh.Range(ColName & FirstRow).Resize(N, M).Value = TotalArr
    Application.ScreenUpdating = True
    Exit Sub
RoutineGetRng:
    With Sh
        LastRow = .Cells(.Rows.Count, ColName).End(xlUp).Row
        Set Rng = Range(.Cells(FirstRow, ColName), .Cells(LastRow, ColName)).Resize(, M)
    End With
    N1 = Rng.Rows.Count
    Return
End Sub

Вложения

Сбор данных с нескольких листов.xls (34.5 Кб, 22 просмотров)

Источник

I have this code in which I’ve been getting help with a bit, but I’ve run into an issue, or what I think is an issue. The last lookup, I am being told that the object doesn’t support this property or method. I know it’s probably something easy, but my brain is smoldering. I’d like some help if someone knows the answer of why this is happening.

Thanks.

Option Explicit

Sub Update_Dakota()

    Dim wsDAO As Worksheet              'Dakota OOR
    Dim wsDAD As Worksheet              'Dakota Data
    Dim wsDAR As Worksheet              'Dakota Archive
    Dim wsPOR As Workbook               'New Workbook
    Dim lastrow As Long, fstcell As Long
    Dim strFile As String, NewFileType As String, filename As String

    Set wsDAO = Sheets("Dakota OOR")
    Set wsDAD = Sheets("Dakota Data")
    Set wsDAR = Sheets("Dakota Archive")


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    lastrow = wsDAD.Range("B" & Rows.Count).End(xlUp).Row + 1

    With wsDAD
        .Range("I2").Formula = "=COUNTIFS('Dakota OOR'!$B:$B,$A2,'Dakota OOR'!$D:$D,$C2, 'Dakota OOR'!$G:$G,$F2)"
        .Range("J2").Formula = "=IF(I2,""Same"",""Different"")"
        wsDAD.Range("I2:J2").Copy wsDAD.Range("I3:J" & lastrow)
        wsDAD.Range("I:J").Calculate
    End With


    strFile = Application.GetOpenFilename()
    NewFileType = "Excel Files 2007 (*.xls)"
    Set wsPOR = Application.Workbooks.Open(strFile)
    lastrow = wsPOR.Range("A" & Rows.Count).End(xlUp).Row + 1

    wsPOR.Range("A2:G" & lastrow).Select


End Sub

asked Sep 5, 2012 at 19:40

The Error is here

lastrow = wsPOR.Range("A" & Rows.Count).End(xlUp).Row + 1

wsPOR is a workbook and not a worksheet. If you are working with «Sheet1» of that workbook then try this

lastrow = wsPOR.Sheets("Sheet1").Range("A" & _
          wsPOR.Sheets("Sheet1").Rows.Count).End(xlUp).Row + 1

Similarly

wsPOR.Range("A2:G" & lastrow).Select

should be

wsPOR.Sheets("Sheet1").Range("A2:G" & lastrow).Select

answered Sep 5, 2012 at 19:46

Siddharth RoutSiddharth Rout

147k17 gold badges206 silver badges250 bronze badges

Источник

Есть файл, работающий уже лет 7. На странице кнопка, при открытии проверяется контрольное поле и в зависимости от этого кнопка активна или нет (.Enable=True or False). Файл работал в нескольких версиях оффиса. Последнее время на 2010 и 2013 (основная версия). В результате последнего апдейта оффиса стало появляться сообщение, приведенное в описании темы. Кроме того, ранее в режиме конструктора можно было просмотреть свойства объекта (кнопки, к примеру) на листе, теперь же вызываются свойства листа, содержащего объект.
Проверил на соседней машине, оффис 2010 СП2 без последних апдейтов — все ОК. Выполнил обновление — картина та же, что описана выше…

Кто-нить сталкивался с подобной проблемой? Спасибо!

ЗЫ: Кстати, подобную проблему нашел в архиве за 2012, но на ХР у меня не возникало подобных ошибок…

http://www.planetaexcel.ru/forum/?PAGE_NAME=read&FID=8&TID=42275

Источник

Use the right properties and methods for the selected object

by Ola-Hassan Bolaji

A Computer Engineering graduate, he has spent most of his life reading and writing about computers. He finds joy in simplifying complex topics into simple solutions for PC… read more

Updated on June 13, 2023

The runtime error 438 occurs if the property or method you are trying to use does not apply to the selected object.
You can fix this issue by uninstalling the Microsoft Works add-in.
Another effective solution is to remove your third-party antivirus.

The runtime error 438 error on Microsoft Visual Basic (VBA) is one of the most annoying issues users encounter. Like other runtime errors like 3706, it stops you from executing your code on VBA.

Fortunately, as daunting as the issue looks, it is not the most difficult to fix, as shown in the ensuing sections of this detailed guide.

What does object doesn’t support property or method mean in Excel?

There are different objects you can use in VBA, and they all have properties and methods that are compatible with them.

However, if you try to use a property or method that is not supported by a particular method, you get the VBA runtime 438, which denotes that the property you are trying to use does not apply to the select object.

It must also be noted that this can sometimes be a false positive from a third-party antivirus. So, if you are sure you selected the correct property and method, you might need to disable or uninstall your antivirus.

How can I fix runtime error 438?

1. Use the right properties and methods

The easiest way to fix runtime error 438 is to use the properties and methods supported by the object you are using. If you can’t determine this yourself, press the dot after defining an object.

This will show the list of all properties and methods compatible with it. Another option is to press F2 to bring up the object browser. This will also list the properties that are applicable to an object.

2. Uninstall the Microsoft Works add-in

Press the Windows key + R, type control, and click the OK button.
Select Uninstall a program under the Programs option.
Now, right-click Microsoft Works and select the Uninstall option.
Finally, follow the onscreen instructions to complete the process and try running your VBA code again.

In some cases, the runtime error 438 might be due to issues with some of your Excel add-ins, like Microsoft Works. The best thing to do here is to uninstall these plugins and check if the error persists.

Read more about this topic

Fix: Keyboard Shortcuts are Not Working in Excel
Excel Keeps Crashing: 5 Simple Ways to Fix

3. Scan for malware

Press the Windows key, type virus, and select Virus & threat protection.
Click the Scan options link.
Now, select your preferred scan option.
Finally, click the Scan now button.

If you are sure that your wrong properties or methods are not the cause of the runtime error 438, then it might be due to the activity of viruses on your PC.

You should use the Full Scan option or Microsoft Defender (Offline) to get rid of them. Alternatively, you can use a dedicated antivirus in ESET NOD32.

This special software will scan the most hidden corner of your PC and eliminate even the most stubborn malware before it can cause problems.

4. Reinstall drivers

Press the Windows key + X and select the Device Manager option.
Double-click any of the sections to expand it, and right-click each of the devices under it.
Now, select the Uninstall device option.
Finally, click the Uninstall button to confirm the action.

Some users have found this runtime error 438 due to problems with drivers for devices like your display adapter. To fix this, uninstall the affected driver and restart your PC.

5. Uninstall third-party antivirus

Press the Windows key + I and select the Apps option in the left pane.
Select the Apps & features option.
Now, click the vertical dots before the antivirus.
Finally, select the Uninstall option and follow the onscreen instructions to complete the process.

At times, the runtime error 438 might be a false alarm by your third-party antivirus. Also, the security software might be messing with your code for some reason.

In this case, you must uninstall the antivirus and use Microsoft Defender instead.

With this, we can now conclude this detailed guide on the VBA runtime error 438. All that is left for you is to follow the instructions therein, and the issue should be history.

If you are facing other issues like runtime error 3709, check our detailed guide to fix it quickly.

Feel free to let us know the solution that helped you fix this issue in the comments below.

Источник

Return to VBA Code Examples

This article will demonstrate how to Fix VBA Error 438 – Object Doesn’t Support Property or Method.

Error 438 is a frequently occuring error in Excel VBA and is caused by either omitting a property or method when referring to an object, or by using a property or method that is not available to an object in the VBA code.

Check the VBA code to Fix Error 438

Let us examine the following code:

Sub RemoveWorksheet()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim sheetName As String
  sheetName = "Sheet 1"
  Set wb = ActiveWorkbook
  For Each ws In wb.Sheets
     If ws = sheetName Then
        wb.Sheets(sheetName).Delete
        Exit For
     End If
  Next ws
End Sub

If we run this code, Error 438 will occur.

To resolve this issue, we click on Debug to see where the error is occurring.

This line of code is trying to equate the worksheet (depicted by the variable ws) to the sheet name. This is not possible as the worksheet is an object but the sheet name is a string so Error 438 is therefore returned.

To solve this, compare the string sheetName to the name property of the worksheet object:

ws.name = sheetName

Now the code runs without issue!

To show a list of all the properties or methods that are available to the worksheet object, we can type a dot (.) after the object.

This invokes the VBA Editor’s Intellisense feature. To make sure it’s turned on, in the Menu, select Tools > Options.

Make sure Auto List Members is checked and then click OK.

NOTE: This is usually on by default.

You can also find a list of all the Properties, Methods and Events for an Excel Object in the Object Browser.

In the Menu, select View > Object Browser or press F2 (See more VBE shortcuts).

A list of classes and objects will appear in the left hand side of the screen. If you click on the object you wish to use (eg: Workbook), a list of all the Properties, Methods and Events that that object supports will appear in the right hand side of the screen. (eg: Members of Workbook).

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!

Источник