Need a macro to import and format data.

**dcgrove** · 01-30-2009, 04:47 PM

Hello, I am having an issue with some data that is pulled from an access database. In column A of the attached file, the numbers are stored as text which is throwing off my vlookup formulas. I need a macro that will do two things. First I need to convert all of column A to numbers from text. The issues is that the number of cells in column A is dynamic from one month to the next. The second task I need the macro to do is remove any text characters after the four digit number.

Because I was asked to put this together for someone who is very challenged with excel, I need this to be done by having the end user push a button on the sheet, having a dialog box open for the user to be able to select the sheet with the data and have it import the data into this sheet.

I have no idea how to do this myself, but I am trying to learn so any help would be appreciated.

Thanks!
Clayton Grove

**mewingkitty** · 01-30-2009, 05:09 PM

See attached sheet.

This satisfies your first two criteria.

as per

having a dialog box open for the user to be able to select the sheet with the data and have it import the data into this sheet

I'm not too sure what exactly you mean.
Are there multiple sheets which may be referenced from, and you want this box to allow them to choose which one?

Also -
VLOOKUP requires the data in the first column to be sorted in ascending order. That will cause you problems with these numbers as they are currently not sorted.

**Leith Ross** · 01-30-2009, 05:19 PM

Hello Clayton,

To check you data after it has been downloaded from a database, you can use the worksheet function CELL to return information about the format and data type of the cells. In this case, the test shows your data is a number formatted as follows "#,##0.00". How did you determine it was text?

**Leith Ross** · 01-30-2009, 06:01 PM

Hello Clayton,

I added this macro to your workbook. This reformats all the cells as numbers using the format "#,##0.00"

Please Login or Register  to view this content.

Need a macro to import and format data.

LinkBack

Thread Tools

Rate This Thread

Display

Need a macro to import and format data.

Re: Need a macro to import and format data.

Re: Need a macro to import and format data.

Re: Need a macro to import and format data.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions