Help with find/replace

[Karen53]

Hi,

I've been going around with this and can't find what is wrong. All
replacements to 'Tablespg' occur as they should. All changes to
'LineItemspg' do not. I have double checked the columns involved on
'LineItemspg' and they are correct. The Debug.Prints return the correct
values. Does anyone have any suggestions?

Sub UpdatePool()

Dim Choice As Long
Dim NewPool As String
Dim OldPool As String

Application.ScreenUpdating = False

Choice = frmPoolList.lboPoolList.ListIndex + 1
'plus 1 because the listbox begins with 0
NewPool = frmPoolList.txtNewPoolType

'determine which Pool List to save to and save
If frmPoolList.lboPoolList.RowSource = Worksheets(Replace(Tablespg.Name,
"", "''")).Range _

("CAMPoolTypes").Address(external:=True) Then
OldPool = Range("CAMPoolTypes").Item(Choice, 1).Value
Range("CAMPoolTypes").Item(Choice, 1).Value = NewPool

If OldPool <> "" Then
'Update Line Items page with new value if old value not blank
LineItemspg.Columns("K:K").Replace What:=OldPool, _
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Line Item Page NewPool " & NewPool & " OldPool " &
OldPool

'Update Tables page Exterior Range "X" with new value
Tablespg.Columns("X:X").Replace What:=OldPool, _
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Tables Page exterior NewPool " & NewPool & "
OldPool " & OldPool

'Update Tables Page Interior Range "AD" with new value
Tablespg.Columns("AD:AD").Replace What:=OldPool, _
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Tables Page interior NewPool " & NewPool & "
OldPool " & OldPool
End If
End If
If frmPoolList.lboPoolList.RowSource = Worksheets(Replace(Tablespg.Name,
"", "''")).Range _

("TaxPoolTypes").Address(external:=True) Then
OldPool = Range("TaxPoolTypes").Item(Choice, 1).Value
Range("TaxPoolTypes").Item(Choice, 1).Value = NewPool

If OldPool <> "" Then
'Update Line Items page with new value if old value not blank
LineItemspg.Columns("K:K").Replace What:=OldPool, _
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Tax Line Item Page NewPool " & NewPool & " OldPool
" & OldPool

'Update Tables page range "AI" with new value
Tablespg.Columns("AI:AI").Replace What:=OldPool, _
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Tables Page tax NewPool " & NewPool & " OldPool " &
OldPool
End If

End If

'Reset
NewPool = ""
OldPool = ""
'clear the textbox
frmPoolList.txtNewPoolType.Value = ""
frmPoolList.txtLineItemAmount.Value = ""
'set focus to list box
frmPoolList.lboPoolList.SetFocus

Application.ScreenUpdating = True

End Sub

 

[Karen53]

Hi,

Sorry, the first LineItemspg is columns F. I had copied and pasted the one
below thinking I may have a typo I wasn't seeing and didn't change it before
pasting it here.

Either way, it's not working on the LineItemspg.

'Update Line Items page with new value if old value not blank
LineItemspg.Columns("F:F").Replace What:=OldPool, _
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Line Item Page NewPool " & NewPool & " OldPool " &
OldPool

 

[Mark Ivey]

Do you have an extra space or any hidden characters that may prevent an
exact match?