Solved: Count empty and non empty in Col B

[Flynazn]

Hi all,

I was hoping to find a vba code for counting the empty and non empty cell in column B and also highlight the empty cell, there are three different sheets. I would like the results to be placed onto sheet 1. Please advise.

[Bob Phillips]

Put the values Seet1, Sheet2, Sheet3 in I2, I3 and I4.

In J2, enter =COUNTA(INDIRECT(I2&"!B:B"))

In K2, enter =COUNTA(INDIRECT(I2&"!A:A"))-J2

Copy J2:K2 down to row 4.

[Flynazn]

Thanks xld...but I got 1 and 0 from the above...what am I doing wrong?

[Bob Phillips]

Post your workbook with the formulae added.

[Flynazn]

here's my form

[Bob Phillips]

You have to put the values in I2:I4 that match the sheet names. If you spell them wrongly, it doesn't work.

[Flynazn]

Thanks XLD...I am a total beginner...is there any suggestion as to what I can do to get better at VBA and excel in general...

thanks again!!!

[Flynazn]

Hi again,
Now that I have the formulas, is it possible to have a VBA script so that I can reuse it on different sheets. The three sheets will be updated daily and I want to be able to create a button so that I can just use for this purpose. Thanks.

[mdmackillop]

[VBA]Sub AddSummary()
Range("I2").Formula = "Sheet 1"
Range("J2").FormulaR1C1 = "=COUNTA(INDIRECT(RC[-1]&""!B:B""))"
Range("K2").FormulaR1C1 = "=COUNTA(INDIRECT(RC[-2]&""!A:A""))-RC[-1]"
Range("I2:K2").AutoFill Destination:=Range("I2:K4"), Type:=xlFillDefault
End Sub
[/VBA]

[Flynazn]

Thanks again...that made my daily review much easier. One more function I left out. I wanted to add to the VBA script a function that will look at column B, and if a date exist then put a formula into Column C "=B2+445".

Thanks again everyone...u don't know how much time this is saving me.

[Bob Phillips]

[vba]

With Activesheet

LastRow = .Cells(.Rows.Count, "B").End (xlUp).Row
For i = 1 To LastRow
If IsDate(.Cells(i, "B").Value) Then
.Cells(i, "C").Value = .cells(i, "B").Value +445
End If
Next i
End With
[/vba]


BTW, why 445

[Flynazn]

OH...the 445 is just a date from the previous date that I need to review the data. Also, this works for sheet1, I need it to go through sheet 2 and sheet3 aswell. I tried placing it between other codes that you helped me with but only sheet one works. Thanks for all your help again.

By the way, no one really answer my question as how can I start learning VBA more and faster. It's a very usefull language, but it fustrating that I simply don't get it. Please advise.

[RonMcK3]

By the way, no one really answer my question as how can I start learning VBA more and faster. It's a very usefull language, but it fustrating that I simply don't get it. Please advise.

First, use the Help button as your first stop when researching a question.

Second, search your local library catalog and/or your local bookseller or your favorite online bookseller, using key word 'VBA'. You'll probably find titles by John Walkenbach and many others. Read the books, work thru the examples they use, try using what you learn on your own projects.

Third, keep working on new projects that build on what you have learned and push you into new areas. Post queries here to help learn new topics.

Fourth, read the questions that other users are posting, here, study the code they post as well as the code in replies they receive.

[RonMcK3]

Also, this works for sheet1, I need it to go through sheet 2 and sheet3 aswell. I tried placing it between other codes that you helped me with but only sheet one works. Thanks for all your help again.

Try this slight change to Xld's code:

[vba]Dim Loop1 As Long

For Loop1 = 1 To 3
Worksheets("Sheet" & Loop1).Activate
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To LastRow
If IsDate(.Cells(i, "B").Value) Then
.Cells(i, "C").Value = .Cells(i, "B").Value + 445
End If
Next i
Next Loop1
[/vba]

[Flynazn]

Thanks Ron,
I will do so and I'll post up my progress. I want to get to the level where I can help others on this site by replying to their post for help. It's a great forum for getting help and learning new things as well. Thanks again...

[Flynazn]

Everythings a go...thanks all...