Change empty cell to a character in VBA

[maxzoran]

Hey all,

Very new to VBA, I am wanting to change some empty cells on a specific sheet to become the character - if it is empty. If it is not empty, to keep the current data. How would I do this in VBA? I just couldn't make anything I've found in the forum work. Thanks!

[lucas]

You don't say which cells....
Option Explicit
Sub Dash()
Dim cell As Range
For Each cell In ActiveSheet.Range("A1:A20")
If cell = "" Then
cell = "-"
End If
Next cell
End Sub

[mvidas]

You could also just use the Replace method:ActiveSheet.UsedRange.Replace "", "-", xlWholeChange ActiveSheet.UsedRange to a smaller range, if needed

[lucas]

Hey that's a role reversal Matt (King of Overkill).....Maxzoran, Matt's idea is cleaner. No need for the loop.

[maxzoran]

Thanks for the help guys! I really appreciate it. There really isn't a range of cells, just a few intermittent ones. Basically I need to have the - as place holders so that when the table is copied and pasted into our work database, the data doesn't get skewed and misaligned. Know of any better ways to do this?

[lucas]

What kind of database? Shouldn't need to copy and paste....could be part of the problem...

SQL (http://vbaexpress.com/forum/forumdisplay.php?f=94)?

[maxzoran]

It's called PERC. It's a database specifically for the petroleum business.

[lucas]

Does all data have to be input manually or by copy and paste or is there some import function that will accept cvs, xls, etc.?

[maxzoran]

copy and paste...and then you have to manually tab and space to get the columns and headers to line up. It's similar to copying a range of cells into notepad. Everything is skewed and misaligned because it doesn't take the grid with it.

[lucas]

Can you paste a table from MS Word into it?

[maxzoran]

nope....that's what I have now. I created a VBA that copies the cells I need and pastes them to a word document. Even when you copy and paste from there, the grid does not stay.

[lucas]

Do you mean that you lose the grid when you paste to Word or do you mean that you get the grid when you paste to word but it won't copy and paste from Word to your database?

[maxzoran]

from word to the database. It is an SQL database. Does that help my cause?

[lucas]

see the link in my post #6....you should not have to copy and paste to an sql database....

[maxzoran]

Again, thank for the help. I looked through the links and it really doesn't mean much to me. I am very green. I don't think I could really even get the data from the spreadsheet to the database and get it into the right location. There are so many different strings with different types of data. Plus, I know nothing of SQL

[mvidas]

Hi,

As it has been a few days you may have already found a solution to your problem. I don't know how much help I'd be with your database, but to replace blanks with - in only certain, intermittent cells:Range("A1,D6,I27,Q10:13,Z3").Replace "", "-", xlWhole