Error 2042 vba как исправить - Исправление недочетов и поиск решений вместе с Examum.ru

I have Column A:

+--+--------+
|  |  A     |
+--+--------+
| 1|123456  |
|--+--------+
| 2|Order_No|
|--+--------+
| 3|    7   |
+--+--------+

Now if I enter:

=Match(7,A1:A5,0)

into a cell on the sheet I get

As a result. (This is desired)

But when I enter this line:

Dim CurrentShipment As Integer
CurrentShipment = 7
CurrentRow = Application.Match(CurrentShipment, Range("A1:A5"), 0)

CurrentRow gets a value of «Error 2042»

My first instinct was to make sure that the value 7 was in fact in the range, and it was.

My next was maybe the Match function required a string so I tried

Dim CurrentShipment As Integer
CurrentShipment = 7
CurrentRow = Application.Match(Cstr(CurrentShipment), Range("A1:A5"), 0)

to no avail.

Источник

Need some help to figure out strange VBA VLOOKUP Type Missmatch error. The code is really simple, since sss0 is a random number and all I want is to find closest value in a range (sheet ‘BMD_CDF’, Range(«A2:B999»)). In the spreadsheet, I set format for Sheets(«BMD_CDF»).Range(«A2:B999») to scientific already…

Dim LookUp_Range As Range
Dim sss0 As Double
Set LookUp_Range = Sheets("BMD_CDF").Range("A2:B999")

sss0=Application.WorksheetFunction.Max(Rnd(), 0.005)
Debug.Print Application.VLookup(sss0, LookUp_Range, 2, 0)

ERROR MSG

What Range looks like

asked Oct 4, 2017 at 23:47

The Error 2042 («N/A») seems to be caused by the fact that the value returned by the Excel Worksheet function:

Aplication.WorksheetFunction.Max(Rnd(), 0.005)

which is always less than 1 will never get into specified range of values (>6) in column A. For testing purpose, try to substitute it with any number in that range of values in Column A, for example, sss0 =6.15 and modify the VLOOKUP() statement as following:

Debug.Print Application.VLookup(sss0, LookUp_Range, 2, 1)

(where 1 stands for logical TRUE) to get it working, i.e. finding the closest value (not exact match) as per your definition.

Hope this may help.

answered Oct 5, 2017 at 0:42

Alexander BellAlexander Bell

7,8243 gold badges26 silver badges42 bronze badges

Источник

Во второй строке #Н/Д, этот код запинается и выдает ошибку, как сделать так что бы действия в положительном блоке IF производились, или формулируя по другому, как сделать так что бы код выделял #Н/Д цветом?

Sub Oshibka()
For i = 2 To 27
iValue = Cells(i, 2).Value ‘Что такое Error 2042
If Cells(i, 2).Value = «#Н/Д» Then
Cells(i, 2).Interior.ColorIndex = 35
End If
Next i
End Sub

	блин две темы получилось, думал эта не опубликовалась. Не сочтите за флуд, народ помогите.

	Может я чего недостаточно пояснил? Скажите, так я поясню. Очень нужна помощь.

A_Zeshko Пользователь Сообщений: 116 Регистрация: 01.01.1970	Sub Oshibka() For i = 1 To 27 iValue = Cells(i, 2).Text If iValue = «#Н/Д» Then Cells(i, 2).Interior.ColorIndex = 35 End If Next i End Sub At odd moments: VBA, VB6, VB.NET, Java, Java for Android, Java Script, Action Script, Windows Scriping Host

	Спасибо за ответ! А можно как то определить, например, что если Cells(i, 2) — ошибка, то выполняем условие?

New Пользователь Сообщений: 4657 Регистрация: 06.01.2013	Sub Макрос1() Dim Rng As Range On Error Resume Next Set Rng = Columns(«B:B»).SpecialCells(xlCellTypeFormulas, 16) If Not Rng Is Nothing Then Rng.Interior.ColorIndex = 35 End Sub

New Пользователь Сообщений: 4657 Регистрация: 06.01.2013	Можно, конечно, и так, но это дольше, чем мой первый вариант (если ячеек много) Sub Макрос1() Dim i& For i = 1 To 27 If IsError(Cells(i, 2)) Then Cells(i, 2).Interior.ColorIndex = 35 Next i End Sub

Lant Гость	#8 29.11.2008 18:03:27 О! Павел то что нужно, спасибо!

Источник

I am writing a vba program to combine 3 worksheets in to a separate worksheet. The object is to take these three worksheets, in separate workbooks, that are being used as data entry, combine the unique rows in to a repository worksheet, in a 4th workbook, and produce metrics from the repository.

I have created a program that opens the repository workbook, loads the contents in to an array, then loops through the other 3 workbooks one at a time, loads their contents in to an array, complare key cells in each rows to key cells in the repository array and add unique rows to the repository.

The program seems to work through the 1st work sheet, but when processing either the second or third worksheet (it is not consistent) the program gets a Type Mismatch error and when I look at the repository array the element is Error 2042.

I have searched for error 2042 but have not found any difinitive information.

The arrays are of type variant. On the surface it seems like a pretty straight forward program but I can’t seem to get past this error.

Any Ideas?

Источник

As a side note to this and for anyone who gets this error in future, with any function returning a possible error, the variant type works quite well:

Dim vreturn as variant 

vreturn = Application.Match(CurrentShipment, Range(A1:A5), 0)  this could be any function like a vlookup for example as well

If IsError(vreturn) Then
     handle error
Else
    CurrentRow = cint(vreturn)
End If

See the list of VBA Cell Error Values:

Constant    Error number  Cell error value
xlErrDiv0   2007          #DIV/0!
xlErrNA     2042          #N/A
xlErrName   2029          #NAME?
xlErrNull   2000          #NULL!
xlErrNum    2036          #NUM!
xlErrRef    2023          #REF!
xlErrValue  2015          #VALUE!

Try converting the value of CurrentShipment from an Integer to a Long instead of to a String:

CurrentRow = Application.Match(CLng(CurrentShipment), Range(A1:A5), 0)

excel – Why am I getting Error 2042 in VBA Match?

If you look for match function in object browser it returns double so i have declared the variable CurrentRow as double and while it accepts 3 variant parameter. Try below code if it works for you.

enter

  Sub sample()

    Dim CurrentShipment As Variant
    CurrentShipment = 7

    Dim CurrentRow As Double
    CurrentRow = Application.Match(CurrentShipment, Range(A1:A5), 0)
    End Sub

Источник