Posts by ABQ

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

Re: Insert formula into a range of cells

Awesome, it worked. Thank you very much

I am trying to use a simple code to insert a formula into a range of cells, but I keep getting a Run-time error 1004. Can someone explain what I am doing wrong?

Sub FormulaTest()
'
' FormulaTest Macro
'
'
    Range("B2:B7").Formula = "=IFERROR(INDEX(AllParts.xlsx!$B:$B,MATCH($A3,AllParts!$A:$A,0)),"")"
End Sub

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

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

thank you, I really appreciate your help

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.

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

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

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

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

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"]

A

B

C

1

Main

Main

Main

2

Part #

Model #

Description

3

101

4

102

5

103

6

104

7

105

8

106

9

107

[/TABLE]

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