Making a formula and using it for all sheets

[RamziSaab]

I have the following the following macro:

Range("C1&quot.Select
Selection.FormulaArray = _
"=SUM(IF([Original.xls]Poland!R23C3:R264C3=R[1]C[-2],1,0))"
Selection.Cut Destination:=Range("C2&quot
Range("C2&quot.Select
Selection.AutoFill Destination:=Range("C2:C93&quot, Type:=xlFillDefault
Range("C2:C93&quot.Select

End Sub

and basically i want to do it for all my sheets (sheets are the country names) is there a way to tell it to do it and to put the results in the columns next to each other. So next one would be in Column D etc.

 

[xlbo]

Well, to start with, you can cut down your code to:

lRow = Range("B65536&quot.end(xlup).row
Range("C1:C" & lRow).FormulaArray = _
"=SUM(IF([Original.xls]Poland!R23C3:R264C3=R[1]C[-2],1,0))"

To do it for all sheets
lRow = sheets("Master&quot.range("B65536&quot.end(xlup).row
i=3
For each sht in thisworkbook.worksheets
if sht.name <> &quot;Master&quot; then
Sheets(&quot;Master&quot.range(cells(1,i),cells(lRow,i).FormulaArray = _
&quot;=SUM(IF([Original.xls]&quot; & sht.name & &quot;!R23C3:R264C3=R[1]C[-2],1,0))&quot;
i = i+1
else
end if
next


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Loomah]

Hi
This should do the trick

Sub a()
Dim ws As Worksheet
For Each ws In Workbooks("Original.xls").Worksheets
    With Cells(2, ws.Index + 2)
        .FormulaArray = _
        "=SUM(IF([Original.xls]" & ws.Name & "R23C3:R264C3=R[1]C[-2],1,0))"
        .AutoFill _
            Destination:=Range(Cells(2, ws.Index + 2), Cells(93, ws.Index + 2)), Type:=xlFillDefault
    End With
Next
End Sub

Happy Friday!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

You could even combine bits from both of the above!!

xlbo
See, slightly different soln! No copying!

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

ROFL
still got there before you tho

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[RamziSaab]

Loomah,

Your formuala does one sheet then gives me an error...

 

[RamziSaab]

Okay found out why it did not work missing an exclamation mark '.

New problem it does not work with sheets that already have an ' in them?? any ideas?

Sub a()
Dim ws As Worksheet
For Each ws In Workbooks(&quot;Original.xls&quot.Worksheets
With Cells(2, ws.Index + 2)
.FormulaArray = _
&quot;=SUM(IF('[Original.xls]&quot; & ws.Name & &quot;'!R23C3:R264C3=RC1,1,0))&quot;
.AutoFill _
Destination:=Range(Cells(2, ws.Index + 2), Cells(93, ws.Index + 2)), Type:=xlFillDefault
End With
Next
End Sub

 

[xlbo]

Ramzisaab - please read the site policies before posting any more questions
You have now double posted the same question - once at the end of a thread and as a new thread. This implies to me that you have not read the site policies....so please do

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[RamziSaab]

ok will do sorry