Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet1 to worksheet2, keep cell format eg cell size | Excel Discussion (Misc queries) | |||
copy selected part number of text from one cell into another cell | Excel Discussion (Misc queries) | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
Copy data in one cell to blank cell immediately below, repeat | Excel Worksheet Functions |