Struggling with finding a method to find and replace data

[Trying2learnVBA]

Hello,

I seem unable to find a way to first copy n paste say Column B:B to sheet2 say A:A (I do this fine lol)
Then I want to run a find and replace on Sheet1 col b:b without the find & replace doing the same thing to sheet2.
As I want to then copy sheet2 a:a to sheet1 a:a to create my cross reference.

This I what I have

The first part is deleting rows I do not need. Rows with 0.

Sub sbDelete_Rows_Based_On_Multiple_Criteria()

'''''''''''''''
'FICS MA ENTRY'
'''''''''''''''


Sheets("1").Select
Dim lRow As Long
Dim iCntr As Long
lRow = 20000
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 6) = 0 And Trim(Cells(iCntr, 7)) = 0 Then
Rows(iCntr).Delete
End If
Next
Columns("C:D").Delete
Range("B:B").Copy Destination:=Sheets("2").Range("A:A")


With Worksheets("1").Range("b:b")
.Replace "8000052", "80000260000-1"
.Replace "800162", "80000620000-2"
.Replace "809018", "80900180000-3"
.Replace "111026", "11440500000-4"
.Replace "131029", "6100010000-5"
.Replace "703010", "70400020000-6"
.Replace "703011", "70300010000-7"
.Replace "703013", "70300020000-8"

End With

End Sub

I have a lot more of these references but If I can get this to work - I could do the rest.
I also tried .range("b:b").replace....... it works but it changes my info on sheet two which I want to preserve


I basically get a file everyday that has all the numbers I want to match to my GL's(General Ledger accounts-accounting stuff.

So, the file only contains one set of the data- all the numbers after replace. the long numbers is what I want this macro to convert it to but I also want to leave the original number in col A my replace to number in col. B.

Any help Is greatly appreciated

 

[Fluff]

Try adding this line

Range("B:B").Copy Destination:=Sheets("2").Range("A:A")

[COLOR=#ff0000]Range("A1").Find ("XXXXX")
[/COLOR]
With Worksheets("1").Range("b:b")

 

[Trying2learnVBA]

I don't understand what is the find for.

Range("B:B").Copy Destination:=Sheets("2").Range("A1")

This step works.


Worksheets("1").Range("b:b").Select
Selection.Replace What:="8000052", Replacement:="80000260000-1"

This step replaces 8000052 on sheet1 and sheet2. ahhhh

I only want it to replace 8000052 to 80000260000-1 on sheet1.

 

[Fluff]

Did you actually try what I suggested?

 

[steve the fish]

Find and replace have various parameters such as 'entire cell contents' etc. Excel remembers the last used setting so the line Fluff added id have thought is there to reset the find replace dialog to 'normal'. The reason its changing it on both id imagine is because they are formulas. Try doing a paste special values instead of a paste if you need the values to be static.

 

[Fluff]

I'm thinking that the Replace is currently set for Workbook, Not sheet

 

[Trying2learnVBA]

Did you actually try what I suggested?

Fluff - I added your code and got an error.

I add the code with the xxx or I replace the xxx with my specific numbers?

and they are not formulas. I checked that.

It is csv file I download daily.

 

[Trying2learnVBA]

Yes!! That's it.

I had the find feature set to look in workbook.

is there a way for me to set code that only changes it in the sheet specified even if the find feat is set to workbook?

is there a way to code it easier? I am doing amateur style.. thts wha I am lol

I have good 100 cross references... using record macro to help I get this

Replace What:="800162", Replacement:="80000620000-2"
' Replace What:="809018", Replacement:="80900180000-3"
' Replace What:="111026", Replacement:="11440500000-4"
' Replace What:="131029", Replacement:="16100010000-5"
' Replace What:="703010", Replacement:="70400020000-6"
' Replace What:="703011", Replacement:="70300010000-7"
' Replace What:="703013", Replacement:="70300020000-8"
' Replace What:="703014", Replacement:="70600030000-9"
' Replace What:="703016", Replacement:="70500010000-10"
' Replace What:="703018", Replacement:="70400010000-11"
' Replace What:="703024", Replacement:="70500020000-12"
' Replace What:="703025", Replacement:="70600020000-13"
' Replace What:="800025", Replacement:="80800250000-14"

Can I simplify this?

 

[DataBlake]

Can I simplify this?

yes, if you can put all 100 cross refernces on a sheet named "Replacetxt" the following macro will work assuming that the thing you're trying to replace is the only value entered in the cell.

Sub test()
Dim ary As Variant

With Sheets("Replacetxt")
 ary = .Range("A1").CurrentRegion.Value2
End With

With ActiveSheet.Range("B:B")
 For i = 1 To UBound(ary)
  .Replace what:=ary(i, 1), replacement:=ary(i, 2), LookAt:=xlWhole
Next i
End With


End Sub

so your "Replace what" would be in column A and your "Replacement" would be in Column B on sheet "Replacetxt"
it will transfer these values to an array & then replace the text using 1st and 2nd values of the array until it reaches the end of the array
this code is also dynamic so it will do this to whatever sheet you're trying to run the code on/have selected as the active sheet

 

[Fluff]

Try

Sub sbDelete_Rows_Based_On_Multiple_Criteria()

'''''''''''''''
'FICS MA ENTRY'
'''''''''''''''


Sheets("1").Select
Dim lRow As Long
Dim iCntr As Long
lRow = 20000
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 6) = 0 And Trim(Cells(iCntr, 7)) = 0 Then
Rows(iCntr).Delete
End If
Next
Columns("C:D").Delete
Range("B:B").Copy Destination:=Sheets("2").Range("A:A")

[COLOR=#ff0000]Range("A1").Find "XXXX"[/COLOR]
With Worksheets("1").Range("b:b")
.Replace "8000052", "80000260000-1"
.Replace "800162", "80000620000-2"
.Replace "809018", "80900180000-3"
.Replace "111026", "11440500000-4"
.Replace "131029", "6100010000-5"
.Replace "703010", "70400020000-6"
.Replace "703011", "70300010000-7"
.Replace "703013", "70300020000-8"

End With

End Sub

The line in red will switch it back to looking at sheet level