0

I would like to use a named ranged using the countifs function, and not using a specific range using the name of column and the number of rows (because on my different sheets, the columns and rows are never the same).

Here is my code:

Sub ClearFULLandPART()
'
' test Macro
'

'
Dim rgn As Range
Dim x As Long, y As Long
    Cells.Find(What:="TOTAL AIRLANES FULL SERVICE", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection _
        :=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireRow.Select
    Selection.ClearContents
    ActiveSheet.Cells(ActiveCell.Row, 1).Select
    ActiveCell.Value = "TOTAL AIRLANES FULL SERVICE"
    
    Range("JB3").Select
    
    Cells.Find(What:="TOTAL AIRLANES PART SERVICE", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection _
        :=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireRow.Select
    Selection.ClearContents
    ActiveSheet.Cells(ActiveCell.Row, 1).Select
    ActiveCell.Value = "TOTAL AIRLANES PART SERVICE"
    
    'Selectionne la cellule Cargoflight, Total Airlanes Part Service
ActiveCell.Select
ActiveCell.Offset(0, 7).Select
 Do Until ActiveCell.EntireColumn.Hidden = False
 ActiveCell.Offset(0, 7).Select
 Loop
 
 'définition des ranges avec maintained et information
 
 
 'Set rgn = ActiveCell.EntireColumn.Resize(Rows.Count - 2).Offset(2)
'rgn.Select



 Cells.Find(What:="TOTAL AIRLANES PART SERVICE", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection _
        :=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    
    ActiveCell.Select
ActiveCell.Offset(0, 7).Select
 Do Until ActiveCell.EntireColumn.Hidden = False
 ActiveCell.Offset(0, 7).Select
 Loop
 
 Dim rng1 As Range




Set rgn1 = Cells(2, ActiveCell.Column)

Dim rgn2 As Range
' -1 to select the cell of how many row above current cell
Set rgn2 = Cells(ActiveCell.Row - 1, ActiveCell.Column)

Dim rgnfrom1to2 As Range
Set rgnfrom1to2 = Range(rgn1, rgn2)

'end of selection of the column to current cell row-1
Dim rangeEG2EG9 As Range
Set rangeEG2EG9 = Range("EG2:EG9")


'HERE IS THE PROBLEM
**ActiveCell.Value = WorksheetFunction.CountIfs(rgnfrom1to2, "maintained", Range("EG2:EG9"), "")**


 'I would like to do the above function to the next 3 countsif using the rgnfrom1to2
ActiveCell.Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = WorksheetFunction.CountIfs(Range("EE2:EE9"), "maintained", Range("EG2:EG9"), "")

ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
ActiveCell.Value = WorksheetFunction.CountIfs(Range("EE2:EE9"), "maintained", Range("EG2:EG9"), "*")

ActiveCell.Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = WorksheetFunction.CountIfs(Range("EE2:EE9"), "maintained", Range("EG2:EG9"), "*")

End Sub

I get an error each time I try this function:

ActiveCell.Value = WorksheetFunction.CountIfs(rgnfrom1to2, "maintained", Range("EG2:EG9"), "")
ZygD
  • 22,092
  • 39
  • 79
  • 102
Dehoucks
  • 11
  • 1
  • What's the result of `Debug.Print rgnfrom1to2.Address`? – BigBen Mar 09 '21 at 13:53
  • What error message? Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Mar 09 '21 at 13:59
  • Thanks for the answer BigBen. Well I'm quite green on VBA, but when I try to put your code right before the first countsif code line, it doesn't change anything (error 1004) . And when I try to put it right before the first countsif code line, and then delete everything after, the code run but doesn't "print ?" anything special. – Dehoucks Mar 09 '21 at 14:01
  • You need to check the Immediate Window: Ctrl + G to bring it up. – BigBen Mar 09 '21 at 14:01
  • Thank you SJR. The error message is :Error 1004 Not possible to read Countifs of the class WorksheetFunction (Ps: I translated by myself from french sorry if it's innacurate) – Dehoucks Mar 09 '21 at 14:09
  • Thanks BigBen, I did CTRL + G, then paste 'Debug.Print rgnfrom1to2.Address', it told me error 424, Object required. – Dehoucks Mar 09 '21 at 14:10
  • You need to put `Debug.Print rgnfrom1to2.Address` in the subroutine (before the first countifs line), *then* check the output in the Immediate Window. – BigBen Mar 09 '21 at 14:11
  • Thanks for the details provided. I paste the code you sent me in the subroutine, then runned the code, and here is what it tell me in the Immediate Window: $ED$2:$ED$10 – Dehoucks Mar 09 '21 at 14:15
  • 1
    That's your problem... `rgnfrom1to2` has one more row than `Range("EG2:EG9")`. They need to have the same number of rows. – BigBen Mar 09 '21 at 14:19
  • Note too that your code will error if any of your search terms are not found. You should always check first before activating or anything else. – SJR Mar 09 '21 at 14:31
  • Thank you BigBen, thanks to you I solved the problem ! Thanks as well for the tip SJR. – Dehoucks Mar 09 '21 at 14:41
  • I don't know if you could help me but I'm having another problem. It seem that : 'ActiveCell.Select ActiveCell.Offset(0, 7).Select Do Until ActiveCell.EntireColumn.Hidden = False ActiveCell.Offset(0, 7).Select Loop' isn't working properly. Indeed, I need to select the 5th visible column of the row active on each of my sheet. But when I run it on different sheet, sometime it select the 2nd visible column, sometime the 10th, sometime the 1st. Could you please help me with it good sirs ? Many thanks – Dehoucks Mar 09 '21 at 15:05

0 Answers0