Vairable Countif

[codexsampler]

Hello,

The macro I am using for Excel 2007 moves the column Oranges next to Kiwis based on the name Oranges and Kiwis found in Row1. The problem I have is that there are countif statements that need to be able to work no matter the location the Oranges and Kiwis columns are.

At this point in the macro an inserted row has been made between the types of fruit and the random number row. Is there a way that the countif range can stop at this separator so as not to cause circular referencing?

Below is the section of code that I tried to modify. (Arrow is start of my attempt at the countif modification.)

Range("A1").Select
    ActiveCell.Select
    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 1
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveSheet.UsedRange.Columns.AutoFit
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.Insert Shift:=xlDown
    Dim s As Long, t As Long
    s = 0: t = 0
    On Error Resume Next
    s = Application.Match("Oranges", Rows(1), 0)
    t = Application.Match("Kiwis", Rows(1), 0)
    On Error GoTo 0
    If t = 0 Or s = 0 Then
    MsgBox "Either/or both 'Oranges' and/or 'Kiwis' do not exist - macro terminated!"
    Exit Sub
    End If
    Application.ScreenUpdating = False
    If s = t - 1 Then
    'do nothing
    Else
     Columns(t).Insert
     Columns(t).Value = Columns(s).Value
     Columns(s).Delete
    End If
    Application.ScreenUpdating = True
    Range("A1").Select
    Cells.Find(What:="Status", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.End(xlDown).Select
    ActiveCell.Offset(7, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Naval"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Golden"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Percent"
    ActiveCell.Offset(1, -2).Range("A1").Select
 
' ----> Start of countif statements
Dim x As Long, u As Long, v As Long, a As Long, b As Long, p As Long, c As Long
a = 0: b = 0: u = 0: v = 0
On Error Resume Next
a = Application.Match("Oranges", Rows(1))
b = Application.Match("Kiwis", Rows(1))
p = Columns(a).End(x1Down).Row
v = Columns(b).End(x1Down).Row
 
x = Range("p" & Rows.Count).End(xlUp).Row
If x< 1 Then x = 1
ActiveCell.Formula = Application.WorksheetFunction.CountIf(Range("p" & x), "Naval") - 1
ActiveCell.Offset(0, 1).Range("A1").Select
x = Range("v" & Rows.Count).End(xlUp).Row
If x< 1 Then x = 1
ActiveCell.Formula = Application.WorksheetFunction.CountIf(Range("v" & x), "Golden") - 1
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
ActiveCell.Offset(0, 0).Select
Selection.NumberFormat = "0.00%"

Excel Workbook
	A	B	C	D	E	F	G	H
1	#	Apples	Tangerines	Bananas	Oranges	Kiwis	Cherries	Grapes
2	1	Granny Smith	Minneola	Manzano	Blood	Golden	Lambert	Bronx
3	2	Red Delicious	Fairchild	Red	Naval	Green	Montmorency	Concord
4	3	Braeburn	Algerian	Plantain	Valencia	Red	Rainier	Emperor
5	4	Honeycrisp	Clementine	Cavendish	Cara Cara	Berry	Royal Ann	Perlette
6	5	Red Delicious	Minneola	Red	Naval	Green	Montmorency	Concord
7	6	Red Delicious	Clementine	Cavendish	Blood	Red	Rainier	Emperor
8	7	Granny Smith	Algerian	Plantain	Naval	Berry	Lambert	Bronx
9	8	Red Delicious	Fairchild	Manzano	Valencia	Golden	Royal Ann	Perlette
10	9	Braeburn	Clementine	Cavendish	Blood	Green	Rainier	Bronx
11	10	Granny Smith	Algerian	Red	Cara Cara	Berry	Montmorency	Concord
12	11	Braeburn	Minneola	Plantain	Valencia	Red	Rainier	Emperor
13	12	Red Delicious	Fairchild	Cavendish	Blood	Golden	Lambert	Perlette
14	13	Honeycrisp	Minneola	Manzano	Cara Cara	Berry	Royal Ann	Bronx
15	14	Granny Smith	Clementine	Red	Naval	Golden	Montmorency	Concord
16	15	Red Delicious	Fairchild	Manzano	Cara Cara	Red	Royal Ann	Perlette
17	16	Honeycrisp	Algerian	Plantain	Valencia	Green	Lambert	Emperor
18	17	Honeycrisp	Minneola	Plantain	Blood	Berry	Montmorency	Bronx
19	18	Granny Smith	Clementine	Cavendish	Cara Cara	Golden	Rainier	Concord
20	19	Red Delicious	Fairchild	Manzano	Cara Cara	Green	Lambert	Emperor
21	20	Granny Smith	Minneola	Red	Blood	Berry	Rainier	Perlette
22	21	Granny Smith	Algerian	Manzano	Valencia	Golden	Royal Ann	Bronx
23	22	Braeburn	Minneola	Cavendish	Naval	Red	Montmorency	Concord
24
25		51	65	87	21	56	48	49
26		211	596	485	700	215	65	87
27
28					Naval	Golden	Percent
29					5	6	83.33%
Sheet1 the number of Naval in Oranges is counted and the Number of Golden in Kiwis is counted.

Excel 2007

Cell Formulas
Range	Formula
E29	=COUNTIF(E1:E23, "Naval")
F29	=COUNTIF(F1:F23, "Golden")
G29	=E29/F29

Excel Workbook
	A	B	C	D	E	F	G
1	#	Apples	Bananas	Oranges	Kiwis	Cherries	Grapes
2	1	Granny Smith	Manzano	Blood	Golden	Lambert	Bronx
3	2	Red Delicious	Red	Naval	Green	Montmorency	Concord
4	3	Braeburn	Plantain	Valencia	Red	Rainier	Emperor
5	4	Honeycrisp	Cavendish	Cara Cara	Berry	Royal Ann	Perlette
6	5	Red Delicious	Red	Naval	Green	Montmorency	Concord
7	6	Red Delicious	Cavendish	Blood	Red	Rainier	Emperor
8	7	Granny Smith	Plantain	Naval	Berry	Lambert	Bronx
9	8	Red Delicious	Manzano	Valencia	Golden	Royal Ann	Perlette
10	9	Braeburn	Cavendish	Blood	Green	Rainier	Bronx
11	10	Granny Smith	Red	Cara Cara	Berry	Montmorency	Concord
12	11	Braeburn	Plantain	Valencia	Red	Rainier	Emperor
13	12	Red Delicious	Cavendish	Blood	Golden	Lambert	Perlette
14	13	Honeycrisp	Manzano	Cara Cara	Berry	Royal Ann	Bronx
15	14	Granny Smith	Red	Naval	Golden	Montmorency	Concord
16	15	Red Delicious	Manzano	Cara Cara	Red	Royal Ann	Perlette
17	16	Honeycrisp	Plantain	Valencia	Green	Lambert	Emperor
18	17	Honeycrisp	Plantain	Blood	Berry	Montmorency	Bronx
19	18	Granny Smith	Cavendish	Cara Cara	Golden	Rainier	Concord
20	19	Red Delicious	Manzano	Cara Cara	Green	Lambert	Emperor
21	20	Granny Smith	Red	Blood	Berry	Rainier	Perlette
22	21	Granny Smith	Manzano	Valencia	Golden	Royal Ann	Bronx
23	22	Braeburn	Cavendish	Naval	Red	Montmorency	Concord
24
25		51	87	21	56	48	49
26		211	485	700	215	65	87
27
28				Naval	Golden	Percent
29				5	6	83.33%
Sheet2 Oranges and Kiwis are one to left.

Excel 2007

Cell Formulas
Range	Formula
D29	=COUNTIF(D1:D23, "Naval")
E29	=COUNTIF(E1:E23, "Golden")
F29	=D29/E29

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	#	Apples	Pumpkins	Cherries	Tangerines	Bananas	Oranges	Kiwis	Grapes
2	1	Granny Smith	Howden	Lambert	Minneola	Manzano	Blood	Golden	Bronx
3	2	Red Delicious	Kakai	Montmorency	Fairchild	Red	Naval	Green	Concord
4	3	Braeburn	Valenciano	Rainier	Algerian	Plantain	Valencia	Red	Emperor
5	4	Honeycrisp	Lumina	Royal Ann	Clementine	Cavendish	Cara Cara	Berry	Perlette
6	5	Red Delicious	Kakai	Montmorency	Minneola	Red	Naval	Green	Concord
7	6	Red Delicious	Lumina	Rainier	Clementine	Cavendish	Blood	Red	Emperor
8	7	Granny Smith	Howden	Lambert	Algerian	Plantain	Naval	Berry	Bronx
9	8	Red Delicious	Valenciano	Royal Ann	Fairchild	Manzano	Valencia	Golden	Perlette
10	9	Braeburn	Kakai	Rainier	Clementine	Cavendish	Blood	Green	Bronx
11	10	Granny Smith	Lumina	Montmorency	Algerian	Red	Cara Cara	Berry	Concord
12	11	Braeburn	Howden	Rainier	Minneola	Plantain	Valencia	Red	Emperor
13	12	Red Delicious	Kakai	Lambert	Fairchild	Cavendish	Blood	Golden	Perlette
14	13	Honeycrisp	Valenciano	Royal Ann	Minneola	Manzano	Cara Cara	Berry	Bronx
15	14	Granny Smith	Howden	Montmorency	Clementine	Red	Naval	Golden	Concord
16	15	Red Delicious	Valenciano	Royal Ann	Fairchild	Manzano	Cara Cara	Red	Perlette
17	16	Honeycrisp	Lumina	Lambert	Algerian	Plantain	Valencia	Green	Emperor
18	17	Honeycrisp	Howden	Montmorency	Minneola	Plantain	Blood	Berry	Bronx
19	18	Granny Smith	Kakai	Rainier	Clementine	Cavendish	Cara Cara	Golden	Concord
20	19	Red Delicious	Valenciano	Lambert	Fairchild	Manzano	Cara Cara	Green	Emperor
21	20	Granny Smith	Lumina	Rainier	Minneola	Red	Blood	Berry	Perlette
22	21	Granny Smith	Howden	Royal Ann	Algerian	Manzano	Valencia	Golden	Bronx
23	22	Braeburn	Kakai	Montmorency	Minneola	Cavendish	Naval	Red	Concord
24
25		51	48	48	65	87	21	56	49
26		211	312	65	596	485	700	215	87
27
28							Naval	Golden	Percent
29							5	6	83.33%
Sheet3 Oranges and Kiwis are two to the right compared to Sheet1

Excel 2007

Cell Formulas
Range	Formula
G29	=COUNTIF(G1:G23, "Naval")
H29	=COUNTIF(H1:H23, "Golden")
I29	=G29/H29

Thanks for your help,
codexsampler

 

[Jerry Sullivan]

Hi codexsampler,

Perhaps instead of inserting and deleting columns, you could read the values of the affected columns into Variant Arrays, then fill them back in the desired columns.

EDIT: Regarding your Countif formulas and circular references, if you took the approach above, you could have formulas that don't need to be modified when your column data is rearranged.

 

[Sandeep Warrier]

Another way

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">#</td><td style="font-weight: bold;;">Apples</td><td style="font-weight: bold;;">Tangerines</td><td style="font-weight: bold;;">Bananas</td><td style="font-weight: bold;;">Oranges</td><td style="font-weight: bold;;">Kiwis</td><td style="font-weight: bold;;">Cherries</td><td style="font-weight: bold;;">Grapes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Counts</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">Granny Smith</td><td style=";">Minneola</td><td style=";">Manzano</td><td style=";">Blood</td><td style=";">Golden</td><td style=";">Lambert</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style=";">Red Delicious</td><td style=";">Fairchild</td><td style=";">Red</td><td style=";">Naval</td><td style=";">Green</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style=";">Braeburn</td><td style=";">Algerian</td><td style=";">Plantain</td><td style=";">Valencia</td><td style=";">Red</td><td style=";">Rainier</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style=";">Honeycrisp</td><td style=";">Clementine</td><td style=";">Cavendish</td><td style=";">Cara Cara</td><td style=";">Berry</td><td style=";">Royal Ann</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style=";">Red Delicious</td><td style=";">Minneola</td><td style=";">Red</td><td style=";">Naval</td><td style=";">Green</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style=";">Red Delicious</td><td style=";">Clementine</td><td style=";">Cavendish</td><td style=";">Blood</td><td style=";">Red</td><td style=";">Rainier</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style=";">Granny Smith</td><td style=";">Algerian</td><td style=";">Plantain</td><td style=";">Naval</td><td style=";">Berry</td><td style=";">Lambert</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td><td style=";">Red Delicious</td><td style=";">Fairchild</td><td style=";">Manzano</td><td style=";">Valencia</td><td style=";">Golden</td><td style=";">Royal Ann</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9</td><td style=";">Braeburn</td><td style=";">Clementine</td><td style=";">Cavendish</td><td style=";">Blood</td><td style=";">Green</td><td style=";">Rainier</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10</td><td style=";">Granny Smith</td><td style=";">Algerian</td><td style=";">Red</td><td style=";">Cara Cara</td><td style=";">Berry</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">11</td><td style=";">Braeburn</td><td style=";">Minneola</td><td style=";">Plantain</td><td style=";">Valencia</td><td style=";">Red</td><td style=";">Rainier</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">12</td><td style=";">Red Delicious</td><td style=";">Fairchild</td><td style=";">Cavendish</td><td style=";">Blood</td><td style=";">Golden</td><td style=";">Lambert</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">13</td><td style=";">Honeycrisp</td><td style=";">Minneola</td><td style=";">Manzano</td><td style=";">Cara Cara</td><td style=";">Berry</td><td style=";">Royal Ann</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">14</td><td style=";">Granny Smith</td><td style=";">Clementine</td><td style=";">Red</td><td style=";">Naval</td><td style=";">Golden</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">15</td><td style=";">Red Delicious</td><td style=";">Fairchild</td><td style=";">Manzano</td><td style=";">Cara Cara</td><td style=";">Red</td><td style=";">Royal Ann</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">16</td><td style=";">Honeycrisp</td><td style=";">Algerian</td><td style=";">Plantain</td><td style=";">Valencia</td><td style=";">Green</td><td style=";">Lambert</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">17</td><td style=";">Honeycrisp</td><td style=";">Minneola</td><td style=";">Plantain</td><td style=";">Blood</td><td style=";">Berry</td><td style=";">Montmorency</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">18</td><td style=";">Granny Smith</td><td style=";">Clementine</td><td style=";">Cavendish</td><td style=";">Cara Cara</td><td style=";">Golden</td><td style=";">Rainier</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">19</td><td style=";">Red Delicious</td><td style=";">Fairchild</td><td style=";">Manzano</td><td style=";">Cara Cara</td><td style=";">Green</td><td style=";">Lambert</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">20</td><td style=";">Granny Smith</td><td style=";">Minneola</td><td style=";">Red</td><td style=";">Blood</td><td style=";">Berry</td><td style=";">Rainier</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">21</td><td style=";">Granny Smith</td><td style=";">Algerian</td><td style=";">Manzano</td><td style=";">Valencia</td><td style=";">Golden</td><td style=";">Royal Ann</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">22</td><td style=";">Braeburn</td><td style=";">Minneola</td><td style=";">Cavendish</td><td style=";">Naval</td><td style=";">Red</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L2</th><td style="text-align:left">=COUNTIF(<font color="Blue">INDEX(<font color="Red">$A$2:$H$23,0,MATCH(<font color="Green">"Oranges",$A$1:$H$1,0</font>)</font>),"Naval"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L3</th><td style="text-align:left">=COUNTIF(<font color="Blue">INDEX(<font color="Red">$A$2:$H$23,0,MATCH(<font color="Green">"Kiwis",$A$1:$H$1,0</font>)</font>),"Golden"</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">#</td><td style="font-weight: bold;;">Apples</td><td style="font-weight: bold;;">Bananas</td><td style="font-weight: bold;;">Oranges</td><td style="font-weight: bold;;">Kiwis</td><td style="font-weight: bold;;">Cherries</td><td style="font-weight: bold;;">Grapes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Counts</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">Granny Smith</td><td style=";">Manzano</td><td style=";">Blood</td><td style=";">Golden</td><td style=";">Lambert</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style=";">Red Delicious</td><td style=";">Red</td><td style=";">Naval</td><td style=";">Green</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style=";">Braeburn</td><td style=";">Plantain</td><td style=";">Valencia</td><td style=";">Red</td><td style=";">Rainier</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style=";">Honeycrisp</td><td style=";">Cavendish</td><td style=";">Cara Cara</td><td style=";">Berry</td><td style=";">Royal Ann</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style=";">Red Delicious</td><td style=";">Red</td><td style=";">Naval</td><td style=";">Green</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style=";">Red Delicious</td><td style=";">Cavendish</td><td style=";">Blood</td><td style=";">Red</td><td style=";">Rainier</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style=";">Granny Smith</td><td style=";">Plantain</td><td style=";">Naval</td><td style=";">Berry</td><td style=";">Lambert</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td><td style=";">Red Delicious</td><td style=";">Manzano</td><td style=";">Valencia</td><td style=";">Golden</td><td style=";">Royal Ann</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9</td><td style=";">Braeburn</td><td style=";">Cavendish</td><td style=";">Blood</td><td style=";">Green</td><td style=";">Rainier</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10</td><td style=";">Granny Smith</td><td style=";">Red</td><td style=";">Cara Cara</td><td style=";">Berry</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">11</td><td style=";">Braeburn</td><td style=";">Plantain</td><td style=";">Valencia</td><td style=";">Red</td><td style=";">Rainier</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">12</td><td style=";">Red Delicious</td><td style=";">Cavendish</td><td style=";">Blood</td><td style=";">Golden</td><td style=";">Lambert</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">13</td><td style=";">Honeycrisp</td><td style=";">Manzano</td><td style=";">Cara Cara</td><td style=";">Berry</td><td style=";">Royal Ann</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">14</td><td style=";">Granny Smith</td><td style=";">Red</td><td style=";">Naval</td><td style=";">Golden</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">15</td><td style=";">Red Delicious</td><td style=";">Manzano</td><td style=";">Cara Cara</td><td style=";">Red</td><td style=";">Royal Ann</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">16</td><td style=";">Honeycrisp</td><td style=";">Plantain</td><td style=";">Valencia</td><td style=";">Green</td><td style=";">Lambert</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">17</td><td style=";">Honeycrisp</td><td style=";">Plantain</td><td style=";">Blood</td><td style=";">Berry</td><td style=";">Montmorency</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">18</td><td style=";">Granny Smith</td><td style=";">Cavendish</td><td style=";">Cara Cara</td><td style=";">Golden</td><td style=";">Rainier</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">19</td><td style=";">Red Delicious</td><td style=";">Manzano</td><td style=";">Cara Cara</td><td style=";">Green</td><td style=";">Lambert</td><td style=";">Emperor</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">20</td><td style=";">Granny Smith</td><td style=";">Red</td><td style=";">Blood</td><td style=";">Berry</td><td style=";">Rainier</td><td style=";">Perlette</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">21</td><td style=";">Granny Smith</td><td style=";">Manzano</td><td style=";">Valencia</td><td style=";">Golden</td><td style=";">Royal Ann</td><td style=";">Bronx</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">22</td><td style=";">Braeburn</td><td style=";">Cavendish</td><td style=";">Naval</td><td style=";">Red</td><td style=";">Montmorency</td><td style=";">Concord</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L2</th><td style="text-align:left">=COUNTIF(<font color="Blue">INDEX(<font color="Red">$A$2:$G$23,0,MATCH(<font color="Green">"Oranges",$A$1:$G$1,0</font>)</font>),"Naval"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L3</th><td style="text-align:left">=COUNTIF(<font color="Blue">INDEX(<font color="Red">$A$2:$G$23,0,MATCH(<font color="Green">"Kiwis",$A$1:$G$1,0</font>)</font>),"Golden"</font>)</td></tr></tbody></table></td></tr></table><br />

 

[codexsampler]

Thank you JS411 and Sandeep for your input.

I do not think I clearly stated what it is I am looking for the macro to do.

The data for the worksheet is received from a server. The user is able to select any number of fruits that they want with Oranges and Kiwis selected by default. The macro should be able to take whatever amount of data is imported and arrange it in the way it is presented in Sheet 1 in my first post.

The issue here is that Oranges and Kiwis can be imported into any column letter and the number of rows can be anywhere from 10 to 300+.The column Oranges moves next to Kiwis for ease of reference for the calculation chart located several cells under it. What I would like is a flexible code that will be able to find the Oranges/Kiwis column and then count the number of times that Naval/Golden appears.

I am looking to be able to just activate the macro and have everything done automatically regardless of the initial locations of the Oranges and Kiwis columns and the number of rows that the data extends to.

I am new to using VBA. All that I know is what I figured out while using the record macro function and what I can glean from the internet. I do not understand what a Variant Array is or how to implement it.

 

[Jerry Sullivan]

codexsampler, Your clarification was helpful, thanks.

Below is some code for you to try.

Sub FruitCount()
    Const sFruit1 = "Oranges"
    Const sType1 = "Naval"
    Const sFruit2 = "Kiwis"
    Const sType2 = "Golden"
    Const lBlankRows = 7 'blank rows above countif table
    Dim lLastRowData As Long
    Dim lCol1 As Long, lCol2 As Long
    
    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False
    
    On Error Resume Next
    lCol1 = Application.Match(sFruit1, Rows(1), 0)
    lCol2 = Application.Match(sFruit2, Rows(1), 0)
    On Error GoTo 0
    
    If lCol1 = 0 Or lCol2 = 0 Then
        MsgBox "Either/or both '" & sFruit1 & "' and/or '" & sFruit2 & _
            "' do not exist - macro terminated!"
        Exit Sub
    End If

    If lCol1 <> lCol2 - 1 Then
        Columns(lCol1).Cut
        Columns(lCol2).Insert
        lCol1 = Application.Match(sFruit1, Rows(1), 0)
    End If
     
    lLastRowData = Cells.Find(What:="*", After:=Range("A1"), _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    With Cells(lLastRowData + lBlankRows, lCol1) 'Top left cell of table
        .Value = sType1
        .Offset(0, 1) = sType2
        .Offset(0, 2) = "Percent"
        .Offset(1, 0).Resize(1, 2).FormulaR1C1 = "=COUNTIF(R1C:R" & _
            lLastRowData & "C, R[-1]C)"
        With .Offset(1, 2)
            .FormulaR1C1 = "=RC[-2]/RC[-1]"
            .NumberFormat = "0.00%"
        End With
    End With

    'your sheet view/formatting code
    Range("A1").CurrentRegion.AutoFilter
    With ActiveWindow
        .SplitColumn = 1
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveSheet.UsedRange.Columns.AutoFit
End Sub

I didn't completely follow what is happening on the few rows above your Countif formula tables (Rows 24:27 on your example). So you'll probably need to make some minor adjustments to the Constant lBlankRows, and add back in the code to insert one row.