Find and Change Values

[StephanB]

Hi everyone,

I am quite new to VBA and I am looking for a solution to my problem:

I have a excel table with 4 columns. In the first column there is a value for time period, in the second a value for an ID and in the third and fourth a value belonging to the specific ID.

There are now values of "N/A" in the table in column C and D which should be replaced with a stepwise find-method regarding the time period value from column A (maximum + 3 steps ahead and maximum -3 steps back).

For example:
The frist N/A value (3rd columm and 2nd row), which belongs to the ID "RTvc12" in row 2 which has a time period value of "0", should be replaced by doing a stepwise search in the range for the time period value 1 (0+1=1). If the ID exists in that time period range, the value of column C is taken to replace the original value. If not or if the value there is also "N/A", then proceed with searching in the range 2 (1+1=2). This has to be done until +3 and if nothing found then -1, then -2, then -3. If no value could be found in the range +3 and -3 from the starting time value, then the "N/A" should be set to "0".

The change of the values has to be taken place after the complete search and check of all "N/A" values.
I have real trouble to find a solution....

The last two columns in red in the table are the solutions for the "N/A" values which ought to be the result.

Table is attached.
Thanks in advance for your help! Much appreciated!
Best
Stephan

[StephanB]

Is it possible to solve this issue with a macro? I tried using the .find method, but not getting the right results.

[mdmackillop]

This gives your results except for D5. The only value for Xct56 is D27 which is +4, unless you count the derived value in D10

Option Explicit
Dim chk As Boolean


Sub Test()
    Dim r As Range, c As Range
    Dim Col(), a
    Dim x$
    Col = Array(3, 4)
    For Each a In Col
        Set r = Range(Cells(2, a), Cells(Rows.Count, a).End(xlUp))
        For Each c In r
            If c.Value = "N/A" Then
                x = SearchForward(c.Offset(, 2 - a), c.Offset(, 1 - a), a)
                If x = 0 And chk = False Then
                    x = SearchBack(c.Offset(, 2 - a), c.Offset(, 1 - a), a)
                End If
                With c
                    .Value = x
                    .Interior.ColorIndex = 6
                End With
            End If
        Next c
    Next a
End Sub


Function SearchForward(ID As Range, Zeit, a)
    Dim cel As Range, FA$
    chk = False
    With Columns(2)
        Set cel = .Find(ID.Value, lookat:=xlWhole, after:=ID, searchdirection:=xlNext)
        If Not cel Is Nothing Then
            FA = cel.Address
            Do
                If cel.Row < ID.Row Then GoTo Exits
                If cel.Offset(, -1) - Zeit <= 3 And cel.Offset(, a - 2) <> "N/A" Then
                    SearchForward = cel.Offset(, a - 2): chk = True
                    Exit Function
                End If
            
            Set cel = .FindNext(cel)
        Loop Until cel.Address = FA
        End If
    End With
Exits:
    SearchForward = 0
End Function


Function SearchBack(ID As Range, Zeit, a)
    Dim cel As Range, FA$
    With Columns(2)
        Set cel = .Find(ID.Value, lookat:=xlWhole, after:=ID, searchdirection:=xlPrevious)
        If Not cel Is Nothing Then
            FA = cel.Address
            Do
                If cel.Row > ID.Row Then GoTo Exits
                If cel.Offset(, -1) - Zeit >= -3 And cel.Offset(, a - 2) <> "N/A" Then
                    SearchBack = cel.Offset(, a - 2)
                    Exit Function
                End If
            
            Set cel = .FindNext(cel)
        Loop Until cel.Address = FA
        End If
    End With
Exits:
    SearchBack = 0
End Function

[StephanB]

Great! I'll have to check in detail what you coded. And yes, the D5 value, since the only value for Xct56 is D27 which is +4, should then set to "0" since it is out of range +3..-3.

[mana]

Option Explicit


Sub test()
    Dim dic As Object
    Dim r As Range, v
    Dim i As Long, s As String, t
    
    Set dic = CreateObject("scripting.dictionary")
    Set r = Cells(1).CurrentRegion
    v = r.Value
    
    For i = 2 To UBound(v)
        s = v(i, 1) & vbTab & v(i, 2)
        dic(s) = i
    Next
    
    For i = 2 To UBound(v)
        If v(i, 3) = "N/A" Then
            For Each t In Array(-3, -2, -1, 3, 2, 1)
                s = v(i, 1) + t & vbTab & v(i, 2)
                If dic.exists(s) Then
                    If v(dic(s), 3) <> "N/A" Then v(i, 3) = v(dic(s), 3)
                End If
            Next
            If v(i, 3) = "N/A" Then v(i, 3) = 0
        End If
        
        If v(i, 4) = "N/A" Then
            For Each t In Array(13, -2, -1, 3, 2, 1)
                s = v(i, 1) + t & vbTab & v(i, 2)
                If dic.exists(s) Then
                    If v(dic(s), 4) <> "N/A" Then v(i, 4) = v(dic(s), 4)
                End If
            Next
            If v(i, 4) = "N/A" Then v(i, 4) = 0
        End If
    Next

   r.Value = v
   
End Sub

[mdmackillop]

Hi Mana
Definitely neater but see post #4
Also, typo here.

For Each t In Array(13, -2, -1, 3, 2, 1)

[mana]

Thank you for your advice.

[StephanB]

Hi mdmackillop,

I'd like to modify your idea of the search procedure in that way that the stepwise search procedure is now with a stepsize of "2" for example and not "1". So not a stepsize of "1", searching from 1, then 2 and then 3, etc., but instead searching directly in the range of "2", then "4" and then "6".

Do you have an idea what changes have to be made?

Thanks in advance for your help!
Best
Stephan

This gives your results except for D5. The only value for Xct56 is D27 which is +4, unless you count the derived value in D10

Option Explicit
Dim chk As Boolean


Sub Test()
    Dim r As Range, c As Range
    Dim Col(), a
    Dim x$
    Col = Array(3, 4)
    For Each a In Col
        Set r = Range(Cells(2, a), Cells(Rows.Count, a).End(xlUp))
        For Each c In r
            If c.Value = "N/A" Then
                x = SearchForward(c.Offset(, 2 - a), c.Offset(, 1 - a), a)
                If x = 0 And chk = False Then
                    x = SearchBack(c.Offset(, 2 - a), c.Offset(, 1 - a), a)
                End If
                With c
                    .Value = x
                    .Interior.ColorIndex = 6
                End With
            End If
        Next c
    Next a
End Sub


Function SearchForward(ID As Range, Zeit, a)
    Dim cel As Range, FA$
    chk = False
    With Columns(2)
        Set cel = .Find(ID.Value, lookat:=xlWhole, after:=ID, searchdirection:=xlNext)
        If Not cel Is Nothing Then
            FA = cel.Address
            Do
                If cel.Row < ID.Row Then GoTo Exits
                If cel.Offset(, -1) - Zeit <= 3 And cel.Offset(, a - 2) <> "N/A" Then
                    SearchForward = cel.Offset(, a - 2): chk = True
                    Exit Function
                End If
            
            Set cel = .FindNext(cel)
        Loop Until cel.Address = FA
        End If
    End With
Exits:
    SearchForward = 0
End Function


Function SearchBack(ID As Range, Zeit, a)
    Dim cel As Range, FA$
    With Columns(2)
        Set cel = .Find(ID.Value, lookat:=xlWhole, after:=ID, searchdirection:=xlPrevious)
        If Not cel Is Nothing Then
            FA = cel.Address
            Do
                If cel.Row > ID.Row Then GoTo Exits
                If cel.Offset(, -1) - Zeit >= -3 And cel.Offset(, a - 2) <> "N/A" Then
                    SearchBack = cel.Offset(, a - 2)
                    Exit Function
                End If
            
            Set cel = .FindNext(cel)
        Loop Until cel.Address = FA
        End If
    End With
Exits:
    SearchBack = 0
End Function

[mdmackillop]

the stepwise search procedure is now with a stepsize of "2"

I don't follow what you're trying to do.

[StephanB]

well, right now the search is performed by searching the value stepwise forward in the +1-range, then +2-range until +3-range and when nothing is found the search is performed stepwise backwards in the -1-range, then -2-range until the -3-range.

What I would like to do is that the search directly jumps to the -2 or +2 range without searching the +1 or -1 range.

Or even better: A flexible search with a parameter as stepsize.

For example: If I have an ID in the time-period range of "2" and I like to search if the same ID appears in the range "+2" (2+2=4) then the search should directly search in time-period range of "4" and picking the value without looking in the ranges between.

[mdmackillop]

My code is not really amenable to that change of stepped searches. Possibly Mana's code is more easily adapted.
In this line

cel.Offset(, -1) - Zeit <= 3

you could use a variable to change the limit of the search.

[StephanB]

Thanks, yes maybe Mana's code is more flexible in this reagrd, but if I use Mana's code, all the "N/A" values get replaced by "0" and not by the found values in Column C as your code does. Can you see why this code isn't working?