Macro to loop between multiple columns and copy the text in one column

Hello Guys,

I need a help in creating a one macro which can resolve my below requirements:

1. There are 1000 of rows in sheet and we are using a V-lookup in columns no. – G, H, I and J for fetching the status from multiple sheets for unique app id present in Column B.
2. Generally, between columns no. G, H, I and J; only one column will have the status (could be any text, for ex: approved or conditioned etc.) and other columns will have the “#N/A” (value not available) error.
3. My requirement is, macro has to look for the status in the columns no. G, H, I and J (ignore if column is containing “#NA”) and if found then copy it and do paste special in Column E (in same row).

Any help would be appreciated a lot. Please refer the attached sample sheet.

Thanks

Hi Manish,
try it

Please Login or Register  to view this content.

Hello nilem,

You are right, column range is from F to I (mistakenly i had wrote G to J).

But, macro is not working. The "No cell is found" is coming at below line:

r.Cells(1, 0).Value = r.SpecialCells(xlCellTypeFormulas, 3)

Manish, are there formulas in your range F3:I7 or only values?

Hi Nilem,
I do not know on the actual data, but on the attached sample, there are no formulas, but values ONLY.

Hi AB, I have focused on this:

Originally Posted by Manish_Gupta

... and we are using a V-lookup in columns ...

Hi nilem,

In sample sheet i had removed but in original sheet there will be a vlookup in the cells.

What we do is we manually insert the vlookup in the F to I columns to fetch the status from multiple workbooks of app id's in column B and then want to run macro who will check for the staus in these columns against column B and update it into column D via ignoring the "#NA" errors.

Thanks

Hi Manish,
I think the previous code should work correctly, but also try it

Please Login or Register  to view this content.

Hi nilem,

Many thanks to you. This Macro is working perfect.

Only one change if you can do in it is in column G there is a numeric value. Can we add somewhere in a Macro that if there is an numeric value in Column G (only for column G) then it should update the status in column D as an "Filed".

Thanks,

Please Login or Register  to view this content.