dynamic allocation of range in macro

[Gayathri Venkatesh]

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

 

[Harvey]

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]

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]

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]

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