CODEWITHSUNDEEP
vbaexcel
Gerry
Gerry

Reputation: 31

find values between range, fill in next cells

I'm looking for a way for copying down all data found between a range and paste it in a next column.

A= text data

B= Random numbers but always starting from 1

C= some data

D= My needed solution

Example:
     A          B   C    D
    018404.00   1   20  20
    018404x0    2   0   20
    018404f1    2   0   20
    018404v1    3   0   20
    11000-0532  4   0   20
    1004-1101   5   0   20
    0720-0125   3   0   20
    0810-0001   3   0   20
    0710-0040   3   0   20
    052269.00   1   0   80
    052269v6    2   0   80
    11001-0000  3   0   80
    1001-1110   4   0   80
    0720-0500   2   0   80
    0810-0001   2   80  80
    0720-0002   2   0   80
    052275.00   1   0   160
    052275v2    2   160 160

When the value in column B is 1 then find value in column C (in Range B:B from 1 to 1) copying it to D

I have tried it with a formula but this limits the depth. If value on Column C isto far from the 1 row it doesn't work.

=IF(AND(B2=1;C2=0);IF(B3=1;0;IF(C3=0;IF(C4=0;C5;C4);C3));IF(C2>0;C2;I1))

So I think I need a vba solution.

Upvotes: 0

Views: 1274

Answers (1)

Dawid
Dawid

Reputation: 786

i think you need something like this. This code search max value between your 1 and 1 value

Sub FindValBetweenOne()

Dim LastRow As Long
Dim FindVal As Long

LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For I = 1 To LastRow
    If Cells(I, "B").Value = 1 Then 'find next "1"
        FindVal = Cells(I, "C").Value
        J = I + 1
        Do While (J <= LastRow And Cells(J, "B").Value <> 1)
            If Cells(J, "C").Value > FindVal Then
            FindVal = Cells(J, "C")
            End If
            J = J + 1
        Loop            
    End If
    Cells(I, "D").Value = FindVal
Next I

End Sub

Upvotes: 1

Related Questions