Search and replace with macro?

  • Thread starter Thread starter Tobias Johansson
  • Start date Start date
T

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
 
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.

Tobias said:
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
Click to expand...
 
Thank you Dave!
Worked like a charm!

Best regards,
Tobbe Gårdner


Dave Peterson said:
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.

Tobias said:
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
Click to expand...
Click to expand...
 
Back
Top