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 in Others by Kithuzzz
• 38,010 points
187 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 by narikkadan
• 63,160 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 in Others by narikkadan
• 63,160 points
200 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 in Others by narikkadan
• 63,160 points
266 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 in Others by narikkadan
• 63,160 points
273 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
701 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
2,895 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,220 points
235 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,670 points
547 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,160 points
479 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,160 points
243 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