Copy corresponding cell if value is 0
 

Hi,

How can I copy corresponding cell if value in Col E 0..? Something like
below.

Col D |Col E
ABC |12
MNO |2
HTR |0
UTV |0
HNC |5
XYZ |6
QTR |2
GTR |0
MTR |8


Result:

Col D |Col E
ABC |12
MNO |2
HNC |5
XYZ |6
QTR |2
MTR |8

Copy corresponding cell if value is 0
 

Hi,

You could apply an AutoFilter and filter on not equal to 0, then copy the
results to a new location
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Kashyap" wrote:

Hi,

How can I copy corresponding cell if value in Col E 0..? Something like
below.

Col D |Col E
ABC |12
MNO |2
HTR |0
UTV |0
HNC |5
XYZ |6
QTR |2
GTR |0
MTR |8


Result:

Col D |Col E
ABC |12
MNO |2
HNC |5
XYZ |6
QTR |2
MTR |8

Copy corresponding cell if value is 0
 

Hi Shane,

I cannot use auto filter there as the whole report is automated.

Can you suggest me any other way of doing this?

Thanks



"Shane Devenshire" wrote:

Hi,

You could apply an AutoFilter and filter on not equal to 0, then copy the
results to a new location
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Kashyap" wrote:

Hi,

How can I copy corresponding cell if value in Col E 0..? Something like
below.

Col D |Col E
ABC |12
MNO |2
HTR |0
UTV |0
HNC |5
XYZ |6
QTR |2
GTR |0
MTR |8


Result:

Col D |Col E
ABC |12
MNO |2
HNC |5
XYZ |6
QTR |2
MTR |8

Copy corresponding cell if value is 0
 

What does an automated report have to do with it? If autofilter affects the
report, simply turn it off after you have done the copy.

Regards,
Fred.

"Kashyap" wrote in message
...

Hi Shane,

I cannot use auto filter there as the whole report is automated.

Can you suggest me any other way of doing this?

Thanks



"Shane Devenshire" wrote:

Hi,

You could apply an AutoFilter and filter on not equal to 0, then copy the
results to a new location
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Kashyap" wrote:

Hi,

How can I copy corresponding cell if value in Col E 0..? Something
like
below.

Col D |Col E
ABC |12
MNO |2
HTR |0
UTV |0
HNC |5
XYZ |6
QTR |2
GTR |0
MTR |8


Result:

Col D |Col E
ABC |12
MNO |2
HNC |5
XYZ |6
QTR |2
MTR |8

Copy corresponding cell if value is 0
 

1.Right click on toolbarselect control box
2.from the control box that appears on the sheet1, select and draw a command
button on your sheet
3.Double click the command button to open the code window and paste following
codes
#
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 5

Dim rowinsheet2 As Integer, colinsheet2 As Integer
rowinsheet2 = 1
colinsheet2 = 5
While Sheet1.Cells(row, col).Value < ""
If CInt(Sheet1.Cells(row, col).Value) 0 Then
Sheet2.Cells(rowinsheet2, colinsheet2).Value = Sheet1.Cells(row,
col).Value
Sheet2.Cells(rowinsheet2, colinsheet2 - 1).Value = Sheet1.Cells
(row, col - 1).Value
rowinsheet2 = rowinsheet2 + 1
End If
row = row + 1
Wend
End Sub
#

Note that it will move data from sheet1 to sheet2


Hope this works


Have a nice time

Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200902/1