how to find a non zero cell from a column

0

Hi Guys,

I need a help with cording, this should be a simple loop but I need help,

what i want is,

there is a colomn and it contain only one value (lets say coloumn B) and there are relevent date in column A. I need to find the cell with the value in coloumn B and copy the relevent cell in column A to deferent sheet.

if i explain more,

I need to go down along the column B till i get to a none zero value cell (lets say it is B10) and then copy the data in relevent column A cell (A10).

0

This is the code to help you write your own code. It is fully functional, but its aim is to show you all the information that is needed which makes it somewhat more convoluted (but much easier to understand) than one would write it for practical purposes.

``````Sub CopyToOtherSheet()

Dim FoundValue As Variant
Dim WsTo As Worksheet               ' the worksheet to paste to
Dim Rt As Long                      ' row to paste to in WsTo
Dim Ct As Long                      ' column to paste to
Dim WsFrom As Worksheet             ' the sheet to copy from
Dim Rng As Range                    ' relevant part of column B
Dim Rstart As Long                  ' first row of Rng
Dim Rend As Long                    ' last row of Rng
Dim R As Long                       ' row counter for loop

Set WsFrom = ActiveSheet
Rstart = 2                                           ' first row below caption ?
With WsFrom
Rend = .Cells(.Rows.Count, "B").End(xlUp).Row    ' last used row ?
For R = Rstart To Rend
If Val(.Cells(R, "B").Value) <> 0 Then
FoundValue = .Cells(R, "A").Value
Exit For
End If
Next R
End With

Set WsTo = Worksheets("Sheet2")
Ct = 3                                              ' 3 = column C ? (1 = A)
With WsTo
Rt = .Cells(.Rows.Count, Ct).End(xlUp).Row + 1   ' below the last used row ?
.Cells(Rt, Ct).Value = FoundValue
End With
End Sub
``````

Start with the list od 'Dim' statements at the top. All of this information will be required. You can fill in the information is you read down the code. In some cases the information may be dynamic, such as the last used row. My code shows you how you might get that information programmatically.

When you are done you may like to streamline the code by eliminating some of the declarations. That will shorten the code but not necessarily make it either faster or better.

Discussion

Need a space after 'Set'    ( 'SetWsFrom' should be 'Set WsFrom').
It doesn't appear to be looping through Column B.
queue (rep: 432) Oct 13, '17 at 10:11 am
@queue Thank you. I've corrected the type in my post. The code loops correctly
Variatus (rep: 2688) Oct 13, '17 at 9:47 pm