PDA

View Full Version : Solved: Why is this not working?



silentsound
11-15-2009, 11:05 AM
I am new to vba and creating a macro to determine whether there are no, 1 or 2 or more used cells in column A. I have done the following but can't understand why it is working if there is 1 used cell. If there is an easier way that would be good, but I would also like to know why this doesn't work

If IsEmpty(Cells(1, 1)) Then
MsgBox "No cells"
End If
If (IsEmpty(Cells(2, 1)) And IsEmpty(Cells(1, 1) = False)) Then
MsgBox "1 cell"
End If
If (IsEmpty(Cells(1, 1)) = False And IsEmpty(Cells(2, 1)) = False) Then
MsgBox "2 or more cells"
End If
Muchas gracias everyone !!

lucas
11-15-2009, 11:27 AM
Well, your structure isn't exactly correct for one and if statements get executed and exit the sub sometimes if not stuctured correctly.

This shows how to check for false in your if statement:
If IsEmpty(Cells(1, 1)) Then
MsgBox "No cells"
End If
If IsEmpty(Cells(2, 1)) And Not IsEmpty(Cells(1, 1)) Then
MsgBox "1 cell"

To check a column though, I would use a worksheet function:
Sub CountNonBlankCells()
Dim CellCount As Long
CellCount = Application.Evaluate("=SUMPRODUCT(--(LEN(A1:A100)>0))")
MsgBox CellCount & " non blank cells", vbOKOnly
End Sub

mdmackillop
11-15-2009, 11:47 AM
You could also use Application.CountA

silentsound
11-15-2009, 11:51 AM
Cheers lucas good work!

lucas
11-15-2009, 12:30 PM
As Malcolm points out, this doesn't have to be done with code. You can easily use a formula on the sheet.

If you're happy, mark your thread solved using the thread tools at the top of the page please.

silentsound
11-15-2009, 01:47 PM
As Malcolm points out, this doesn't have to be done with code. You can easily use a formula on the sheet.

If you're happy, mark your thread solved using the thread tools at the top of the page please.

Sure. Just to let you know the thread tools don't work in Google chrome, because the drop down arrow doesn't appear. I used IE to mark this solved. Although I suppose that's not an issue for 99.9% of forum viewers!

Aussiebear
11-15-2009, 03:57 PM
Not even if you click on "Thread Tools"?

silentsound
11-21-2009, 02:27 PM
Not even if you click on "Thread Tools"?
No Google chrome doesn't seem to recognise the thread tools text as a drop down menu. The triangular button doesn't appear in google chrome. If you click on thread tools it directs you to the thread tools at the bottom of the page