| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
| Find/Replace Event or Find/Replace for Protected Sheet ... | Excel Programming | |||
| find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
| Using Find and Replace to replace " in a macro | Excel Programming | |||
| Replace method - cannot find any data to replace | Excel Programming | |||
Hybrid Mode
