Need modification to code....

Posted by travis on January 11, 2002 6:23 AM

I have two columns of data. I want to search column A for a specific value (X). When I find that value, I want to change the data in the same row but in column B to a different value (y). For example, when I find the number 22 anywhere in column A, I want the data in the same row, column B to be "hello".

I have this code and it works great! Though, I need a slight modification so I can select the column I want to search and the column I want to modify. This code will only allow me to replace information in the column next to my search column. I only do minimal programming so I don't understand all of what this code is doing. Any help would be awesome. Thanks!

Dim rng As Range
Set rng = Range([A1], [A65536].End(xlUp))
Application.ScreenUpdating = False
Columns(3).Insert
With rng.Offset(0, 2)
.FormulaR1C1 = "=IF(RC[-2]=22,""hello"",RC[-1])"
.Copy
.PasteSpecial Paste:=xlValues
End With
Columns(2).Delete

Posted by Gonzalo on January 11, 2002 6:36 PM

Try this :-

Dim searchCol As Variant, searchRng As Range
Dim modifyCol As Variant, modifyRng As Range
Dim colDiff As Integer

Do
searchCol = Application.InputBox(Prompt:="Enter the search column number", Type:=1)
If searchCol = "False" Then Exit Sub
If Val(searchCol) > 256 Then
MsgBox "You must enter a number between 1 and 256"
Else
Exit Do
End If
Loop

Do
r:
modifyCol = Application.InputBox(Prompt:="Enter the modify column number", Type:=1)
If modifyCol = "False" Then
If MsgBox("Do you want to continue", vbYesNo, "Continue?") = vbNo Then
Exit Sub
Else: GoTo r
End If
End If
If Val(modifyCol) > 256 Then
MsgBox "You must enter a number between 1 and 256"
Else
Exit Do
End If
Loop

colDiff = searchCol - modifyCol
Set searchRng = Range(Cells(1, searchCol), Cells(65536, searchCol).End(xlUp))
Set modifyRng = searchRng.Offset(0, -colDiff)

Application.ScreenUpdating = False
With modifyRng
.FormulaR1C1 = "=IF(RC[" & colDiff & "]=22,""hello"",RC[" & colDiff & "])"
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False

Posted by Gonzalo on January 11, 2002 7:13 PM

Or .....

..... perhaps the following revised last two paragraphs of code produce what you need :-

colDiff = searchCol - (modifyCol + 1)
Set searchRng = Range(Cells(1, searchCol), Cells(65536, searchCol).End(xlUp))
Set modifyRng = searchRng.Offset(0, -colDiff)

Application.ScreenUpdating = False
modifyRng.EntireColumn.Insert
With modifyRng.Offset(0, -1)
.FormulaR1C1 = "=IF(RC[" & colDiff & "]=22,""hello"",RC[-1])"
.Copy
.PasteSpecial Paste:=xlValues
End With
modifyRng.Offset(0, -2).EntireColumn.Delete