Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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).

Please help.  

Answer
Discuss

Answers

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.

Discuss

Discussion

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


Answer the Question

You must create an account to use the forum. Create an Account or Login