Creating a macro that will copy and paste an Index formula onto another worksheet

ABQ
Sep 7th 2017
Thread is marked as Resolved.

ABQ

Beginner

Points

75

Posts

11
Sep 7th 2017
- #1
I have two formulas that I am trying to create a macro for that will copy the formula from one sheet and paste the formula onto another sheet. The formulas I am using are:

Retrieve Model # =IFERROR(INDEX(AllParts.csv!$F:$F,MATCH($B3,AllParts.csv!$E:$E,0)),"")
Retrieve Description =IFERROR(INDEX(AllParts.csv!$H:$H,MATCH($B3,AllParts.csv!$E:$E,0)),"")

The formula references an external workbook (AllParts.csv), but the formulas are located on a separate sheet (Formulas) in the "Main" workbook. The "Main" workbook has the following:

[TABLE="width: 500"]
[tr]

[td][/td]

[td]
A
[/td]

[td]
B
[/td]

[td]
C
[/td]

[/tr]

[tr]

[td]
1
[/td]

[td]
Main
[/td]

[td]
Main
[/td]

[td]
Main
[/td]

[/tr]

[tr]

[td]
2
[/td]

[td]
Part #
[/td]

[td]
Model #
[/td]

[td]
Description
[/td]

[/tr]

[tr]

[td]
3
[/td]

[td]
101
[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]
4
[/td]

[td]
102
[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]
5
[/td]

[td]
103
[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]
6
[/td]

[td]
104
[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]
7
[/td]

[td]
105
[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]
8
[/td]

[td]
106
[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]
9
[/td]

[td]
107
[/td]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

I tried recording a macro that would copy and paste the formula into cell B3, it changes "B3" to "B5"
PCI

Super Moderator

Reactions Received

2

Points

20,762

Posts

4,148
Sep 10th 2017
- #2
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

"copy and paste the formula into cell B3, it changes "B3" to "B5""
Where are the original formulas ?
Can you attached a short sample of your data, it will help to understand
Triumph without peril brings no glory: Just try
ABQ

Beginner

Points

75

Posts

11
Sep 11th 2017
- #3
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

Spreadsheets uploaded

The OnHand Spreadsheet, I put the formulas into the first two areas, that is where I would like the macro to start putting in the formula
Files

OnHand.xlsx 11.54 kB – 87 Downloads AllParts.xlsx 8.93 kB – 94 Downloads
PCI

Super Moderator

Reactions Received

2

Points

20,762

Posts

4,148
Sep 11th 2017
- #4
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

You want to copy the first row and then paste it until the last row ?
BTW instead of the formulas used in the file attached, you could use a VLOOKUP ...!
Triumph without peril brings no glory: Just try
ABQ

Beginner

Points

75

Posts

11
Sep 11th 2017
- #5
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

Yes, unless there is an easier way to fill in both B and C columns with a macro. What is the advantage or differences between VLOOKUP and INDEX
PCI

Super Moderator

Reactions Received

2

Points

20,762

Posts

4,148
Sep 11th 2017
- #6
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

The VLOOKUP is more simple in the use, but the value used as reference must be in the first column.
It seems that in the file sent you use column "A" for the reference , is it?
Triumph without peril brings no glory: Just try
ABQ

Beginner

Points

75

Posts

11
Sep 11th 2017
- #7
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

yes, column a is the part number that I reference on both workbooks

Sep 11th 2017

#8

Re: Creating a macro that will copy and paste an Index formula onto another worksheet

Try

Code

Option Explicit


Sub Treat()
Dim WkRg  As Range
Dim LR As Integer
Const FR As Integer = 2
Const Col1 As String = "A"
Const Col2 As String = "B"
Const Col3 As String = "C"


    LR = Cells(Rows.Count, Col1).End(3).Row
    Set WkRg = Range(Cells(FR, Col2), Cells(LR, Col2)): WkRg.FillDown
    Set WkRg = Range(Cells(FR, Col3), Cells(LR, Col3)): WkRg.FillDown
End Sub

Display More

ABQ

Beginner

Points

75

Posts

11
Sep 11th 2017
- #9
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

If not too much to ask, can you explain the code and how it works? I am trying to learn code and would really like to pick apart different codes and mix and match them with others
ABQ

Beginner

Points

75

Posts

11
Sep 11th 2017
- #10
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

Also, how does this reference my other workbook to look up the values? I guess I am used to seeing the name of the workbook in the code, itself.
PCI

Super Moderator

Reactions Received

2

Points

20,762

Posts

4,148
Sep 11th 2017
- #11
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

If you do not have any knowledge about VBA it will take time.
What you could do to start: go step by step in the code using F8 key and see the effect in excel tab
Triumph without peril brings no glory: Just try
ABQ

Beginner

Points

75

Posts

11
Sep 11th 2017
- #12
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

thank you, I really appreciate your help
PCI

Super Moderator

Reactions Received

2

Points

20,762

Posts

4,148
Sep 11th 2017
- #13
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

The macro just Copy the formula already written in the first row and paste it down until the last row
Triumph without peril brings no glory: Just try
ABQ

Beginner

Points

75

Posts

11
Sep 11th 2017
- #14
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

Sorry my fault, guess I should have explained myself better. I posted the formula to show what I was using. Basically I want the macro to do the formula, the spaces for serial number and description would be blank, but filled in once the macro ran

Sep 12th 2017

#15

Re: Creating a macro that will copy and paste an Index formula onto another worksheet

Is it what you want

Code

Option Explicit


Sub Treat1()
Dim WkRg  As Range
Dim LR As Integer
Const FR As Integer = 2
Const Col1 As String = "A"
Const Col2 As String = "B"
Const Col3 As String = "C"
Const Formula1 As String = "=IFERROR(INDEX('[AllParts.xlsx]PartList'!$B:$B;MATCH($A2;'[AllParts.xlsx]PartList'!$A:$A;0));"""")"
Const Formula2 As String = "=IFERROR(INDEX('[AllParts.xlsx]PartList'!$D:$D;MATCH($A2;'[AllParts.xlsx]PartList'!$A:$A;0));"""")"
    
    LR = Cells(Rows.Count, Col1).End(3).Row
    With Range(Cells(FR, Col2), Cells(LR, Col2))
        .Cells(1, 1).FormulaLocal = Formula1
        .FillDown
    End With
    With Range(Cells(FR, Col3), Cells(LR, Col3))
        .Cells(1, 1).FormulaLocal = Formula2
        .FillDown
    End With
End Sub

Display More

ABQ

Beginner

Points

75

Posts

11
Sep 12th 2017
- #16
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

awesome work, thank you. I will make some minor mods, but this works perfectly
PCI

Super Moderator

Reactions Received

2

Points

20,762

Posts

4,148
Sep 12th 2017
- #17
Re: Creating a macro that will copy and paste an Index formula onto another worksheet

Good, you're welcome
Triumph without peril brings no glory: Just try

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

Users Viewing This Thread

1 Guest

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.