PDA

View Full Version : Solved: Replace macro



maxflia10
08-27-2005, 08:46 AM
I have the following macro,

Option Compare Text

Sub test2()
Dim x As Integer
If [I122].Value = "Trade" Then
With Range("B6:H119")
For x = 122 To 139
.Replace What:=Range("E" & x), Replacement:=Range("H" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next x
End With
Range("E122").Select
End If
End Sub

However if I122 does not equal trade, the macro stops checking. What I need is for the macro to check each row from I122:I139, then do a find and replace on E and H.

Thanks for your time...

mdmackillop
08-27-2005, 09:35 AM
Try the following. I've added a line to highlight changed cells as yellow for testing purposes.

Sub Test()
Dim x As Integer, c As Range
If [I122].Value = "Trade" Then
With Range("B6:H119")
For x = 122 To 139
Set c = .Find(What:=Range("E" & x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = Range("H" & x)
Set c = .FindNext(c)
'Remove next line after test
c.Interior.ColorIndex = 3
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next x
End With
Range("E122").Select
End If
End Sub

maxflia10
08-27-2005, 06:24 PM
Thank-you! I will try this and report back.

Edit,

Reporting back. I have this error

Object variable or with block variable not set.

Essentially what I want the macro to do is IF I122 = trade (not case sensitive), replace E122 with H122 so on and so on down to row 139. If column I does not equal trade, do not find/replace

Thank-you

Dr Who
08-28-2005, 01:04 AM
Option Explicit
Option Compare Text

Sub TryThis()
Dim Cell As Range
For Each Cell In [I122:I139]
If Cell = "Trade" Then
Range("E" & Cell.Row) = Range("H" & Cell.Row)
End If
Next
[E122].Select
End Sub

maxflia10
08-28-2005, 06:34 AM
Option Explicit
Option Compare Text

Sub TryThis()
Dim Cell As Range
For Each Cell In [I122:I139]
If Cell = "Trade" Then
Range("E" & Cell.Row) = Range("H" & Cell.Row)
End If
Next
[E122].Select
End Sub

Apologies, my explanation is wrong. In the range B6:H199, if I122 = Trade, replace E122 with H122, and so on looking at rows 122:139 for criteria and replacing in range from B6:H199