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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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,494
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,494
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,020
Messages
5,569,678
Members
412,287
Latest member
Ardamit
Top