Results 1 to 1 of 1

Thread: Explanation requested on macros recorder and the way to make it working

  1. #1

    Explanation requested on macros recorder and the way to make it working



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,

    I ask for help because I am not a programmer. I allways wanted to use excel macros. I was advised several times to use macros. But I never get it working.

    So here I tried to make computations on a workbook (workboook 1 / name: Calcul sur data)where I submit ID information (for example 3 differents ID). The macro must recall related informations from another workbook (workboook 2/ name: source of data) and copy all the related information in the workbook 1.

    Workbook 2 contains for instance 10 ID with corresponding values
    A 2
    B 2
    C 8
    D 1
    ....
    so I f I give in the macros of workbook 1: look for A and D-->
    it must recall A 2
    D 1

    Then I want automatic calculations Dividing / substracting and percentage.
    ...

    I did everything manually, but then when I change in workbook 1 to look not for A and D but for B E and F... it is not working at all.

    It is not seeking for values that are given but copied the values I made the first time.


    %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

    I thank you all for your help / suggestion
    Below the code:
    %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


    Code:
    Sub TestCalculation()
    Cells.Find(What:="article Nr", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("B2").Select
    Selection.Copy
    Windows("source data.xlsx").Activate
    Cells.Find(What:="RM01", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("D2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("data.xlsx").Activate
    Range("B2").Select
    Cells.Find(What:="RM01", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("F2").Select
    ActiveSheet.Paste
    Cells.Find(What:="RM01", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("source data.xlsx").Activate
    Cells.Find(What:="RM05", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("D6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("data.xlsx").Activate
    Cells.Find(What:="RM05", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("F3").Select
    ActiveSheet.Paste
    Cells.Find(What:="RM05", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("source data.xlsx").Activate
    Cells.Find(What:="RM03", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("D4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("data.xlsx").Activate
    Cells.Find(What:="RM03", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("F4").Select
    ActiveSheet.Paste
    Cells.Find(What:="RM03", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUMME"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-3]C)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/R[5]C[-1]"
    Range("E2").Select
    Selection.Copy
    Range("E3").Select
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-1]/R7C4"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/R7C4"
    Range("E3").Select
    Selection.Copy
    Range("E4").Select
    ActiveSheet.Paste
    Range("G2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
    Range("G2").Select
    Selection.Copy
    Range("G3").Select
    ActiveSheet.Paste
    Range("G4").Select
    ActiveSheet.Paste
    Range("G5").Select
    ActiveSheet.Paste
    Range("G7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-3]C)"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-7]"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]"
    Range("H8").Select
    End Sub
    Last edited by p45cal; 2015-11-01 at 12:05 AM. Reason: added code tags around code and removed blank lines which prevented code running

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •