Error 2042 vba excel

Error 2042 in Vlookup VBA despite type and value equality

Summerization: Despite seeming equality of two strings, they do not match match the text in their respective cells. Even though the third one does match with its.

I was given task «if possible» to simplify navigating through a complex excel WorkBook. I figured out a better way to generate IDs, as the old ones are almost nonsensical.

My IDs are created from 3 different numbers and the macro I’m making reverses these IDs with description explaining what the ID stands for.

Example: 550000210 first five stand for the account, the two after for the purpose and last two for the distribution (method). All 3 IDs are of the same format (or so I think so) and they are all strings(text). The final ID is a string(text), too.

To me surprise, however, it is not easy to use VLookup function in VBA and besides the first 5 digits, the two pairs don’t match with the values in the ranges they refer too.

That made me think the named ranges within the «legenda» sheet do not share format, as the account number is generated by an online system but the pairs are by me. So I removed the formating and copied the format of the account numbers and it didn’t change anything, tried double instead didn’t help. Using add watch, it says number I expect it to find is the same type «Variant/String», when I make an if condition where i compare them, it goes through successfully.

I’m really getting desperate, I do not necessarily have a time pressure but this was supposed to be a minor step to a «try» attempt to see if it goes anywhere for the task.

Expected result is having the part of ID in left column of labels and description in the right one. It works only for «a». I suspect the issue really still is formating however.

Источник

Thread: Solved: vlookup error 2042

Thread Tools

Display

Solved: vlookup error 2042

i use a vlookup to find a match on a different sheet with this code:

[VBA]result = Application.VLookup(str, Sheets(3).Range(«A1:B» & Cells(Rows.Count, 1).End(xlUp).Row), 2, False)
MsgBox result
[/VBA]
and that works just fine while it finds something, but in 99,99% of the times it will not find anything.
that makes the result become «error 2042» and causing it to crash.

how can i avoid the msgbox-code when result is 2042?

[VBA]if result = «error 2042″[/VBA]
[VBA]on error goto 1:
msgbox result
1:[/VBA]
[VBA]if is not err.number = 2042[/VBA]

its probably simple, what did i do wrong?

[VBA]
On Error Resume Next
Result = Application.VLookup(MyStr, Sheets(3).Range(«A1:B» & Cells(Rows.Count, 1).End(xlUp).Row), 2, False)
If Result = «Error 2042» Then Result = «Nothing Found»
MsgBox Result
On Error GoTo 0
[/VBA]

MVP (Excel 2008-2010)

Post a workbook with sample data and layout if you want a quicker solution.

To help indent your macros try Smart Indent

Please remember to mark threads ‘Solved’

There are times when it is acceptable to use On error resume next. this seems to fit that need.

You have to be careful using it though as in the wrong usage it will ignore errors that can cause incorrect results.

oh sorry i forgott to say i dont like on error resume next since this might make the code skip by a serious thing.

[VBA]if result= «Error 2042″[/VBA]
gives me an errorcode 13 incompatible types.
i think this is because the Error 2042 is not a string, its something else.
it looks like a string when i hower the mousepointer above it, but its not.

There are times when it is acceptable to use On error resume next. this seems to fit that need.

You have to be careful using it though as in the wrong usage it will ignore errors that can cause incorrect results.

yeah.. i was typing my other reply while you wrote this.

the code i have is much more complex than this part i showed here, so a resume next is very risky in my opinion.

is there a way to say:

use on error resume next from here.
.

Post 2 restores normal error operation

MVP (Excel 2008-2010)

Post a workbook with sample data and layout if you want a quicker solution.

To help indent your macros try Smart Indent

Please remember to mark threads ‘Solved’

sweet! thanks that could work

BTW, avoid using Str as a variable. It is a VBA function. I used MyStr in my example.

MVP (Excel 2008-2010)

Post a workbook with sample data and layout if you want a quicker solution.

To help indent your macros try Smart Indent

Please remember to mark threads ‘Solved’

I was wondering if you had read past Malcolms suggestion.

lucas, yes i did read his post but since it «wont work».

if result = «Error 2042»
that crashes since its incompatible types.

and even if it would work it would change result to «Nothing found»
then do msgbox result.
that mean i would get a msgbox «all the time» with nothing found.
not really what i was hoping on.

but i had no clue about on error goto 0 means restore «default procedure».

mdmacillop: thanks i had no idea that was a function. i will change that!
i just made this code as a «test» since i wanted to make the code run faster.
i used to have a loop doing the same thing, but as the list gets bigger the code will run slower so i figured i would take care of the issue before it becomes a problem.

Источник

Vba excel error 2042

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I am new to VB so my question probably sounds silly!

I would like to use VLookup function but I cannot handle the return value when the function does not find the parameter. I get a «Error 2042». I dont know its actuall format. Is it only a string? I tried to check it with the Aplication.IsNA() and IsEmpty() message but they don’t work correctly.

Do you know how can I solve this problem?

Answers

Per our support engineer:

First of all, I think our buddy asked a very good question about how to handle the exception thrown by VLOOKUP function. J

Please ask our buddy refer to following KB article:

How to Use VLOOKUP or HLOOKUP to find an exact match

We set the last parameter of VLOOKUP to ‘FALSE’ to find the exact matched data. We can capture the exception by calling ‘ISERROR()’ function. If ‘ISERROR()’ equals to TRUE, it means we can not find the exact matched item in the source table. Please refer to following VB code:

Dim exRange As Range

Set exRange = Sheets(«Product»).UsedRange

ActiveWorkbook.Names.Add Name:=»ProductRange», RefersToR1C1:=»=Sheet1!R1C1:R15C2″

Dim currentSheet As Worksheet

Set currentSheet = Sheets(«Receipt»)

Dim i As Integer

Dim strCmd As String, strCmd1 As String

‘No VLookup error handling

strCmd = «VLOOKUP(» & «R» & CStr(i) & «C2,Product!R1C1:R15C2,2)»

MsgBox «Formula in Cell R» & CStr(i) & «C2: » & strCmd

currentSheet.Range(«C» & i).FormulaR1C1 = «=» & strCmd

‘With VLookup error handling

strCmd = «VLOOKUP(» & «R» & CStr(i) & «C2,Product!R1C1:R15C2,2)»

strCmd1 = «VLOOKUP(» & «R» & CStr(i) & «C2,Product!R1C1:R15C2,2, False)»

strCmd = «IF(ISERROR(» & strCmd1 & «),»»CUSTOM ERROR»»» & «,» & strCmd & «)» ‘Handle the exception and replace the value in that cell with custom message

MsgBox «Formula in Cell R» & CStr(i) & «C2: » & strCmd

currentSheet.Range(«C» & i).FormulaR1C1 = «=» & strCmd

Source Sheet (Product)

[*this is a two column table showing product and price]

DDR RAM 256M
$80

DDR RAM 512M
$100

Mainboard A
$150

Mainboard B
$200

Target Sheet (Receipt)

[*this is a three column table showing description, product and price]

Источник

Why am I getting Error 2042 in VBA Match?

I have Column A:

into a cell on the sheet I get

As a result. (This is desired)

But when I enter this line:

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

6 Answers 6

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:

See the list of VBA Cell Error Values:

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

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.

I had exactly the same error message when tried to use the Match function in a workbook with several worksheets. The use of the variant type for the variable that receives the result is good to avoid the running errors, if the subject that we try to match does not exists. But my mistake was in the location of the range used. It is vital to point for the worksheet that have the range to be searched. In the first, time I used Range(. ) without the worksheet reference, and the function only worked correctly if I had that worksheet activated.

Источник

Excel VBA error 2042 vlookup — how to handle this error

I have a user form with input data (Fullname, Field1, Field2, etc.) that I need to write to the table (column1 — Fullname, column2 — Field1, column3 — Field2) on the sheet. Before writing, I need to check if the data that I have in user form are already in the table or aren’t. If the data are in the table then I need to show msgbox with the text «Data are already written, do you want to overwrite?» if yes then overwrite data in a row according to Fullname that is the same in user form and table in column1

For checking, I use vlookup for Fullname, Field1, and Field2. But when I start first row of Vlookup code for fullname, code is ok but the second shows error 2042. Code is the same but results are not. I don’t know how to handle error 2042 (no data match, even the data in userform and data in table are the same)

Can you help me?

Hot Network Questions

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.14.43159

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

Per our support engineer:

First of all, I think our buddy asked a very good question about how to handle the exception thrown by VLOOKUP function. J

Please ask our buddy refer to following KB article:

How to Use VLOOKUP or HLOOKUP to find an exact match

http://support.microsoft.com/default.aspx?scid=kb;en-us;181213

We set the last parameter of VLOOKUP to ‘FALSE’ to find the exact matched data. We can capture the exception by calling ‘ISERROR()’ function. If ‘ISERROR()’ equals to  TRUE, it means we can not find the exact matched item in the source table. Please refer to following VB code:

===========================

Dim exRange As Range

 Set exRange = Sheets(«Product»).UsedRange

 ActiveWorkbook.Names.Add Name:=»ProductRange», RefersToR1C1:=»=Sheet1!R1C1:R15C2″

 Dim currentSheet As Worksheet

 Set currentSheet = Sheets(«Receipt»)

 Dim i As Integer

 Dim strCmd As String, strCmd1 As String

 ‘No VLookup error handling

 For i = 2 To 4

    strCmd = «VLOOKUP(» & «R» & CStr(i) & «C2,Product!R1C1:R15C2,2)»

    MsgBox «Formula in Cell R» & CStr(i) & «C2: » & strCmd

    currentSheet.Range(«C» & i).FormulaR1C1 = «=» & strCmd

 Next

 ‘With VLookup error handling

  For i = 6 To 8

    strCmd = «VLOOKUP(» & «R» & CStr(i) & «C2,Product!R1C1:R15C2,2)»

    strCmd1 = «VLOOKUP(» & «R» & CStr(i) & «C2,Product!R1C1:R15C2,2, False)»

    strCmd = «IF(ISERROR(» & strCmd1 & «),»»CUSTOM ERROR»»» & «,» & strCmd & «)» ‘Handle the exception and replace the value in that cell with custom message

    MsgBox «Formula in Cell R» & CStr(i) & «C2: » & strCmd

    currentSheet.Range(«C» & i).FormulaR1C1 = «=» & strCmd

 Next

============================

Source Sheet (Product)

============================

[*this is a two column table showing product and price]

Product
 Price

 CPU A
 $100

 CPU B
 $80

 CPU C
 $120

 CPU D
 $70

 CPU E
 $150

 DDR RAM 256M
 $80

 DDR RAM 1G
 $200

 DDR RAM 512M
 $100

 Mainboard A
 $150

 Mainboard B
 $200

 Mainboard C
 $40

 Mainboard D
 $60

 Mainboard E
 $80

 Mainboard F
 $110

Target Sheet (Receipt)

===========================

[*this is a three column table showing description, product and price]

 Description
 Product
 Price

 CPU
 CPU A
 100

 RAM
 DDR RAM 256M
 200

 Mainboard
 Mainboard C
 40

 　 CPU
 CPU F
 CUSTOM ERROR

 RAM
 DDR RAM 512M
 100

 Mainboard
 Mainboard A
 150

Note: ‘CPU F’ is not in the source table.

-brenda (ISV Buddy Team)

When I use Match() in my VBA sub, it returns a 2042 Error every other time. When no error is returned the code works perfectly fine, it is quite strange… Here is what the code looks like:

target.Sheets("Countries").Range("D3:D" & lastRow) = Application.Trim(target.Sheets("Countries").Range("D3:D" & lastRow).Value)
target.Sheets("Countries").Range("D3:D" & lastRow).NumberFormat = "@"
target.Sheets("Countries").Range("T3:T" & lastRow) = Application.Trim(target.Sheets("Countries").Range("T3:T" & lastRow).Value)

For i = 0 To rowz - 1

    pay = Application.Index(target.Sheets("Countries").Range("G3:G" & lastRow), Application.Match(target.Sheets("TEST").Range("D" & 7 + i), target.Sheets("Countries").Range("D3:D" & lastRow), 0))
    fact = Application.Index(target.Sheets("Countries").Range("T3:T" & lastRow), Application.Match(target.Sheets("TEST").Range("D" & 7 + i), target.Sheets("Countries").Range("D3:D" & lastRow), 0))

'rest of code

Next i

I have also noticed that the error only happens on the lookup values that are numbers formatted as text for some reason, but always works on values that stat with a letter. When error is returned by payit is also retuned by fact, but when it works for the first one it works for both.

	Номер ошибки:	Ошибка 2042
Название ошибки:	Excel Error 2042
Описание ошибки:	Ошибка 2042: Возникла ошибка в приложении Microsoft Excel. Приложение будет закрыто. Приносим извинения за неудобства.
Разработчик:	Microsoft Corporation
Программное обеспечение:	Microsoft Excel
Относится к:	Windows XP, Vista, 7, 8, 10, 11

Обзор «Excel Error 2042»

«Excel Error 2042» также считается ошибкой во время выполнения (ошибкой). Чтобы убедиться, что функциональность и операции работают в пригодном для использования состоянии, разработчики программного обеспечения, такие как Microsoft Corporation, выполняют отладку перед выпусками программного обеспечения. К сожалению, такие проблемы, как ошибка 2042, могут быть пропущены, и программное обеспечение будет содержать эти проблемы при выпуске.

Некоторые пользователи могут столкнуться с сообщением «Excel Error 2042» при использовании Microsoft Excel. После возникновения ошибки 2042 пользователь программного обеспечения имеет возможность сообщить разработчику об этой проблеме. Разработчик сможет исправить свой исходный код и выпустить обновление на рынке. Следовательно, разработчик будет использовать пакет обновления Microsoft Excel для устранения ошибки 2042 и любых других сообщений об ошибках.

Почему возникает ошибка времени выполнения 2042?

Сбой устройства или Microsoft Excel обычно может проявляться с «Excel Error 2042» в качестве проблемы во время выполнения. Вот три наиболее распространенные причины, по которым происходят ошибки во время выполнения ошибки 2042:

Ошибка 2042 Crash — Номер ошибки вызовет блокировка системы компьютера, препятствуя использованию программы. Когда Microsoft Excel не может обеспечить достаточный вывод для данного ввода или действительно не знает, что выводить, он часто путает систему таким образом.

Утечка памяти «Excel Error 2042» — ошибка 2042 приводит к постоянной утечке памяти Microsoft Excel. Потребление памяти напрямую пропорционально загрузке ЦП. Это может быть вызвано неправильной конфигурацией программного обеспечения Microsoft Corporation или когда одна команда запускает цикл, который не может быть завершен.

Ошибка 2042 Logic Error — Вы можете столкнуться с логической ошибкой, когда программа дает неправильные результаты, даже если пользователь указывает правильное значение. Это происходит, когда исходный код Microsoft Corporation вызывает уязвимость при обработке информации.

Основные причины Microsoft Corporation ошибок, связанных с файлом Excel Error 2042, включают отсутствие или повреждение файла, или, в некоторых случаях, заражение связанного Microsoft Excel вредоносным ПО в прошлом или настоящем. Как правило, решить проблему можно заменой файла Microsoft Corporation. В качестве последней меры мы рекомендуем использовать очиститель реестра для исправления всех недопустимых Excel Error 2042, расширений файлов Microsoft Corporation и других ссылок на пути к файлам, по причине которых может возникать сообщение об ошибке.

Распространенные проблемы Excel Error 2042

Усложнения Microsoft Excel с Excel Error 2042 состоят из:

• «Ошибка в приложении: Excel Error 2042»
• «Недопустимая программа Win32: Excel Error 2042»
• «Возникла ошибка в приложении Excel Error 2042. Приложение будет закрыто. Приносим извинения за неудобства.»
• «Не удается найти Excel Error 2042»
• «Отсутствует файл Excel Error 2042.»
• «Ошибка запуска программы: Excel Error 2042.»
• «Не удается запустить Excel Error 2042. «
• «Excel Error 2042 остановлен. «
• «Неверный путь к приложению: Excel Error 2042.»

Проблемы Excel Error 2042 с участием Microsoft Excels возникают во время установки, при запуске или завершении работы программного обеспечения, связанного с Excel Error 2042, или во время процесса установки Windows. Важно отметить, когда возникают проблемы Excel Error 2042, так как это помогает устранять проблемы Microsoft Excel (и сообщать в Microsoft Corporation).

Создатели Excel Error 2042 Трудности

Проблемы Excel Error 2042 могут быть отнесены к поврежденным или отсутствующим файлам, содержащим ошибки записям реестра, связанным с Excel Error 2042, или к вирусам / вредоносному ПО.

Более конкретно, данные ошибки Excel Error 2042 могут быть вызваны следующими причинами:

• Недопустимый Excel Error 2042 или поврежденный раздел реестра.
• Вредоносные программы заразили Excel Error 2042, создавая повреждение.
• Другая программа (не связанная с Microsoft Excel) удалила Excel Error 2042 злонамеренно или по ошибке.
• Excel Error 2042 конфликтует с другой программой (общим файлом).
• Неполный или поврежденный Microsoft Excel (Excel Error 2042) из загрузки или установки.

Продукт Solvusoft

Загрузка
WinThruster 2022 — Проверьте свой компьютер на наличие ошибок.

Совместима с Windows 2000, XP, Vista, 7, 8, 10 и 11

Установить необязательные продукты — WinThruster (Solvusoft) | Лицензия | Политика защиты личных сведений | Условия | Удаление

1. 04-11-2005, 06:06 PM

   
   

   #1

   Error 2042-how I was told to do it.
   

   I am trying to use the match function within vba without using a cell.
   The following is how I was told to do it. I am trying to match the
   value of begbase (which is «10/1/97» in this case).

   Dim sb As Variant

   sb = Application.Match(begbase, Range(«a2:a» & causerows))

   What I get is Error 2042. I don’t know what that is. Can some one
   either tell me what it is, or better yet, how to solve the problem?

   TIA

   ---

2. 04-11-2005, 07:06 PM

   
   

   #2

   re: Error 2042-how I was told to do it.
   

   Error 2042 means that the Match was not found. It’s equivalent to
   returning #N/A when called from the worksheet.

   Since you’re not using the match type argument, it means a value greater
   than sb was found before sb, if sb exists in the target range.

   In article <1113256107.604337.63660@g14g2000cwa.googlegroups.com>,
   «papa jonah» <adullam04-excelgoogle@yahoo.com> wrote:

   
   > I am trying to use the match function within vba without using a cell.
   > The following is how I was told to do it. I am trying to match the
   > value of begbase (which is «10/1/97» in this case).
   >
   > Dim sb As Variant
   >
   > sb = Application.Match(begbase, Range(«a2:a» & causerows))
   >
   > What I get is Error 2042. I don’t know what that is. Can some one
   > either tell me what it is, or better yet, how to solve the problem?
   >
   > TIA

   ---

3. 04-11-2005, 10:08 PM

   
   

   #3

   re: Error 2042-how I was told to do it.
   

   Are you trying to find an exact match?

   If yes:

   dim SB as variant
   sb = application.match(begbase,range(«a2:a» & causerows),0))
   if iserror(sb) then
   ‘not found
   else
   ‘found
   end if

   Sometimes with dates, this works better:
   sb = application.match(clng(begbase),range(«a2:a» & causerows),0))

   papa jonah wrote:

   
   >
   > I am trying to use the match function within vba without using a cell.
   > The following is how I was told to do it. I am trying to match the
   > value of begbase (which is «10/1/97» in this case).
   >
   > Dim sb As Variant
   >
   > sb = Application.Match(begbase, Range(«a2:a» & causerows))
   >
   > What I get is Error 2042. I don’t know what that is. Can some one
   > either tell me what it is, or better yet, how to solve the problem?
   >
   > TIA

   —

   Dave Peterson

   ---