If & Macro

[rjacmuto32]

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
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6
7	1	Actuals	AltBudget	AltBudget
8		Year2005	Year2005	Year2005
9		Jan	Feb	March
10
11
12	2	Actuals	Actuals	AltBudget
13		Year2005	Year2005	Year2005
14		Jan	Feb	March				1
15
16
17	3	Actuals	Actuals	Actuals
Sheet1

 

[al_b_cnu]

Hi,

how about something like this:

Book1
	B	C	D	E	F	G	H	I	J	K
7	$B$7	$C$7	$D$7
8	$B$8	$C$8	$D$8
9	$B$9	$C$9	$D$9					$B$7	$C$7	$D$7
10								$B$8	$C$8	$D$8
11								$B$9	$C$9	$D$9
12	$B$12	$C$12	$D$12
13	$B$13	$C$13	$D$13
14	$B$14	$C$14	$D$14				1
15
16
17	$B$17	$C$17	$D$17
18	$B$18	$C$18	$D$18
19	$B$19	$C$19	$D$19
Sheet1

Formula in I9 & copied across & down is =IF($H$14=1,B7,IF($H$14=2,B12,B17))

 

[rjacmuto32]

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]

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]

VBA

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

 

[Yogi Anand]

Edit: deleted the formula based approach and provided VBA code

Hi rjacmut032:

Here is the VBA 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]

Thanks, But...

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

 

[al_b_cnu]

Hi,

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))

 

[rjacmuto32]

Thanks

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

 

[Yogi Anand]

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?