Add cell if

[tuytuy]

Hi,
I have a sheet that looks like this:

GSM number

Extension

Group name

Subgroup name

Name GSM number

User ref1

User Ref2

Call date

Call time

Call duration

Destination

Zone/Country/Operator

Tariff

Type

Supplementary services

Indicative tariff

Usage amount

Currency

Country of destination

Account number

Discount Indicator

Jan-13

xxxxxxxxxxxx

Unfiled - Niet toegekend - Non attribué

28-Jan-13

16:58:53

00:02:05

478882664

On Site

Normal

Communications nationales

0

0

EUR

10736059

N

Jan-13

xxxxxxxxxxxx

Unfiled - Niet toegekend - Non attribué

29-Jan-13

16:47:21

00:11:37

493194255

Mobistar

Normal

Communications nationales

R

0

1.3803

EUR

10736059

Y

Jan-13

xxxxxxxxxxxx

Unfiled - Niet toegekend - Non attribué

02-Jan-13

21:31:21

00:01:00

31 111 483 392

From Site

Réduit

Communications internationales

R

0

0.06

EUR

10736059

Y

<tbody>
</tbody>

i'd like to add up, using a macro, the "Usage amount" if the type is "Communication nationals" only.

 

[VoG]

Try like this

=SUMIF(O:O,"Communications nationales",R:R)

 

[tuytuy]

i wanted to use a macro, vba code

 

[VoG]

THen something like this but it will be much less efficient

Sub test()
Dim LR As Long, i As Long
Dim Total As Double
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For i = 2 To LR
    If Range("O" & i).Value = "Communications nationales" Then Total = Total + Range("R" & i).Value
Next i
MsgBox Total
End Sub

 

[Rick Rothstein]

Give this a try...

Sub GetUsageAmounts()
  Dim LastRow As Long, Total As Double
  Const LookupType As String = "Communications nationales"
  LastRow = Cells(Rows.Count, "O").End(xlUp).Row
  Total = WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
  MsgBox Total
End Sub

 

[tuytuy]

It's part of a longer macro written to generate a report of a hug file every month.
Thx anyways it worked

 

[tuytuy]

i've gone a bit further, but i get an error with my code, for this one there are 4 conditions to the if function is the way i did ok ?

'Copy COST FOR NATIONAL CALLS
ActiveWorkbook.Sheets("Sheet6").Activate
Dim LastRow As Long, TotalN As Double
  Const LookupType As String = "Communications internationales" Or "Communications internationales" Or "Communications effectuées a l'étranger (ROAMING)" Or "Communications recues a l'étranger (ROAMING)"
  LastRow = Cells(Rows.Count, "O").End(xlUp).Row
  Total = WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
ActiveWorkbook.Sheets("Synthesis").Activate
Range("A4").Value = "Total cost of International Communications"
Range("B4").Value = TotalN

 

[Rick Rothstein]

i've gone a bit further, but i get an error with my code, for this one there are 4 conditions to the if function is the way i did ok ?

'Copy COST FOR NATIONAL CALLS
ActiveWorkbook.Sheets("Sheet6").Activate
Dim LastRow As Long, TotalN As Double
  Const LookupType As String = "Communications internationales" Or "Communications internationales" Or "Communications effectuées a l'étranger (ROAMING)" Or "Communications recues a l'étranger (ROAMING)"
  LastRow = Cells(Rows.Count, "O").End(xlUp).Row
  [B][COLOR=#a52a2a]Total [/COLOR][/B]= WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
ActiveWorkbook.Sheets("Synthesis").Activate
Range("A4").Value = "Total cost of International Communications"
Range("B4").Value = TotalN

You cannot do that with constants (the Const statement). What you will need to do is make LookupType a variable instead of a constant and then create a loop that iterates the four values while maintaining a running total. By the way, I see you changed my suggested variable name Total to TotalN, but you missed changing the one I highlighted in red above. See if this code works for you...

'Copy COST FOR NATIONAL CALLS
TotalN = 0
ActiveWorkbook.Sheets("Sheet6").Activate
Dim LastRow As Long, TotalN As Double, LookupType As Variant, vItem As Variant
LastRow = Cells(Rows.Count, "O").End(xlUp).Row
LookupType = Array("Communications internationales", _
                   "Communications internationales", _
                   "Communications effectuées a l'étranger (ROAMING)", _
                   "Communications recues a l'étranger (ROAMING)")
For Each vItem In LookupType
  TotalN = TotalN + WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
Next
ActiveWorkbook.Sheets("Synthesis").Activate
Range("A4").Value = "Total cost of International Communications"
Range("B4").Value = TotalN

NOTE: The first two items in the Array function being assigned to the LookupType variable look identical to me (see blue highlighted text above)... that is what you posted so I used it (especially since you said you had 4 items), but you will need to change one of them to whatever item type it should have been (otherwise "Communications internationales" will end up being double counted).

 

[tuytuy]

i get a duplicate deceleration error ?! but can see it anywhere

'Copy COST FOR NATIONAL CALLS


ActiveWorkbook.Sheets("Sheet6").Activate
Dim LastRow As Long, TotalN As Double
  Const LookupType As String = "Communications nationales"
  LastRow = Cells(Rows.Count, "O").End(xlUp).Row
  TotalN = WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
ActiveWorkbook.Sheets("Synthesis").Activate
Range("A3").Value = "Total cost of National Communications"
Range("B3").Value = TotalN


'Copy COST FOR INTERNATIONAL CALLS
TotalIN = 0
ActiveWorkbook.Sheets("Sheet6").Activate
Dim LastRow As Long, TotalIN As Double, LookupType As Variant, vItem As Variant
LastRow = Cells(Rows.Count, "O").End(xlUp).Row
LookupType = Array("Communications internationales", _
                   "Communications effectuées a l'étranger (ROAMING)", _
                   "Communications recues a l'étranger (ROAMING)")
For Each vItem In LookupType
  TotalIN = TotalIN + WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
Next
ActiveWorkbook.Sheets("Synthesis").Activate
Range("A4").Value = "Total cost of International Communications"
Range("B4").Value = TotalIN

 

[Rick Rothstein]

i get a duplicate deceleration error ?! but can see it anywhere

'Copy COST FOR NATIONAL CALLS


ActiveWorkbook.Sheets("Sheet6").Activate
[B][COLOR=#a52a2a]Dim LastRow As Long, TotalN As Double[/COLOR][/B]
  Const LookupType As String = "Communications nationales"
  LastRow = Cells(Rows.Count, "O").End(xlUp).Row
  TotalN = WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
ActiveWorkbook.Sheets("Synthesis").Activate
Range("A3").Value = "Total cost of National Communications"
Range("B3").Value = TotalN


'Copy COST FOR INTERNATIONAL CALLS
TotalIN = 0
ActiveWorkbook.Sheets("Sheet6").Activate
[B][COLOR=#a52a2a]Dim LastRow As Long, TotalIN As Double[/COLOR][/B], LookupType As Variant, vItem As Variant
LastRow = Cells(Rows.Count, "O").End(xlUp).Row
LookupType = Array("Communications internationales", _
                   "Communications internationales", _
                   "Communications effectuées a l'étranger (ROAMING)", _
                   "Communications recues a l'étranger (ROAMING)")
For Each vItem In LookupType
  TotalIN = TotalIN + WorksheetFunction.SumIf(Range("O2:O" & LastRow), LookupType, Range("R2:R" & LastRow))
Next
ActiveWorkbook.Sheets("Synthesis").Activate
Range("A4").Value = "Total cost of International Communications"
Range("B4").Value = TotalIN

If the above code is all in the same procedure, then the problem is the lines of code I highlighted in red... you can only declare (Dim) a variable one time with a procedure otherwise you get a "Duplicate declaration in current scope" error. Most people declare their variables at the beginning of the procedure where it is easier to spot duplicate declarations rather than posting them inline like your code above does.