Find blank cell and replace based on criteria

**anrichards22** · 11-18-2012, 01:47 AM

I currently use an IF formula to fill blank cells in my data set.
Basically the formula checks for blank cells in columns C, D, E and F and fills them with the value in the row above or below IF the value column G is the same for each pair ( Column G always has data).

Using a formula method I need to add another column to the data set for each of columns C, D, E and F.
The data set has over 750,000 rows.

I am looking to write a macro that will be do the same task WITHOUT adding any more columns to the data set.

My formula:

=IF(C100="",IF(G100=G99,C99,IF(G100=G101,C101,C100)))

Is the macro as simple as selecting the range (say column C for example) and inserting my formula into the 'do' part of the macro??

**FDibbins** · 11-18-2012, 01:53 AM

why do you need to add another column? If that formula is already there, Im sure it can be modified to suite?

**anrichards22** · 11-18-2012, 01:57 AM

There is no formula in any of columns C, D , E or F...there is supposed to be data.

I add another column in order to find blanks and replace with...

**Moo the Dog** · 11-18-2012, 02:31 AM

Try this code and let me know if it works (obviously try it on test data, or a copy of your workbook first!!!):

Please Login or Register  to view this content.

- Moo

**Moo the Dog** · 11-18-2012, 02:35 AM

One issue I know of with my VBA above is if there are two consecutive blank rows (columns C:F)... they will remain blank. My code only works if there is a single blank row at a time.

- Moo

* EDIT - It will work for two consecutive blank rows, as long as there are values to match in column G. =)

**anrichards22** · 11-19-2012, 03:27 AM

Will give it a try.

Thanks Moo

**anrichards22** · 11-19-2012, 09:02 AM

Moo,

Your code runs without errors but it does not fill the blanks!

**Moo the Dog** · 11-19-2012, 11:05 AM

Hmmm.. it works for me. Do you have a sample worksheet you could upload? My test spreadsheet is attached... not sure how yours is set up though.

- Moo

**anrichards22** · 11-20-2012, 04:10 AM

My data is formatted as a table, would that stop the macro from filling the blanks?

**Moo the Dog** · 11-20-2012, 07:44 PM

Yes, that has a lot to do with it. Unfortunately, I don't know how to convert the code I wrote (which works just fine for normal ranges) into something that works on Tables in Excel.

What I would recommend is to post a new thread, include my code above in the post if you want to (be sure to use code tags), as well as a small sample of your worksheet with the Table (remove any sensitive info if necessary) to see if someone else with more VBA knowledge is able to help accomplish what you need to do. Post in the same section (Programming/VBA/Macros) and use a title that includes something like "VBA to conditionally fill rows in a table."

Sorry! I gave it my best shot.

- Moo

**anrichards22** · 11-21-2012, 07:12 AM

No worries, thanks for your help Moo!

Much Appreciated

p.s. I reformatted as a range and your code worked beautifully!

**Naveed Raza** · 11-21-2012, 07:39 AM

Hi anrichards22,

Try this attached file and let me know is it meet ur requirement or not.

**Moo the Dog** · 11-21-2012, 10:51 AM

Glad I could help, anrichards22... at least somewhat.

- Moo

**anrichards22** · 11-25-2012, 08:25 PM

Originally Posted by Naveed Raza

Hi anrichards22,

Try this attached file and let me know is it meet ur requirement or not.

I will give it try, Thanks Naveed

**anrichards22** · 11-27-2012, 12:14 AM

Hi Naveed,

I tried your code (after adapting it) and it does NOT work.

I changed the code to this:

Please Login or Register  to view this content.

The blank cells are in the middle of my data (Columns T to AA) and the matching column is Column AC

When I run your code it fills ALL the blank cells with whatever is in column AC.

**Naveed Raza** · 11-27-2012, 05:36 AM

Yes it will fill that value only.

If possible can u provide ur sample data so that i can make some small modification.

Thanks - Naveed.

Find blank cell and replace based on criteria

LinkBack

Thread Tools

Rate This Thread

Display

Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Re: Find blank cell and replace based on criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions