Replace Col reference with Variable using "Countif formula" in For Loop

  • Hello,


    I thought this would be easy but everything I try gives me a blank sheet or a qualifier error. The code on the attached example gives the correct result when using a column reference to lookup Countif result (Sheet Orders Col14) in the For Loop. I want to change the code to use a variable for the "Countif" so that I can remove the column from the worksheet. I have tried using Application.WoksheetFuntion and R1C1 reference for the variable but am getting completely stuck. Hope that makes sense.


    Really appreciate the help this site provides to all of us that are learning.


    Cheers :)


    Jan



    Files

    • Book2.xlsm

      (22.77 kB, downloaded 69 times, last: )
  • I'm not sure exactly what you are doing, but the Data sheet is poorly formatted for working with to create any reports.


    Read this to understand how to use data in excel.


    Working with data in Excel

  • Hi Roy,


    I have up uploaded a new formatted spreadsheet. In doing so I noticed a code error which I have updated. This code was adapted from a larger project where the rows were resized based on the numbers in each size columns but for this project I only require the rows to be resized by the count. My apologies if this caused confusion. As mentioned above, is it possible to replace the reference to Column 14 with a variable? I just can't get the formula to work.

    Thanks.


    Jan


  • I haven't looked at anything but if you want a variable in a formula it should look something like this

    Code
    1. cnt.FormulaR1C1 = "=COUNTIF(R" & i & "C7:R" & i & "C12,>0)"

    I have not checked if it works.

  • After spending much time searching with no progress, I have made some changes to the above code and have used WorksheetFunction.Countif.

    (The if/else statement has been removed as I couldn't get it to give the correct result....still some work needed.)


    The issue with the code to this point is that without the "on Error Resume Next" I get an "Application Defined or Object Defined" error on Line 22. But I cannot work out what the problem is with the code. Any pointers in the right direction would be much appreciated as I am completely stumped. Thanks.

  • BTW

    Code
    1. n = WorksheetFunction.CountIf(shO.Range("H" & i & ": M" & i), ">0")

    The zero (">0") is not a value here but a string.

    If you want a value, as it probably need to be, you need just a zero (> 0). No double quotes.

  • Maybe

  • Any columns from the first column past row 3 having a value of "RRP" to the last used column in your data range minus 1 (Count Column)

    If you delete the "Count" Column, change the code to reflect that.

    In the "n = WorksheetFunction....." line, change the "lc - 1" to "lc"

    Add some extra Columns for sizes to try it out.

  • Hi Jolivanes,

    Fantastic. Thank you so much for your patience with this. I have run both sets of code but prefer you last option in Comment 12 because of the flexibility with columns.

    :/:?:I have spent some time understanding the code but don't quite follow the syntax in line 7 - Find "RRP" in Row 3. Commas rep Optional arguments - how do you know how many to put in? and what is the "1"? If you have a moment, could you please explain.

    I have read these 2 article https://docs.microsoft.com/en-…/vba/api/Excel.Range.Find and this https://www.ozgrid.com/VBA/find-method.htm but still not quite getting the syntax.


    Cheers and have a great day. 8)

  • If you check out this article

    https://excelmacromastery.com/excel-vba-find/

    and come back to this

    Function Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat]) As Range

    you'll see that the fourth item says "LookAt" (xlWhole or xlPart)

    We want to use "Lookat:=xlWhole" which is the same as 1


    Hope that this explains it. If not, we'll hear it.

    Good Luck in the meantime.