ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with find/replace (https://www.excelbanter.com/excel-programming/403356-help-find-replace.html)

Karen53
Help with find/replace
 
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


--
Thanks for your help.
Karen53

Karen53
Help with find/replace
 
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

--
Thanks for your help.
Karen53


"Karen53" wrote:

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


--
Thanks for your help.
Karen53

Mark Ivey
Help with find/replace
 
Do you have an extra space or any hidden characters that may prevent an
exact match?



"Karen53" wrote in message
...
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

--
Thanks for your help.
Karen53


"Karen53" wrote:

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


--
Thanks for your help.
Karen53



All times are GMT +1. The time now is 02:53 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com