VBA code to count the no of cells from one sheet to another with its background color.

Hi,
I am new to VBA, please help me in this!
 How to write VBA code for  counting cells from one sheet to  copy the count value to  another sheet   with its background color.
this is code i tried,


Sub Workbook_Open()
Dim str As String
str = range("d4").Value
With Sheets("newCorp")
With .range("d6")
'.Formula = "=COUNTIFS(" & str & "!F:O," & str & ")"
End With
End With
range("d6").Select
range("F10:010").Copy newCorp.range("d6" & Rows.Count).End(xlUp)(2)
'Selection.AutoFill Destination:=Range("d6")
'Range("d6").Select
End Sub
Saha cAsked:
Who is Participating?
 
ShumsConnect With a Mentor Distinguished Expert - 2017Commented:
Ok.

Check in attached..
Countifnew_2.xlsm
0
 
ShumsDistinguished Expert - 2017Commented:
Hi Saha,

Welcome to Expert Exchange!

Please post your sample/dummy workbook.
0
 
Saha cAuthor Commented:
Hi,
Please find the attached sheet.
here it has to count no of passed cells from sheet2 and paste the count in newcorp with its background color.

Please help!
Thanks in advance.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
ShumsDistinguished Expert - 2017Commented:
No attachment :(
0
 
Saha cAuthor Commented:
0
 
ShumsDistinguished Expert - 2017Commented:
Do you mean in attached...

Click Get Count and check the result.
Saha_C_Countif.xlsm
0
 
Saha cAuthor Commented:
Thank you!!!  Its fine.
with that we can copy the detail of cells?
I mean why it failed like that.
0
 
ShumsDistinguished Expert - 2017Commented:
What do you mean by details? You wanted just count.
0
 
Saha cAuthor Commented:
Ya that's correct sir.
some failed cases are also there know how it will define ?
how many failed and details for that . Is it possible to do?
0
 
ShumsDistinguished Expert - 2017Commented:
Where do you want the count of Fail? In attached I got it in Col E.
Saha_C_Countif_v3.xlsm
0
 
Saha cAuthor Commented:
In WW31 column i.e.,  col D  only i need that details. In fail some information also written write we have to copy that also.
If pass no change but if it fails we need details for failure .
0
 
ShumsDistinguished Expert - 2017Commented:
Check in last version, I have list in Col E from E12
0
 
Saha cAuthor Commented:
ok I will check...
0
 
Saha cAuthor Commented:
Once again thank you.....
0
 
ShumsDistinguished Expert - 2017Commented:
Does it means, provided solution serves your purpose?
0
 
Saha cAuthor Commented:
yes thank you sir.
0
 
ShumsDistinguished Expert - 2017Commented:
Then I would request you to please close this question by following below link, if you are not aware:
How do I close my question?
0
 
Saha cAuthor Commented:
Before that i want to ask you one question  that,  can we insert one more row in between to copy the fail detail ? Is it possibe?
Sorry for disturbing again and again just i want to ask this.
0
 
Saha cAuthor Commented:
previous one is also good but i think this is more effective.
0
 
ShumsDistinguished Expert - 2017Commented:
There is already 1 row gab between Pass Count & Fail Details (which is row 11), do you want one more row in between?
0
 
Saha cAuthor Commented:
no no not like that , i'm telling where we mention the pass count know below that only it is possible?
0
 
ShumsDistinguished Expert - 2017Commented:
It is not possible in your workbook as it has structured.

Increase the row height of 11th row and display as Details of Fail, check in attached...
Saha_C_Countif_v3.xlsm
0
 
Saha cAuthor Commented:
in same cell we can't mention both fail and pass?
0
 
Saha cAuthor Commented:
Hi,
i can do like this ,Please check attachment (corp sheet).
Saha_C_Countif_v3.xlsm
0
 
ShumsDistinguished Expert - 2017Commented:
No. Its not possible
0
 
Saha cAuthor Commented:
OK thank you.
0
 
Saha cAuthor Commented:
.FormulaR1C1 = "=IF(COUNTIF(Sheet2!R[5]C[2]:R[5]C[11],""PASS"")=0,"""",COUNTIF(Sheet2!R[5]C[2]:R[5]C[11],""PASS""))"

Sir,  Please can u explain how you selected this formula?
0
 
Saha cAuthor Commented:
If I add one more col in between ,  red color for failure is coming..
Please check in attached sheet...
Countifnew.xlsm
0
 
ShumsDistinguished Expert - 2017Commented:
Formula I used is simple CountIf, If in Sheet 2 range F10:Q10 contains "PASS", then count "PASS", it goes down as the number of rows.
=IF(COUNTIF(Sheet2!F10:O10,"PASS")=0,"",COUNTIF(Sheet2!F10:O10,"PASS"))
same with "<>PASS" not equal to Pass.
Kindly note, when you define variables in VBA, you cannot change/insert/delete cells/rows/columns. If you do so, then you need to change in VBA as well, inserting one more column in newCorp sheet between D & E, you need to change this range as well .Range("E5:E" & LRow)

Hope this helps.
0
 
Saha cAuthor Commented:
Sorry for disturbing again and again, but we want data in E col only know then which col we have to mention there.
i am changing the col but its not applying..
0
 
Saha cAuthor Commented:
OK Thank you.
This is for one row if we added one more sheet then we have to do same for that?
0
 
ShumsDistinguished Expert - 2017Commented:
Of Course. For every sheet and every workbook, You need to change variables.
0
 
Saha cAuthor Commented:
Only Variables we need to change right?  If we copy paste the same code means it will work right?
0
 
Saha cAuthor Commented:
Because like Sheet2 so many sheets are there , that's why i am asking
0
 
ShumsDistinguished Expert - 2017Commented:
Sorry that was not initial requirement. Code provided for one sheet.
0
 
Saha cAuthor Commented:
Can we paste that failure details in that same cell?
0
 
ShumsDistinguished Expert - 2017Commented:
You already asked this earlier and I replied not possible.
0
 
Saha cAuthor Commented:
Yes, that was task given by manager for one sheet but now he has given some more sheets to add .
0
 
Saha cAuthor Commented:
no i am asking where we paste the failure count know with that only we can't do?
0
 
Saha cAuthor Commented:
instead of pasting count of failure , can we store details there?
i mean in leaving that count can we store directly details there?
0
 
Saha cAuthor Commented:
Thank you sir.. Now i will close the question.
0
 
Saha cAuthor Commented:
Sir help me please...
If i have added new sheet it is not working for that.
0
 
Saha cAuthor Commented:
Still my  question is not completed....
0
 
Saha cAuthor Commented:
Hi Shums,
 Can you help me in this please....
0
 
Saha cAuthor Commented:
I want like this ... it has to copy the count and details of failure like in sheet "corp"
It has to take count the pass and fail from different sheets like ww31 , ww33 and more  and also copy the failure details in "newcorp"
new2.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.