Find a number then paste in next column (vba)

[Mood]

I have a dilemma. What I’m trying to do is find a number in column C, then paste that number in the same row, but in column D.

I would like a dialogue box to ask for a number (eg. 36) from 1 to 2000 only.


Then search in column C for this number (36). There could be more than one occurrence of this number, so I would like to find the last number in column C which would also be blank in column D.


Then paste this number (36) into column D.


I would also need to cancel the operation and allow for any errors, which could be made. Eg. inputting text or allow for stopping the process.

Col C.. Col D
1990....1990
36......36
500.....500
36......___ this blank space here is the one to find
36......36

I have tried searching, but no luck.

Many thanks for helping and taking the time to read,
Peter

[Russell Hauf]

Try this:

Sub FindAndPaste() 



    Dim lngLastRow As Long 

    Dim lngRow As Long 

    Dim varNumToFind As Variant 

    

    varNumToFind = Application.InputBox(Prompt:="Please enter a number", Type:=1) 

    

    lngLastRow = ActiveSheet.Range("C65536").End(xlUp).Row 

    

    For lngRow = lngLastRow To 1 Step -1 

        If Cells(lngRow, 3) = varNumToFind And Trim(Cells(lngRow, 4).Text) = "" Then 

            Cells(lngRow, 4) = Cells(lngRow, 3) 

            Exit For 

        End If 

    Next lngRow 



End Sub 

Hope it helps,

Russell

[Mood]

Thanks Russell, works slick...

I've been trying to figure out the "for" statement, with limited success. With time and the help file should eventually crack it open.

Again thanks for the quick response.

Peter