If & Macro

rjacmuto32

Board Regular
Joined
Jul 14, 2004
Messages
98
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

rjacmuto32

Board Regular
Joined
Jul 14, 2004
Messages
98
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
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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
 

rjacmuto32

Board Regular
Joined
Jul 14, 2004
Messages
98

ADVERTISEMENT

VBA

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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")
 

rjacmuto32

Board Regular
Joined
Jul 14, 2004
Messages
98

ADVERTISEMENT

Thanks, But...

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

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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))
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,130
Messages
5,857,551
Members
431,885
Latest member
Rsdg

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?

Disable AdBlock

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
Top