redapplesonly
New Member
- Joined
- Jun 28, 2022
- Messages
- 1
- Office Version
- 2019
- Platform
- Windows
Hi everyone, I need a Macro that populates a range of cells with a formula. At first, I thought this would be an easy problem... but I'm starting to think that it is a more advanced problem than I originally guessed.
To explain: In my spreadsheet, I have two sheets: PERM_DATA and PLAY_WITH_DATA. PERM_DATA is populated with product data, and is never meant to be altered by the user. Let's say it looks like this:
The data on this sheet is static; the user is never supposed to modify it. But let's say on the other sheet, PLAY_WITH_DATA, I want to give the user an identical set of data with which to play around. On the first visit, PLAY_WITH_DATA would look like this:
Where the drop-down menus in Column A list all the PRODUCTS from PERM DATA. The idea is, if the user wants to play with the numbers associated with TeeShirt_A, they can select "TeeShirt_A" from any drop-down menu, and then the rest of the TeeShirt_A numbers will populate into the rest of the row. This formula should do that:
The user can then "play with" (i.e. change) the data on the PLAY_WITH_DATA tab. The original data is preserved on PERM_DATA, and can be re-imported into PLAY_WITH_DATA, should we run the Macro again.
So I'm wracking my brain on how to do this. I guess, first I need a Macro that populates the above formula into every cell in the target row, maybe something like this:
The above kicks up a syntax error within the formula, however; Excel doesn't like the reference to "$B2" I don't know why.
I would still need to figure out how to tie the above in with a PLAY_WITH_DATA row's drop-down menu.
So I realize that I'm prob attempting something harder than I can handle, but I thought I'd ask: Is my approach way off? Does anyone see my error? Or is there any easier approach? Any advice is welcomed. Thanks
To explain: In my spreadsheet, I have two sheets: PERM_DATA and PLAY_WITH_DATA. PERM_DATA is populated with product data, and is never meant to be altered by the user. Let's say it looks like this:
| PRODUCT | Cost_to_Produce | Wholesale_Price | Retail_Price |
| TeeShirt_A | $5 | $8 | $10 |
| TeeShirt_B | $6 | $8 | $14 |
| TeeShirt_C | $3 | $6 | $9 |
The data on this sheet is static; the user is never supposed to modify it. But let's say on the other sheet, PLAY_WITH_DATA, I want to give the user an identical set of data with which to play around. On the first visit, PLAY_WITH_DATA would look like this:
| PRODUCT | Cost_to_Produce | Wholesale_Price | Retail_Price | |||
| <<Drop-down Menu>> | ||||||
| ||||||
| <<Drop-down Menu>> |
Where the drop-down menus in Column A list all the PRODUCTS from PERM DATA. The idea is, if the user wants to play with the numbers associated with TeeShirt_A, they can select "TeeShirt_A" from any drop-down menu, and then the rest of the TeeShirt_A numbers will populate into the rest of the row. This formula should do that:
VBA Code:
=IFERROR(VLOOKUP($B1,'PERM_DATA'!$A$2:$I$11,2,0),"")The user can then "play with" (i.e. change) the data on the PLAY_WITH_DATA tab. The original data is preserved on PERM_DATA, and can be re-imported into PLAY_WITH_DATA, should we run the Macro again.
So I'm wracking my brain on how to do this. I guess, first I need a Macro that populates the above formula into every cell in the target row, maybe something like this:
Code:
Sub ImportRowFromPermData()
Dim i As Integer
For i = 2 To 4
Cells(2, i).Formula = IFERROR(VLOOKUP($B2,'PERM_DATA'!$A$2:$I$11,i,0),"")
Next i
End SubThe above kicks up a syntax error within the formula, however; Excel doesn't like the reference to "$B2" I don't know why.
I would still need to figure out how to tie the above in with a PLAY_WITH_DATA row's drop-down menu.
So I realize that I'm prob attempting something harder than I can handle, but I thought I'd ask: Is my approach way off? Does anyone see my error? Or is there any easier approach? Any advice is welcomed. Thanks
