Hi All
I need to upgrade this macro that I have written using the Macro Record function.
The basics are that I wish to look up the text EUR in column G and every time if finds that value Copy across to the cell to the left in Column F and Delete the text EUR leaving just the numerical value. The next step is to delete the original cell insert the exchange value into the row on Column I and perform the calculation to give a dollar value in the starting cell in column G.
I have been successful with the calculations and the copying but currently it only works for a single cell and row not the whole data set.
here is the code
and here is a sample data set.
<tbody>
</tbody>
I need to upgrade this macro that I have written using the Macro Record function.
The basics are that I wish to look up the text EUR in column G and every time if finds that value Copy across to the cell to the left in Column F and Delete the text EUR leaving just the numerical value. The next step is to delete the original cell insert the exchange value into the row on Column I and perform the calculation to give a dollar value in the starting cell in column G.
I have been successful with the calculations and the copying but currently it only works for a single cell and row not the whole data set.
here is the code
Code:
Sub Euro_Correction()'
' Euro_Correction Macro
'
' Keyboard Shortcut: Option+Cmd+Shift+E
'
Columns("G:G").Select
Selection.Find(What:="EUR", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Range("G36").Select
Selection.Copy
Range("F36").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Replace What:="EUR", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:="EUR", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/RC[2])"
Range("H36").Select
ActiveCell.FormulaR1C1 = ""
Range("I36").Select
ActiveCell.FormulaR1C1 = "0.58026923"
End Sub
and here is a sample data set.
A | B | C | D | E | F | G | H | I | J |
Co./Last Name | Date | Item Number | Qty | Desc. | Euro | Price | Total | Forex | SalesRep |
cust 1 | 1/3/15 | 1234 | 1 | $300 | 0 | ||||
cust 1 | 1/3/15 | 1235 | 1 | $400 | 0 | ||||
cust 2 | 1/3/15 | 1236 | 2 | $500 | 0 | ||||
cust 2 | 1/3/15 | 1237 | 1 | $600 | 0 | ||||
cust 3 | 4/3/15 | 1234 | 3 | EUR100 | .58926 | ||||
cust 3 | 4/3/15 | 1235 | 1 | EUR200 | .58926 | ||||
cust 4 | 5/3/15 | 1236 | 1 | EUR300 | .58926 | ||||
cust 4 | 5/3/15 | 1237 | 4 | EUR300 | .58926 | ||||
cust 5 | 5/3/15 | 1238 | 2 | $700 | 0 |
<tbody>
</tbody>