VBA Code - If Code #1234 found in ColumnA then result code 5678 return in ColumnC

[murthysri272]

Hi,
Could you please help me to find the VBA code for below scenario.

I have data in Column A, ColumnB, ColumnC.

Check code #1234 in ColumnA. If it finds, 5678 should return in corresponding cells for Column C.
Else
Check code #ABCD in ColumnA. If it finds, EFGH should return in corresponding cells for Column C.


Regards,
Sri

[SamT]

Sub t()
Dim Found As Range
Set Found = Columns(1).find("#1234")
If Not Found is nothing then
Found.Offset( 2) = 5678
Else
Set Found = Columns(1).find("#ABCD")
If Not Found is nothing then
Found.Offset( 2) = "EFGH"
End If
End If
End Sub

[murthysri272]

Hi,
I think, I have not conveyed my scenario to you.
Please find detailed explanation of scenario:

Find "ABCDE" in ColumnA (Example Range from A20 to A3000) then replace it with "FGHIJ" as a result in column C.Means, if "ABCDE" present in ColumnA of Cell A30 then result "FGHIJ" return to Column C of cell C30.


Regards,
Sri

[SamT]

Sub t()
Dim Found As Range
Dim ColumnA As Range

Set ColumnA = Range("A20:A3000")

Set Found = ColumnA.find("ABCDE")
If Not Found Is Nothing Then
Found.Offset(, 2) = "FGHIJ"
End If
End Sub

[Paul_Hossler]

Hi,
Find "ABCDE" in ColumnA (Example Range from A20 to A3000) then replace it with "FGHIJ" as a result in column C.Means, if "ABCDE" present in ColumnA of Cell A30 then result "FGHIJ" return to Column C of cell C30.


Does ABCDE occur more that one time in column A?

[murthysri272]

Hi,
If There are 3-4 occurrences of "ABCDEF" in columnA, then the code is working only for 1st occurrence and rest are unchanged. How can we achieve this change for all occurrences of "ABCDEF" in columnA?
Please suggest.

[SamT]

Option Explicit

Sub VBAX_SamT_ReplaceInC()
Dim Found As Range
Dim ColumnA As Range
Dim FirstAddress As String
Set ColumnA = Range("A20:A3000")

Set Found = ColumnA.Find("ABC")
If Not Found Is Nothing Then
firstAddress = Found.Address
Do
Found.Offset(, 2) = "EFG"
Set Found = .FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> firstAddress
End If

End Sub

[SamT]

Option Explicit

Sub VBAX_SamT_ReplaceAnyInC(WkShtName As String, FindString As Variant, ReplaceString As Variant)
Dim Found As Range
Dim ColumnA As Range
Dim FirstAddress As String

With Sheets(WkShtName)
Set ColumnA = Sheets(WkShtName).Range("A20:A3000")

Set Found = ColumnA.Find(FindString)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Offset(, 2) = ReplaceString
Set Found = .FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
End Sub



Sub UsageExample1()
VBAX_SamT_ReplaceAnyInC "Sheet1", "ABC", "DEF"
End Sub


Sub UsageExample2()
VBAX_SamT_ReplaceAnyInC "Sheet1", "ABC", 123
End Sub


Sub UsageExample3()
VBAX_SamT_ReplaceAnyInC "Sheet1", 456, "DEF"
End Sub

[murthysri272]

Hi,
I am trying the below option and getting error at Red line (Object doesn't support this property or method).



Sub VBAX_SamT_ReplaceAnyInC(WkShtName As String, FindString As Variant, ReplaceString As Variant)
Dim Found As Range
Dim ColumnA As Range
Dim FirstAddress As String

With Sheets(WkShtName)
Set ColumnA = Sheets(WkShtName).Range("A20:A3000")

Set Found = ColumnA.Find(FindString)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Offset(, 2) = ReplaceString
Set Found = .FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
End Sub
-------------
Sub UsageExample1()
VBAX_SamT_ReplaceAnyInC "Sheet1", "ABC", "DEF"
End Sub



Note:- I have to find and replace more than 1 occurrences.


Regards,
Sri

[murthysri272]

Yes, ABCDE occurs in more than onetime in columnA

[SamT]

Strange

Try replacing FindNext(Found) with FindNext(findString).

[murthysri272]

Hi,
No luck with it. Getting Run-time error 438 : Object doesn't support this property or method.


Regards,
Sri

[murthysri272]

Does ABCDE occur more that one time in column A?

HI,
Could you please provide some help to achieve the mentioned scenario.


Regard,
Sri