copy data and not formula

[martinblake02]

Hello,

I have a document containing 3 sheets.

sheet 1 pulls data from an external source
sheet 2 uses index and match to locate and copy errors
sheet 3 will record errors collected by sheet 2

sheet 2 is using index/match formulas to locate information
there is 100 rows with the index/match formulas locating data
however I currently only have 30 results so I have 70 rows showing #N/A or blank using iferror

I wish to copy only the 30 lines of data and not the remaining 70 rows with formulas (no results)


How can I do this using a macro?
I want to select only the cells with results (data) and not all 100 rows?




any help is much appreciated.

thanks
martin

 

[Andrew Poulsom]

Select all the cells, on the Home tab click Find & Select then Go To Special. Check Formulas, uncheck Errors and click OK. The macro recorder will give you some VBA code.

 

[martinblake02]

hi,

I have tried this and it does not work, as its an index/match formula it returns #N/A which excel treats as data.

therefore it selects all rows, I have tried all different settings in GoTo.

Thanks though

 

[Andrew Poulsom]

Can you post an example of your formula and what it returns if there is no error please?

 

[martinblake02]

Hi,

my formula used match/rank to locate the data from sheet1 and then index/match the number to return data
it displays a "-" if there is no data to the relevant rank:

=IFERROR(INDEX(Material!A:A,MATCH($B19,Material!$AG:$AG,0)),"-")

Material = SHeet1

thanks

 

[Andrew Poulsom]

What does it return if there is no error - text or a number?

 

[martinblake02]

it returns just the dash, but I can change it to return any value (text or number)

in the formula where it current says ,"-") I just change the - to any value

 

[Andrew Poulsom]

I asked if there was no error.

 

[martinblake02]

sorry,

if there is no error then it returns data, some columns are text and some are numbers.

thanks

 

[Andrew Poulsom]

If the formula returns text you won't be able to use Go To Special because "-' is also text.