If cell value found then replace with adjacent cell

[teatimecrumpet]

Hi,

I have two columns with values. I want to include some vba code that will search the first column for specific text values and if found then replace it with the value in the adjacent column and row's value.

The code below works but it takes some time to process.


Is there a way to use a formula for this? Because I could then use a filter and then replace the visible values in the column with a formual that would pull the adjacent cells values.

Also how would I incorporate "+1" in the below quoted code to use the found range +1 column? (just curious for this one)

Here is my code that works but it takes a bit of time.

Sub changetheunknown()
Dim I As Long
Dim LR As Long



Sheets("sheet1").Activate

With Sheets("sheet1")
LR = Cells(Rows.Count, 6).End(xlDown).Row
For I = LR To 2 Step -1

Select Case Range("F" & I).Value
Case "UNKNOWN"
Range("F" & I).Value = Range("G" & I)
Case "DEFAULT"
Range("F" & I).Value = Range("G" & I)



Case Else
End Select

Next I
End With
End Sub

THANKS!

 

[John Davis]

Hi,

I have two columns with values. I want to include some vba code that will search the first column for specific text values and if found then replace it with the value in the adjacent column and row's value.

The code below works but it takes some time to process.


Is there a way to use a formula for this? Because I could then use a filter and then replace the visible values in the column with a formual that would pull the adjacent cells values.

Also how would I incorporate "+1" in the below quoted code to use the found range +1 column? (just curious for this one)

Here is my code that works but it takes a bit of time.




THANKS!

Does this work any faster??

Sub TEATIMECRUMPET()
Dim lr As Long
Dim rcell As Range
Dim col As Range

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 6).End(xlUp).Row

Set col = Range("F2:F" & lr)

    For Each rcell In col
    
        If rcell.Value = "UNKNOWN" Or rcell.Value = "DEFAULT" Then
                
        rcell.Offset(0, 1).Copy rcell
        
        End If
    
    Next

Application.ScreenUpdating = True

End Sub

 

[teatimecrumpet]

Hi John,


Your code does work faster. If you have the time could you explain the reasoning?


And how does this work:

rcell.Offset(0, 1).Copy rcell

It copies it but you don't have to "paste" it you can just put the rcell and vba will know to paste?

Thanks!

 

[John Davis]

Always when there's a need to speed up your code you should turn your screenupdating off and then on after your code runs:

Application.screenupdating = False

YOUR CODE

Application.screenupdating = True

In the code I used, I only tried a different approach in using a For Each Loop vrs the Select Case you used. In light of what I mentioned above, I can't testify which is faster or why. Just trial and error.

For your other question: If you notice when you are typing code in the VBE and using copy, if you just space over it'll ask you for a destination. There's is where you'd type your destination. It is one of the feature of Copy.

Other than that, I'm just a Novice at the game. Hope, that helps.

 

[teatimecrumpet]

Hi John,

Thanks again for the code and the explanation.