Use another cell if a cell is empty?

[CaptainCsaba]

Hey Guys!

Basically what happened is that one of our data suppliers changed his format a bit in excel. We have a macro for it however now it does not work every time, because it sometimes comes in a different format. So the macro works like this: The cells get unmerged, and after that, in all rows every words get's into one cell. So if Cell A1 had "Apple Banana 345" then Apple gets into A1, Banada into B1 and 345 into C1. And after this it starts to look for the things we need. That part of the code looks like this:

    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=IF(Input!R[-2]C[3]=""/"",Input!R[-2]C[2],0)"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=IF(Input!R[-2]C[2]=""/"",Input!R[-2]C[-1],0)"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=0,0,IF(AND(Input!R[-2]C[1]=""/"",Input!R[3]C[1]=""/""),CONCATENATE(Input!R[-2]C[3],"" "",Input!R[-2]C[4],"" "",Input!R[-2]C[5],"" "",Input!R[-2]C[6],"" "",Input!R[-2]C[7],"" "",Input!R[-2]C[8],"" "",Input!R[-2]C[9],"" "",Input!R[-2]C[10],"" "",Input!R[-2]C[11],"" "",Input!R[-2]C[12],"" "",Input!R[-2]C[13],"" "",Input!R[-2]C[14],"" "",Input!R[-2]C[15],"" " & _
        """,Input!R[-2]C[16],Input!R[-1]C[-2],"" "",Input!R[-1]C[-1],"" "",Input!R[-1]C,"" "",Input!R[-1]C[1],"" "",Input!R[-1]C[2],"" "",Input!R[-1]C[3],"" "",Input!R[-1]C[4],"" "",Input!R[-1]C[5]),CONCATENATE(Input!R[-2]C[3],"" "",Input!R[-2]C[4],"" "",Input!R[-2]C[5],"" "",Input!R[-2]C[6],"" "",Input!R[-2]C[7],"" "",Input!R[-2]C[8],"" "",Input!R[-2]C[9],"" "",Input!R[-2]C" & _
        "[10],"" "",Input!R[-2]C[11],"" "",Input!R[-2]C[12],"" "",Input!R[-2]C[13],"" "",Input!R[-2]C[14],"" "",Input!R[-2]C[15],"" "",Input!R[-2]C[16])))" & _
        ""
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=0,0,IF(Input!R[-1]C=""for"",CONCATENATE(Input!R[-1]C[5],"" "",Input!R[-1]C[6],"" "",Input!R[-1]C[7],"" "",Input!R[-1]C[8],"" "",Input!R[-1]C[9],"" "",Input!R[-1]C[10],"" "",Input!R[-1]C[11],"" "",Input!R[-1]C[12]),IF(Input!RC=""for"",CONCATENATE(Input!RC[5],"" "",Input!RC[6],"" "",Input!RC[7],"" "",Input!RC[8],"" "",Input!RC[9],"" "",Input!RC[10],"" "",In" & _
        "put!RC[11],"" "",Input!RC[12]),IF(Input!R[1]C=""for"",CONCATENATE(Input!R[1]C[5],"" "",Input!R[1]C[6],"" "",Input!R[1]C[7],"" "",Input!R[1]C[8],"" "",Input!R[1]C[9],"" "",Input!R[1]C[10],"" "",Input!R[1]C[11],"" "",Input!R[1]C[12]),IF(Input!R[2]C=""for"",CONCATENATE(Input!R[2]C[5],"" "",Input!R[2]C[6],"" "",Input!R[2]C[7],"" "",Input!R[2]C[8],"" "",Input!R[2]C[9],""" & _
        " "",Input!R[2]C[10],"" "",Input!R[2]C[11],"" "",Input!R[2]C[12]),IF(Input!R[3]C=""for"",CONCATENATE(Input!R[3]C[5],"" "",Input!R[3]C[6],"" "",Input!R[3]C[7],"" "",Input!R[3]C[8],"" "",Input!R[3]C[9],"" "",Input!R[3]C[10],"" "",Input!R[3]C[11],"" "",Input!R[3]C[12]),IF(Input!R[4]C=""for"",CONCATENATE(Input!R[4]C[5],"" "",Input!R[4]C[6],"" "",Input!R[4]C[7],"" "",Inpu" & _
        "t!R[4]C[8],"" "",Input!R[4]C[9],"" "",Input!R[4]C[10],"" "",Input!R[4]C[11],"" "",Input!R[4]C[12]),IF(Input!R[5]C=""for"",CONCATENATE(Input!R[5]C[5],"" "",Input!R[5]C[6],"" "",Input!R[5]C[7],"" "",Input!R[5]C[8],"" "",Input!R[5]C[9],"" "",Input!R[5]C[10],"" "",Input!R[5]C[11],"" "",Input!R[5]C[12]),IF(Input!R[6]C=""for"",CONCATENATE(Input!R[6]C[5],"" "",Input!R[6]C[" & _
        "6],"" "",Input!R[6]C[7],"" "",Input!R[6]C[8],"" "",Input!R[6]C[9],"" "",Input!R[6]C[10],"" "",Input!R[6]C[11],"" "",Input!R[6]C[12]),0)))))))))" & _
        ""
   Range("E3").Select
    ActiveCell.FormulaR1C1 = "=IF(Input!R[-2]C[-1]=""/"",Input!R[-2]C,0)"

What happened is part of the text that was in the same row as "/" is now below that cell. It does not happen with every report but it becomes more often. So we need a code that is basically tells the macro that "if you can't find useful data then get the data from the cells that are below this one"

To show you this in action, this is what we got before (this is one out many, this continues down quite a while but the format is the same for all of them):

48,876 Shares OM04 / WBED Aberdeen UK equity Fund
4th Floor
25 Walbrook, London E4CN 8AF UK
Investment manager is: Aberdeen Asset Management

This is what we get now:

48,876 Shares OM04 / WBED
Aberdeen UK equity Fund
4th Floor
25 Walbrook, London E4CN 8AF UK
Investment manager is: Aberdeen Asset Management

As you can see the orange part of the text has moved to the cells below and now the macro does not work because it can't find the data. (you can see it in the code I inserted above)

So we need to insert a code into this or after this that says that "if there is no data in the place that you are looking for, then it is probably in the row below". Don'tf orget that all the words are in their own cell as mentioned above.

Does somebody know how to write a code like this? Do you need any more information?

Edit: I can insert more of the code if necessary.
[TABLE="width: 577"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"][/TD]
[/TR]
</tbody>[/TABLE]

 

[CaptainCsaba]

Somebody?

 

[CaptainCsaba]

bump