I have a dynamic excel sheet which is updated every month. What I am trying to do is to find the cell in column A that contains for example “May22”, and select the cells below it. For example let say “May22” is found in cell “A18”, I want to use the range “A20:C27” so it will be A(18+2):C(18+9). I would be glad if you can help me to write a VBA code for this.

1

Your question is missing quite a bit of detail about where you are getting several key pieces of information but I am going to try and help you with what you have provided so far.

Please keep in mind that VBA is not optimized and therefore much slower that the built in functions so I have provided below an example of how you can do what you are asking using the built-in OFFSET function, as well as a way you can use This same function via VBA.

Example:

tr
    {mso-height-source:auto;}
col
    {mso-width-source:auto;}
br
    {mso-data-placement:same-cell;}
.style0
    {mso-number-format:General;
    text-align:general;
    vertical-align:bottom;
    white-space:nowrap;
    mso-rotate:0;
    mso-background-source:auto;
    mso-pattern:auto;
    color:black;
    font-size:11.0pt;
    font-weight:400;
    font-style:normal;
    text-decoration:none;
    font-family:Calibri, sans-serif;
    mso-font-charset:0;
    border:none;
    mso-protection:locked visible;
    mso-style-name:Normal;
    mso-style-id:0;}
td
    {mso-style-parent:style0;
    padding-top:1px;
    padding-right:1px;
    padding-left:1px;
    mso-ignore:padding;
    color:black;
    font-size:11.0pt;
    font-weight:400;
    font-style:normal;
    text-decoration:none;
    font-family:Calibri, sans-serif;
    mso-font-charset:0;
    mso-number-format:General;
    text-align:general;
    vertical-align:bottom;
    border:none;
    mso-background-source:auto;
    mso-pattern:auto;
    mso-protection:locked visible;
    white-space:nowrap;
    mso-rotate:0;}
.xl65
    {mso-style-parent:style0;
    font-weight:700;
    border:.5pt solid #BFBFBF;}
.xl66
    {mso-style-parent:style0;
    border:.5pt solid #BFBFBF;
    background:gray;
    mso-pattern:black none;}
.xl67
    {mso-style-parent:style0;
    font-weight:700;
    text-align:left;
    border:.5pt solid #BFBFBF;}
.xl68
    {mso-style-parent:style0;
    font-weight:700;
    border:.5pt solid #BFBFBF;
    white-space:normal;}
.xl69
    {mso-style-parent:style0;
    border:.5pt solid #BFBFBF;}
.xl70
    {mso-style-parent:style0;
    mso-number-format:"@";
    border:.5pt solid #BFBFBF;}
.xl71
    {mso-style-parent:style0;
    text-align:left;
    border:.5pt solid #BFBFBF;}
.xl72
    {mso-style-parent:style0;
    text-align:left;
    border:.5pt solid #BFBFBF;
    padding-left:9px;
    mso-char-indent-count:1;}
.xl73
    {mso-style-parent:style0;
    border:.5pt solid #BFBFBF;
    white-space:normal;}
.xl74
    {mso-style-parent:style0;
    mso-number-format:"@";
    text-align:left;
    border:.5pt solid #BFBFBF;}
.xl75
    {mso-style-parent:style0;
    mso-number-format:"@";
    text-align:left;
    border:.5pt solid #BFBFBF;
    padding-left:9px;
    mso-char-indent-count:1;}
<body link="#0563C1" vlink="#954F72" class=xl69>

<table border=0 cellpadding=0 cellspacing=0 width=1370 style="border-collapse:
 collapse;table-layout:fixed;width:1029pt">
 <col class=xl69 width=69 style="mso-width-source:userset;mso-width-alt:2523;
 width:52pt">
 <col class=xl69 width=73 style="mso-width-source:userset;mso-width-alt:2669;
 width:55pt">
 <col class=xl66 width=22 style="mso-width-source:userset;mso-width-alt:804;
 width:17pt">
 <col class=xl65 width=288 style="mso-width-source:userset;mso-width-alt:10532;
 width:216pt">
 <col class=xl71 width=59 style="mso-width-source:userset;mso-width-alt:2157;
 width:44pt">
 <col class=xl72 width=309 style="mso-width-source:userset;mso-width-alt:11300;
 width:232pt">
 <col class=xl73 width=550 style="mso-width-source:userset;mso-width-alt:20114;
 width:413pt">
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl65 width=69 style="height:30.75pt;width:52pt">Mon-Year</td>
  <td class=xl65 width=73 style="border-left:none;width:55pt">Some Data</td>
  <td class=xl66 width=22 style="border-left:none;width:17pt">&nbsp;</td>
  <td class=xl65 width=288 style="border-left:none;width:216pt">&nbsp;</td>
  <td class=xl67 width=59 style="border-left:none;width:44pt">Results</td>
  <td class=xl67 width=309 style="border-left:none;width:232pt">Actual Formulas</td>
  <td class=xl68 width=550 style="border-left:none;width:413pt">Notes</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Jan22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">1</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Total Columns To
  Return</td>
  <td class=xl71 style="border-top:none;border-left:none">2</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">This
  is just the number of columns you want to return as part of your data array</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Feb22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">2</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Value To Match</td>
  <td class=xl74 style="border-top:none;border-left:none">Sep22</td>
  <td class=xl75 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">This
  is just the text to find</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Mar22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">3</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Find Matching Value</td>
  <td class=xl71 style="border-top:none;border-left:none">10</td>
  <td class=xl72 style="border-top:none;border-left:none">=MATCH(E3,A:A,0)</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">This
  finds the value to match in the first column</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Apr22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">4</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Total Rows In Sheet</td>
  <td class=xl71 style="border-top:none;border-left:none">21</td>
  <td class=xl72 style="border-top:none;border-left:none">=COUNTA(A:A)</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">This
  counts the total number of rows in column A that are not blank</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">May22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">5</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Rows After Found
  Value</td>
  <td class=xl71 style="border-top:none;border-left:none">11</td>
  <td class=xl72 style="border-top:none;border-left:none">=E5-E4</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">This
  is the number of rows found after the value to match cell</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Jun22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">6</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Create Address Of
  Starting Cell</td>
  <td class=xl71 style="border-top:none;border-left:none">$A$10</td>
  <td class=xl72 style="border-top:none;border-left:none">=ADDRESS(E4,1)</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">This
  gets us the address of the value to match cell. In the formulas below I will
  be using the INDIRECT function to make this a cell reference that can be used
  in formulas</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Jul22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">7</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Create Array Of Data
  After Matching Value</td>
  <td class=xl71 style="border-top:none;border-left:none">#VALUE!</td>
  <td class=xl72 style="border-top:none;border-left:none">=OFFSET(INDIRECT(E7),1,0,E6,E2)</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">The
  #VALUE here is normal because this is returning an array and excel cannot
  display multiple cells worth of data in a single cell.<span
  style="mso-spacerun:yes"> </span></td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Aug22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">8</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Value From Array,
  First Row, First Column<span style="mso-spacerun:yes"> </span></td>
  <td class=xl71 style="border-top:none;border-left:none">Oct22</td>
  <td class=xl72 style="border-top:none;border-left:none">=INDEX(OFFSET(INDIRECT(E7),1,0,E6,E2),1,1)</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Sep22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">9</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Value From Array,
  Last Row, Second Column<span style="mso-spacerun:yes"> </span></td>
  <td class=xl71 style="border-top:none;border-left:none">20</td>
  <td class=xl72 style="border-top:none;border-left:none">=INDEX(OFFSET(INDIRECT(E7),1,0,E6,E2),E6,E2)</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Oct22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">10</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">Value From Array, Sum
  of Second Column</td>
  <td class=xl71 style="border-top:none;border-left:none">165</td>
  <td class=xl72 style="border-top:none;border-left:none">=SUM(OFFSET(INDIRECT(E7),1,1,E6,1))</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Nov22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">11</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Dec22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">12</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Jan22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">13</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Feb22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">14</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Mar22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">15</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Apr22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">16</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">May22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">17</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Jun22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">18</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Jul22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">19</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>
 <tr height=41 style="mso-height-source:userset;height:30.75pt">
  <td height=41 class=xl70 style="height:30.75pt;border-top:none">Aug22</td>
  <td class=xl69 align=right style="border-top:none;border-left:none">20</td>
  <td class=xl66 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl65 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl71 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl72 style="border-top:none;border-left:none">&nbsp;</td>
  <td class=xl73 width=550 style="border-top:none;border-left:none;width:413pt">&nbsp;</td>
 </tr>     
</table>

</body>

Full explanation of the Offset function

Syntax: OFFSET(reference, rows, cols, [height], [width])

Here is my breakdown of the OFFSET function in regards to the example provided:

  • Reference The cell you want to start at. ($A$10 – The matched value)
  • Rows The number of rows you want to move from the starting cell reference. Basically moves your starting position. (1 – Moves to the next row because you want everything after the matched value.)
  • Cols The number of columns you want to move from the starting cell reference. Basically moves your starting position. (0 – No change because you want to include this column.)
  • Height Optional. The number of rows from your new starting position you want this to return. If used minimum 1. (11 – The number of rows to include.)
  • Width option. The number of columns from your new starting position you want this to return. If used minimum 1. (2 – The number of columns to include.)

Full explanation of the Application.Evaluate method. You can use any formula in VBA using the Application.Evaluate method. Example: Formula in Cell: =SUM(A1:B1) Formula in VBA: Application.Evaluate(“SUM(A1:B1)”)