Count if function on Excel spread sheet.

[mohan08]

I have 2 different spread sheet(Chandoo_Macro and Chandoo) below are the activity which needs to be done.

Should have the macro on chandoo_macro and open 2nd Chandoo and do the following functions.

1. Unmerge rows 2 and 3
   
   
2. To have new inserted after line 1 and apply count if function on row 2 until values available on row 3.
   
   
3. Excel formula =countif(list,A3). I have the range named on Chandoo_macro (Sheet 2). Can we have yes or No instead of 0 and 1 after applying functions
   

Rename sheet one with name list1 available on D3(Chandoo_macro) and Copy worksheet from another workbook(Chandoo2) and use the named range list2(Available on Chandoo_macro) and repeat the above 3 function and rename sheet as list2 available on E3 (Chandoo_macro) and save the workbook with file name available on F4.


Regards,
Mohan

 

[SirJB7]

Hi, mohan08!
If you didn't do it yet I strongly suggest you to use the built-in macro recorder (Programmer tab, Record Macro button), perform all the actions that you describe, stop recording, and then analyze the created code.
Regards!

 

[mohan08]

Hi SirJB7

I tried to record macro and below is the code generated. But the results for all the cells is 1.
But Cell D2 should be 0 as the values are not in the list1

Sub Macro2()
'
' Macro2 Macro
'

'
  ActiveCell.FormulaR1C1 = "=COUNTIF(Chandoo_Macro1.xlsm!list1,R[1]C)"
  Range("B2").Select
  Selection.FillRight
  Range("C2").Select
  Selection.FillRight
  Range("D2").Select
  Selection.FillRight
  ActiveCell.FormulaR1C1 = "=COUNTIF(Chandoo_Macro1.xlsm!list1,R[1]C)"
  Range("D2").Select
End Sub

I also tried writting code based on above recorded code and using loop.
But the values are not updating on the sheets and also didn't find any error message too.

Sub updateformula()
Dim rng As Range
Set rng = Range("A2:S2")
For Each Cell In rng
If Cell.Value = " " Then
Cell.Value = "=COUNTIF(Chandoo_Macro1.xlsm!list1,R[1]C)"
End If
Next Cell
End Sub

any help or suggestion what's wrong with the above codes.

 

[mohan08]

Hi All,

Any Help on the above code

 

[mohan08]

Hi,

Any help or suggestion on whats wrong with the above code.

Mohan