VBA to look for blank cells in range and copy with value above

[jj72uk]

Im currently using a web query to get some external data for some sales people.

In this case these sales people usually cover 2 brands.

The way the SAS code is driven when displayed on our intranet means that the sales agents ID will only display for one of the brands and the correlating second brand will always be below, but the ID will be missing.

Is there a VBA macro that can be written that will look in a range of cells in a coloum, look to see if its blank and then copy the value from the cell right above it into it?

I've attached a sheet to show what I mean.

[DonkeyOte]

If I've understood, perhaps:

Please Login or Register  to view this content.

[jj72uk]

Donkey,

You never fail to impress me!

My VBA is basic. Very basic and though alot of the times I can understand the reasoning why the VBA works, in this case i cannot.

Mind giving me a quick explination?

[DonkeyOte]

The code basically

a) creates a Range in D based on content of E
(ie last row can be identified by last non-blank in E, once established offset to left by column to get identical range but in D)

b) applies SpecialCells logic to the range to identify all Blanks within and subsequently applies formula to the blanks to reference cell above
(uses R1C1 notation)

c) removes the formulae by essentially setting the content of each cell to be the present result only

The On Error statement is required because if no Blanks were found the SpecialCells method would otherwise generate an error.

The SpecialCells method can be replicated using F5 -> Special -> Blanks -> OK -> = up arrow -> CTRL + ENTER... but depending on the content of the last cell in D you may not get the correct output (ie if last cell in D is blank you won't get a value)

[jj72uk]

Very very intresting!!

Is there a way to encorprecate in to VBA a web query refresh to link to a command button...

Or.... how about VBA for the query update + the above?

[DonkeyOte]

Sounds like a different question to me... so new thread please.

[jj72uk]

Sorry.

In relation to the orginal question.

What amendments to the code would be needed if the command button is on a different sheet, but the tables for the VBA code to action is on a sheet called 'data'?

[DonkeyOte]

You could try

Please Login or Register  to view this content.

[jj72uk]

Once again, thanks