# If & Macro

#### rjacmuto32

##### Board Regular
I have a file with several tabs that I have macros to change data and format data and I trying to add one more thing but don't know how or where to do it. Below is my excel workbook code that I used the free program to convert your sheet to put into this message. I'm not looking for anything fancy, just plain and simple. Being new to these types of statements I've tried some websites to learn different things but I'm stuck at this:

I have 3 ranges with data B7:D9, B12:D14, B17:D19 while column A has the number 1,2,3 on row 7,12,17. I want to do something where whatever number I put in say cell H14 it will copy the corresponding range and paste it into I9:K11

Ex. Cell H14 has the number 2, so I would want to copy the range B12:D14 and paste that into cells I9:K11
Book1
ABCDEFGHIJK
1
2
3
4
5
6
71ActualsAltBudgetAltBudget
8Year2005Year2005Year2005
9JanFebMarch
10
11
122ActualsActualsAltBudget
13Year2005Year2005Year2005
14JanFebMarch1
15
16
173ActualsActualsActuals
Sheet1

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Doesn't Work

The problem with that formula is excel IF statements are only good for up to 7 things. I would put in about 30 of them which is why I wanted to put it into VBA or a macro

Actually a better formula in I9 & copied across & down might be:
=OFFSET(\$A\$1,\$H\$14*5+ROW()-8,COLUMN()-8)

This assumes that H14 starts at 1

VBA

How do you do this in VBA, not an excel formula?

Edit: deleted the formula based approach and provided VBA code

Hi rjacmut032:

Here is the VBA code ...
Code:
``````Cells(9, "I") = "=OFFSET(\$A\$6,INDEX(\$A:\$A,MATCH(\$H\$14,\$A:\$A))+(\$H\$14-1)*4+ROWS(\$1:1)-1,COLUMNS(\$A:A))"
Cells(9, "I").Copy Range("I9", "K11")``````

Thanks, But...

How do you do something like that in VBA, instead of formula's?

Hi,

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "\$H\$14" Then Exit Sub
If Target.Cells.Count <> 1 Then Exit Sub
Dim lRow As Long

lRow = Target.Value * 5 + 2

Range("I9:K11").Value = Range("B" & lRow & ":D" & lRow + 2).Value
End Sub``````

To install, right-click sheet tab, select 'View code' & paste above into code window.

... but I still think this formula is better: =OFFSET(\$A\$6,\$H\$14+4+ROWS(\$1:1),COLUMNS(\$A:A))

Thanks

The formula is good and I'll going to try the code stuff now.

al_b_cnu said:
Hi,

....
... but I still think this formula is better: =OFFSET(\$A\$6,\$H\$14+4+ROWS(\$1:1),COLUMNS(\$A:A))
Hi Alan:

Your formula dis not work for me. However, I think the following works.
=OFFSET(\$A\$1,\$H\$14*5+ROWS(\$1:1),COLUMNS(\$A:A))

What do you think?

Replies
1
Views
141
Replies
4
Views
971
Replies
0
Views
212
Replies
6
Views
766
Replies
5
Views
365

1,219,576
Messages
6,149,089
Members
450,858
Latest member
Vvy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back