Select Range VBA With CountA

[excelbytes]

I have the following code:

Private Sub CheckBox2_Click()
    
   
On Error Resume Next
On Error GoTo err_handler
  
If CheckBox2.Value = True Then Sheets("IC304").Range("B2:B50").Value = "MP0"
If CheckBox2.Value = False Then Sheets("IC304").Range("B2:B50").Value = ""
If CheckBox2.Value = True Then Sheets("TH102").Range("B2:B50").Value = "MP0"
If CheckBox2.Value = False Then Sheets("TH102").Range("B2:B50").Value = ""
  
Exit Sub

err_handler:
MsgBox "Error number: " & Err.Description, vbExclamation, Err.Number
Exit Sub
End Sub

However, I don't want to insert MP0 in the range B2:B50, rather start at B2 and only enter it based on the count from another worksheet:

Sub CountCells()

Dim ws As Worksheet
Set ws = Worksheets("OFFICIAL DRAFT")
Application.WorksheetFunction.CountA(ws.Range("D15:D100"))

End Sub

How can I combine these so that it will only enter MP0 starting in cell B2 and going down as far as the count in the OFFICIAL DRAFT worksheet D15:D100?

Thanks!

 

[dmt32]

Hi
untested but see if update to your code will do what you want

Private Sub CheckBox2_Click()
    Dim ws(1 To 3)  As Worksheet
    Dim CountCells  As Long, i  As Long
    Dim Checked     As Boolean
    Dim rng         As Range
    
    With ThisWorkbook
        For i = 1 To 3
           Set ws(i) = .Worksheets(Choose(i, "IC304", "TH102", "OFFICIAL DRAFT"))
        Next i
    End With
    
    Set rng = ws(3).Range("D15:D100")
    
    CountCells = Application.CountA(rng)
    Checked = CheckBox2.Value And CountCells > 0
    If Not Checked Then CountCells = rng.Cells.Count
    
    For i = 1 To 2
        ws(i).Cells(2, 2).Resize(CountCells).Value = IIf(Checked, "MP0", "")
    Next i
    
End Sub

Dave

 

[excelbytes]

Hi
untested but see if update to your code will do what you want

Private Sub CheckBox2_Click()
    Dim ws(1 To 3)  As Worksheet
    Dim CountCells  As Long, i  As Long
    Dim Checked     As Boolean
    Dim rng         As Range
   
    With ThisWorkbook
        For i = 1 To 3
           Set ws(i) = .Worksheets(Choose(i, "IC304", "TH102", "OFFICIAL DRAFT"))
        Next i
    End With
   
    Set rng = ws(3).Range("D15:D100")
   
    CountCells = Application.CountA(rng)
    Checked = CheckBox2.Value And CountCells > 0
    If Not Checked Then CountCells = rng.Cells.Count
   
    For i = 1 To 2
        ws(i).Cells(2, 2).Resize(CountCells).Value = IIf(Checked, "MP0", "")
    Next i
   
End Sub

Dave

Dave,

It works great! I had to make a slight modification because of a range size I changed, but it's fine.

I have one other similar process on the same workbook and tried to use this as a template to take care of that one but it didn't work. Here is what I had:

Private Sub CheckBox3_Click()
    Dim ws(1 To 2)  As Worksheet
    Dim CountCells  As Long, i  As Long
    Dim Checked     As Boolean
    Dim rng         As Range
    
    With ThisWorkbook
        For i = 1 To 2
           Set ws(i) = .Worksheets(Choose(i, "IC304", "OFFICIAL DRAFT"))
        Next i
    End With
    
    Set rng = ws(2).Range("D15:D100")
    
    CountCells = Application.CountA(rng)
    Checked = CheckBox2.Value And CountCells > 0
    If Not Checked Then CountCells = rng.Cells.Count
    
    For i = 1 To 2
        ws(i).Cells(2, 5).Resize(CountCells).Value = IIf(Checked, "100", "")
    Next i
    

    
End Sub

The value 100 needs to go into column E starting at cell E2. What am I doing wrong?

 

[dmt32]

The value 100 needs to go into column E starting at cell E2. What am I doing wrong?

Looks like you have not updated the CheckBox code needs to refer to which may be your issue?

Checked = CheckBox3.Value And CountCells > 0

Dave

 

[excelbytes]

Looks like you have not updated the CheckBox code needs to refer to which may be your issue?

Checked = CheckBox3.Value And CountCells > 0

Dave

That did it! Thanks!