Microsoft Excel - lookup - Asked By Dan on 09-Mar-12 10:26 AM

Having problems viguring this out.

In column B:B I have numbers.  If the number is a zero, or the cell is blank, do nothing, but if it is any number other than zero, look to column I:I get the number from that cell and place the number in column L:L.

Thanks,

Dan
Donald Ross replied to Dan on 09-Mar-12 01:20 PM
Dan

you can use the IF statement like this =if(cell="",do "",else = this value or cell)
=if(B1="","",L1)  this mean you put this formula all the way down the page in a new colum some where.


HTH Don
wally eye replied to Dan on 09-Mar-12 01:51 PM
Your formula in L:L could look like this:

=IF(OR(B2=0,B2=""),"",I2)

Need Macro Help - Dan replied to Dan on 09-Mar-12 06:26 PM

Unfortunately I am having problems, but I think I am close.  Any help would be appreciated.

So, I have tried to dreate a loop to go down column B and if a cell in Column "B" is anything other than a zero, copy the number from cell "I" and paste it in cell "N". 

Sub Macro1()

Sheets("January").Select

   
Dim FirstCell As Range
Dim SecondCell As Range
Dim FirstTargetCell As Range

Dim i As Integer

Set FirstCell = Sheets("January").Range("B2")

Set SecondCell = Sheets("January").Range("I2")

Set FirstTargetCell = Sheets("January").Range("N2")

i = 0

Range("B2").Select

Do

If ActiveCell = 0 Then
ActiveCell.Offset(1, 0).Select

Else

''''''''''''''This next line is the issue.  If cell B is a zero, do nothing and go to the next cell in B. Okay so far, now if cell B is anything other than a zero, take the number from cell I and put it in cell N.  It is not doing it correctly and number are not in the right cell in cell N.

FirstTargetCell.Offset(i, 0).Value = SecondCell.Offset(i, 0).Value
i = i + 1


'''''''''''''The next lines are fine.

ActiveCell.Offset(1, 0).Select

End If

Loop Until FirstCell.Offset(i, 0).Value = ""

End Sub

Donald Ross replied to Dan on 09-Mar-12 07:00 PM
Dan you can do this with a formula, I am still learning to code on my own.  if Wally or Prichart get to if first they can help you with code, but as for me see attached file for in cell formula.

=IF(B1="","",IF(B1=0,"",I1))

dan.zip
Pichart Y. replied to Dan on 09-Mar-12 10:08 PM
Hi Dan,

Please attach you file here, of course with your code, even not complete yet this help to understand your work.

pichart Y.