Search and replace with macro?

[Tobias Johansson]

Hi,

I have an excel sheet with product information. Each product contains
several items with the following columns:
- quantity (of each item), column B
- item ID
- item text
- net cost, column F
- cost (quantity * net cost), column G

All information regarding the items are placed on a separate worksheet.

I would like to merge the 'net cost' column and the 'cost' column.

Today the formula looks like this:
column F (net cost): ='Item list'!K66
column G (cost): =F16*B16

I want the following formula in column F:
='Item list'!K66*B16

I have tried to create a macro but my problem is that it "locks" to a
certain cell when I would like it to "move along" after it has replaced the
content of one cell. The macro looks like this:

Sub Macro2()
Cells.Find(What:="item list", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Range("G7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "='Item list'!R[60]C[5]*RC[-4]"
Range("F8").Select
End Sub

Could anyone give me any hints on how to fix the macro or possibly if you
have another solution to the problem?

Best regards,
Tobbe Gårdner

 

[Dave Peterson]

Instead of a macro, can you use =vlookup()?

If you have a key value and a table (the first column of which is a list of
those key values), you can retrieve values to the right of that first column.

If I had a value in A17 of sheet1 and a table on sheet2 $a$1:$g$9999, I could
retrieve the data in column G like:

=vlookup(a17,sheet2!$a$1:$g$9999,7,false)

If I sort sheet1, then the formulas will bring the right data back.

Hi,

I have an excel sheet with product information. Each product contains
several items with the following columns:
- quantity (of each item), column B
- item ID
- item text
- net cost, column F
- cost (quantity * net cost), column G

All information regarding the items are placed on a separate worksheet.

I would like to merge the 'net cost' column and the 'cost' column.

Today the formula looks like this:
column F (net cost): ='Item list'!K66
column G (cost): =F16*B16

I want the following formula in column F:
='Item list'!K66*B16

I have tried to create a macro but my problem is that it "locks" to a
certain cell when I would like it to "move along" after it has replaced the
content of one cell. The macro looks like this:

Sub Macro2()
Cells.Find(What:="item list", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Range("G7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "='Item list'!R[60]C[5]*RC[-4]"
Range("F8").Select
End Sub

Could anyone give me any hints on how to fix the macro or possibly if you
have another solution to the problem?

Best regards,
Tobbe Gårdner

 

[Tobias Gårdner]

Thank you Dave!
Worked like a charm!

Best regards,
Tobbe Gårdner

Instead of a macro, can you use =vlookup()?

If you have a key value and a table (the first column of which is a list of
those key values), you can retrieve values to the right of that first column.

If I had a value in A17 of sheet1 and a table on sheet2 $a$1:$g$9999, I could
retrieve the data in column G like:

=vlookup(a17,sheet2!$a$1:$g$9999,7,false)

If I sort sheet1, then the formulas will bring the right data back.

Hi,

I have an excel sheet with product information. Each product contains
several items with the following columns:
- quantity (of each item), column B
- item ID
- item text
- net cost, column F
- cost (quantity * net cost), column G

All information regarding the items are placed on a separate worksheet.

I would like to merge the 'net cost' column and the 'cost' column.

Today the formula looks like this:
column F (net cost): ='Item list'!K66
column G (cost): =F16*B16

I want the following formula in column F:
='Item list'!K66*B16

I have tried to create a macro but my problem is that it "locks" to a
certain cell when I would like it to "move along" after it has replaced the
content of one cell. The macro looks like this:

Sub Macro2()
Cells.Find(What:="item list", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Range("G7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "='Item list'!R[60]C[5]*RC[-4]"
Range("F8").Select
End Sub

Could anyone give me any hints on how to fix the macro or possibly if you
have another solution to the problem?

Best regards,
Tobbe Gårdner