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

 

[Trying2learnVBA]

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

This works -Thank you. You were right - I had it to replace workbook not just the sheet.

Guys I've made so much progress thanks to you all.

I've ran into another problem.

my code:
Range("B:B").Replace "900017", "80900170000-70"
Range("B:B").Replace "9000177", "80901770000-71"

Excel seems to think Is the same reference.
[TABLE="width: 198"]
<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="152" style="width: 114pt; mso-width-source: userset; mso-width-alt: 5558;"> <tbody>[TR]
[TD="width: 112, bgcolor: transparent, align: right"]900017
[/TD]
[TD="width: 152, bgcolor: transparent"] > 80900170000-70
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9000177
[/TD]
[TD="bgcolor: transparent"]> 80900170000-707
[/TD]
[/TR]
</tbody>[/TABLE]
I added 1, 2, 3, etc at the end to help trouble shoot issues.

These have an issue as well:
Range("B:B").Replace "703024", "70500020000-12"
Range("B:B").Replace "1110107", "11430100000-22"
Range("B:B").Replace "111010710", "11430103010-46"

[TABLE="width: 255"]
<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="228" style="width: 171pt; mso-width-source: userset; mso-width-alt: 8338;"> <tbody>[TR]
[TD="width: 112, bgcolor: transparent, align: right"]70302410 >
[/TD]
[TD="width: 228, bgcolor: transparent"]70500020000-1210
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]111010710
[/TD]
[TD="bgcolor: transparent"]
> 11430100000-2210

[/TD]
[/TR]
</tbody>[/TABLE]

 

[DataBlake]

I've ran into another problem.

my code:
Range("B:B").Replace "900017", "80900170000-70"
Range("B:B").Replace "9000177", "80901770000-71"

you just need to add
LookAt:=xlWhole

https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
this link contains the syntax and usage for what you're looking for.

 

[Trying2learnVBA]

you just need to add
LookAt:=xlWhole

https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
this link contains the syntax and usage for what you're looking for.

Thanks for the info - I read the article. I am unsure where to add that piece of code.

Worksheets("1").Select
lookat:=xlWhole
Range("B:B").Replace "8000052", "80000260000-1"
Range("B:B").Replace "800162", "80000620000-2"
Range("B:B").Replace "809018", "80900180000-3"

Could you give me an example how to use it?
Also, is there a way to make a long code instead of one by one? Like if I could say col.b.replac "xxx", "vvv" and keep adding more "xxx", "vvvv" type code

 

[DataBlake]

Could you give me an example how to use it?

it would be used where you are establishing the rules of what you're doing

.Replace what:="xxxx", replacement:="yyyy", LookAt:=xlWhole

adding it once replaces the settings so you do not need it after every line.
so in your example it would be:

Range("B:B").Replace "8000052", "80000260000-1", LookAt:=xlWhole

 

[Trying2learnVBA]

it would be used where you are establishing the rules of what you're doing

.Replace what:="xxxx", replacement:="yyyy", LookAt:=xlWhole

adding it once replaces the settings so you do not need it after every line.
so in your example it would be:

Range("B:B").Replace "8000052", "80000260000-1", LookAt:=xlWhole

Thank you!! This works with one modification -
Columns("B").Replace "8000052", replacement:="80000260000-1", lookat:=xlWhole
Range("B:B").Replace "800162", "80000620000-2"

Range(xx) "xx", "xxx", lookat:=xwhole does not work.
Columns(xx. .. works.

The last part of my project -....

If B2 = xxxx then C2= a pre saved description. Is this possible?

Example.

A1 = 800002 B2 = 80000620000 IF A1 = 800002 THEN C2 = "PRE SET DESCRIPTION"

I don't think I need to complicate the code to say if a2 = xx and b2 = xxx then c2 = text description.
since A2 and B2 are the same unique references - I want to find code that gives me a text description for C2 based on A2 or B2. Either.
Doable?

 

[DataBlake]

If B2 = xxxx then C2= a pre saved description. Is this possible?

yes but i think its best you start a new thread for that is a separate topic of VBA to discuss.
for now i would recommend saving these kinds of informations in a table/separate sheet/separate workbook and you will have hundreds of ways to manipulate data as opposed to this one static way.
see you on your new post, you can post examples of tables / data by clicking here and using one of the methods of pasting tables to MrExcel