Copy cell, if cell blank move to cell?

**BobTheRocker** · 03-14-2010, 06:38 AM

I am having trouble writing a code to copy a number from one cell into another cell. Basically if the cell is blank I want to move horizontally to the cell to the left and copy that one, if thats blank move to the left again until a number is found.

My data cells B109 to H109. I thought something like this would work =IF(H109="",IF(G109="",F109)) but this is as far as I can get it to work, if I use this =IF(H109="",IF(G109="",IF( F109="",E109))) it returns false. I'm sure there must be a simpler way of achieving this.

Thanks in advance

**martindwilson** · 03-14-2010, 06:48 AM

right or left?
find the first number after blanks

=index(b109:h109,match(true,index(b109:h109>0,0),0))

eg in blank,blank 1,2,3,5,5 it will return 1
or
find the last number used is
=LOOKUP(10^99,B109:H109)
1,2,3,4,5,blank ,blank will return 5

**Richard Buttrey** · 03-14-2010, 06:54 AM

Hi Bob, and welcome to the forum.

One way

Code:

=INDEX(B109:H109,1,MATCH(9E+307,B109:H109))

HTH

**BobTheRocker** · 03-14-2010, 08:05 AM

Brilliant guys, thanks very much. Both your methods work. Out of interest Martin, what does the 10^99 signify. Equally Richard what does the 9E+307 signify. New to excel, just trying to understand its scripting better.

**martindwilson** · 03-14-2010, 05:30 PM

10^99 is just a big number the biggest being what richard used 9E+307
i just can never remember it lol. as long as its bigger than any number in your range it matches the last one in lookup