PDA

View Full Version : Find and Change Values



StephanB
08-15-2017, 01:34 AM
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
08-15-2017, 03:21 AM
Is it possible to solve this issue with a macro? I tried using the .find method, but not getting the right results.

mdmackillop
08-15-2017, 04:39 AM
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
08-15-2017, 04:49 AM
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
08-15-2017, 07:21 AM
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
08-15-2017, 07:36 AM
Hi Mana
Definitely neater but see post #4
Also, typo here.

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

mana
08-15-2017, 07:56 AM
Thank you for your advice.

StephanB
09-12-2017, 07:31 AM
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
09-12-2017, 07:55 AM
the stepwise search procedure is now with a stepsize of "2"
I don't follow what you're trying to do.

StephanB
09-13-2017, 12:12 AM
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
09-13-2017, 01:18 AM
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
09-13-2017, 02:08 AM
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?