PDA

View Full Version : VBA Code - If Code #1234 found in ColumnA then result code 5678 return in ColumnC



murthysri272
02-17-2016, 02:25 PM
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
02-17-2016, 03:53 PM
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
02-17-2016, 06:38 PM
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
02-17-2016, 07:27 PM
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
02-17-2016, 07:33 PM
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
02-17-2016, 07:33 PM
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
02-17-2016, 08:20 PM
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
02-17-2016, 08:28 PM
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
02-18-2016, 09:02 AM
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
02-18-2016, 09:47 AM
Yes, ABCDE occurs in more than onetime in columnA

SamT
02-18-2016, 02:31 PM
Strange

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

murthysri272
02-18-2016, 03:26 PM
Hi,
No luck with it. Getting Run-time error 438 : Object doesn't support this property or method.


Regards,
Sri

murthysri272
02-19-2016, 08:44 AM
Does ABCDE occur more that one time in column A?

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


Regard,
Sri