Getting data out of a cell with a NAME error in Excel VBA

0 votes

I import data into Excel from another software and manipulate it using a macro. Because the text in the other programme began with a hyphen, I recently witnessed a user experience a crash. It's a last name, and the value was "-Smith" when I would normally anticipate "Smith" or something similar. This was interpreted as an equation when exported to Excel, and the value in the cell was "=-Smith". Naturally, a #NAME? error was displayed. An error occurred when I attempted to set a variable to that value.

I can get around this by assigning the cell contents to a variable before utilising the ISTEXT() function. But what I really want to do is gather the information.

As a test, on the worksheet I tried this:

'''=IF(ISTEXT(A2)=FALSE,MID(A2,3,20),A2)

You would think that this would grab the "Smith" portion of the contents of A2, but it doesn't. I've also tried:

'''=VALUETOTEXT(MID(A2,3,20))
'''=TEXTAFTER(A2,"-")

Nothing works. I get #NAME? error for all of this. If I click on the cell I can see the data I need, but I can't figure out how to get it.

Feb 3, 2023 in Others by Kithuzzz
• 38,000 points
669 views

1 answer to this question.

0 votes

If you need VBA, use .Formula:

Dim f As String
f = ActiveCell.Formula

Which you can then clean:

f = Replace(f, "=-", "")

If you need a worksheet formula, then use FORMULATEXT:

=SUBSTITUTE(FORMULATEXT(A1),"=-","")

enter image description here

answered Feb 3, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Highlighting Unique List of Words in Each Cell of a Selection of Cells - Excel VBA

In a Textbox it is a vbcrlf ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,600 points
531 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,600 points
938 views
0 votes
1 answer

How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,600 points
859 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,258 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,695 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
968 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,088 views
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,600 points
1,500 views
0 votes
1 answer

Activating a Specific Cell in Excel Using VBA Results to Error 400

I think you trying to select cells(4, ...READ MORE

answered Dec 27, 2022 in Others by narikkadan
• 63,600 points
568 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP