dynamic allocation of range in macro

Gayathri Venkatesh

New Member
Joined
Jan 18, 2006
Messages
25
Hi,

I am using follwing formula in macro

ActiveCell.FormulaR1C1 = "=SUMPRODUCT((INSTALLrange>0)*((BILLONLYrange)>0)*((AUrange)>0))"

Installrange,billonlyrange & aurange are defined names used for storing range

Value of Aurange changes depending on country.
i would like to use looping for change in aurange value as the formula is used more than once with variation for a given country. i cannot assign value to aurange dynamically as values for all countries will change with the change in value of aurange. i also tried using a variable say varrange and assign defined names (say aurange, nzrange) i.e. value within value for e.g

ActiveCell.FormulaR1C1 = "=SUMPRODUCT((INSTALLrange>0)*((BILLONLYrange)>0)*((varrange)>0))"


and assign

varrange = aurange for AU
varrange = nzrange for NZ

but it does not work.

Any easier way of dealing with it.

Thanks
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim aantalrijen As Integer
    Dim adres As String
    Dim rangenaam As String
    rangenaam = Cells(2, Target.Column).Value
    
    On Error GoTo end_rngch
    
    With ThisWorkbook.Sheets("Aanvullende gegevens").Range(rangenaam)
        aantalrijen = WorksheetFunction.CountA _
            (.EntireColumn) - _
            WorksheetFunction.CountA(Range( _
            Cells(1, .Column), _
            Cells(5, .Column)))
        adres = "=" & _
            Range _
            (Cells(6, .Column), _
            Cells(5 + aantalrijen, .Column)).Address
    End With
    
    Names.Add Name:=rangenaam, RefersTo:=adres
    
end_rngch:
    

End Sub

this code placed in the worksheet_change function expands a named range when data is entered. I thought it might be useful. Note that the data in my sheet starts at row 6.
 

Gayathri Venkatesh

New Member
Joined
Jan 18, 2006
Messages
25
Hi harvey,

Thanks for your suggestion. i am not sure whether it will accomplice my need. below is sample of what i am trying to aceive.

Region job1 job1 job1 job2 job2 job2 job3 job3 job3 job4 job4 job4
No O/S SLA % O/S SLA No O/S SLA % O/S SLA No. O/S SLA % O/S SLA No. O/S SLA % O/S SLA
AU 50 33 66 164 40 24 115 21 18 12 0 0
NZ 9 5 56 20 7 35 18 6 33 2 0 0
ID 37 26 70 13 1 8 26 3 12 23 16 70
PH 17 5 29 3 1 33 7 1 14 8 3 38
SG 168 62 37 64 8 13 88 9 10 56 16 29

first 2 columns under each job uses the formula i have mentioned in my original post with slight variation. all defined names are ranges and they are predetermined and each country has one range of its own. all countries share the other ranges defined in the formula. i am trying to create the summary using loop for the countries.as there are a lot more countries than what i have mentioned.

cheers
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
hi Gayathri,

I am not sure yet of what you wish to accomplish, but I think that my code doesn't cover it.

Am I right if I say you want the following:

Select a country from the list.
Get the row from that country.
Use the values in the row as a range in your formula.

If this is true, I think a simple VLOOKUP can help you out, but I wait for your response before I start working it out since I'm not completely sure if this is what you mean.
 

Gayathri Venkatesh

New Member
Joined
Jan 18, 2006
Messages
25
Hi Harvey,

in Formula

SUMPRODUCT((INSTALLrange>0)*((BILLONLYrange)>0)*((AUrange)>0))"
installrange,billonlyrange and aurange are all column range. i have data on a separate worksheet with column as

install, billonly, serviceonly, au,nz, my,sg.....

for each row
install,billonly,service only can have values ranging from 0 to any number but the countries columns can have only 0 or 1 and for each row only 1 country can have a value of 1 all other countries will be zero.

above mentioned formula will do summation as well as the product and give me one value. therfore i cannot use vlookup. i am trying to create a summary out of these data with column heading as

country, billonly ,os/sls bill only , service only,o/s sla servie only .....
i hope i have made myself clear of what i am trying to acheive

cheers
 

Forum statistics

Threads
1,136,877
Messages
5,678,290
Members
419,753
Latest member
Sallylwy

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