Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling in some blanks to a data table
Hello everyone. I'm back with another 'data manipulation' kind of
question. I'm using Excel 2010 and I have a weekly table similar to the one that you see in Figure 1 below. The last field, entitled Location has some blank entries in it and this is the way it comes out of the system and I have no control over that aspect of it. I want my code to simply go down the Location column and anywhere it finds a blank space insert the words NO LOCATION. Please see Figure 2 for what the end result should look like. Can anyone help me out with the code that would make this happen? Thank you very much. Fig 1. FirstName Lastname EmpID MgrEmpID Email Location Alex Anderson aaaa abcxy Philadelphia Brooke Brown bbbb aaaa Philadelphia Carol Clark cccc aaaa David Drew dddd aaaa Philadelphia Erin Eisley eeee bbbb Felicia Ford ffff bbbb Phoenix Fig 2. FirstName Lastname EmpID MgrEmpID Email Location Alex Anderson aaaa abcxy Philadelphia Brooke Brown bbbb aaaa Philadelphia Carol Clark cccc aaaa NO LOCATION David Drew dddd aaaa Philadelphia Erin Eisley eeee bbbb NO LOCATION Felicia Ford ffff bbbb Phoenix |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling in some blanks to a data table
'Fill empty cells in the last column of a block of data With ActiveSheet.Range("A1").CurrentRegion .Columns(.Columns.Count).SpecialCells(xlCellTypeBl anks).Value = "No Location" End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling in some blanks to a data table
Thank you very much.
When I ran the code it kept throwing an error of "No cells found," then I realized that some of the cells only appear blank when they actually contain a space. Sorry for not noticing that earlier. When re- testing insuring that the contents of the cell were indeed empty, the code you showed me ran perfect. Question: Is it possible to modify the code so that it also changes cells with a space in them to the words "No Location." Sorry for not noticing that before. Thanks again. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling in some blanks to a data table
On 7/01/2012 10:45 PM, John Menken wrote:
Thank you very much. When I ran the code it kept throwing an error of "No cells found," then I realized that some of the cells only appear blank when they actually contain a space. Sorry for not noticing that earlier. When re- testing insuring that the contents of the cell were indeed empty, the code you showed me ran perfect. Question: Is it possible to modify the code so that it also changes cells with a space in them to the words "No Location." Sorry for not noticing that before. Thanks again. Hi John Modify to suit '1200 represents how far down the spreadsheet you need to go, rather then check 65k + cells, you can set the upper limit. '2 represents the starting cell as it assumes the 1st row is your Header. finalrow = Cells(1200, 1).End(xlUp).Row For i = 2 To finalrow If Not Cells(i, 6).Value < "" Then Cells(i, 6).Value = "No Location" End If Next i HTH Mick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling Blanks only with ABOVE nonBlanks | Excel Programming | |||
Filling in blanks | Excel Discussion (Misc queries) | |||
Filling in the blanks methods? | Excel Programming | |||
Access - Filling Blanks | Excel Worksheet Functions | |||
Filling in blanks. | Excel Worksheet Functions |