VBA Macro to Fill in Formulas Referencing Separate Sheet

[rschappe]

Hello All,

Thank in advance for taking look.

My code is very simple. I creates a summary table of important stats I need to know about data in Sheet1.

     shtName = ActiveSheet.Name
    strName = InputBox(Prompt:="Enter AOI Name, please.", _
    Title:="Generate Summary", Default:="")
    Sheets.Add After:=ActiveSheet

' add formulas
    Range("B2").Formula = "=COUNTIF('" & shtName & "'!G:G, 0)"
    Range("B3").Formula = "=COUNTIF('" & shtName & "'!G:G, <101)"
    Range("B5").Formula = "=MAX('" & shtName & "'!Q:Q)"
    Range("B6").Formula = "=MAX('" & shtName & "'!R:R)"
    Range("B7").Formula = "=AVERAGE('" & shtName & "'!Q:Q)"
    Range("B8").Formula = "=AVERAGE('" & shtName & "'!R:R)"
    Range("B9").Formula = "=MIN('" & shtName & "'!S:S)"
    Range("B10").Formula = "=MAX('" & shtName & "'!S:S)"
    Range("B11").Forumla = "=MIN('" & shtName & "'!T:T)"
    Range("B12").Formula = "=MAX('" & shtName & "'!T:T)"
    Range("B13").Forumla = "=MIN('" & shtName & "'!W:W)"
    Range("B14").Formula = "=MAX('" & shtName & "'!W:W)"
    Range("B15").Formula = "=AVERAGE('" & shtName & "'!W:W)"
    Range("B16").Formula = "=MIN('" & shtName & "'!X:X)"
    Range("B17").Formula = "=MAX('" & shtName & "'!X:X)"
    Range("B18").Formula = "=MIN('" & shtName & "'!Y:Y)"
    Range("B19").Formula = "=MAX('" & shtName & "'!Y:Y)"

Code is failing in two places. The formula for range ("B3") doesn't function as I cant figure out the correct escape character to input "<101" into the second argument of the COUNTIF statement.

Secondly, and most oddly, only the first MIN statement functions. The rest return an error. Seems very strange since they are syntactically the same.


Thanks for taking a look.

Robert

 

[JLGWhiz]

Hello All,

Thank in advance for taking look.

My code is very simple. I creates a summary table of important stats I need to know about data in Sheet1.

     shtName = ActiveSheet.Name
    strName = InputBox(Prompt:="Enter AOI Name, please.", _
    Title:="Generate Summary", Default:="")
    Sheets.Add After:=ActiveSheet

' add formulas
    Range("B2").Formula = "=COUNTIF('" & shtName & "'!G:G, 0)"
    Range("B3").Formula = "=COUNTIF('" & shtName & "'!G:G, <101)"
    Range("B5").Formula = "=MAX('" & shtName & "'!Q:Q)"
    Range("B6").Formula = "=MAX('" & shtName & "'!R:R)"
    Range("B7").Formula = "=AVERAGE('" & shtName & "'!Q:Q)"
    Range("B8").Formula = "=AVERAGE('" & shtName & "'!R:R)"
    Range("B9").Formula = "=MIN('" & shtName & "'!S:S)"
    Range("B10").Formula = "=MAX('" & shtName & "'!S:S)"
    Range("B11").Forumla = "=MIN('" & shtName & "'!T:T)"
    Range("B12").Formula = "=MAX('" & shtName & "'!T:T)"
    Range("B13").Forumla = "=MIN('" & shtName & "'!W:W)"
    Range("B14").Formula = "=MAX('" & shtName & "'!W:W)"
    Range("B15").Formula = "=AVERAGE('" & shtName & "'!W:W)"
    Range("B16").Formula = "=MIN('" & shtName & "'!X:X)"
    Range("B17").Formula = "=MAX('" & shtName & "'!X:X)"
    Range("B18").Formula = "=MIN('" & shtName & "'!Y:Y)"
    Range("B19").Formula = "=MAX('" & shtName & "'!Y:Y)"

Code is failing in two places. The formula for range ("B3") doesn't function as I cant figure out the correct escape character to input "<101" into the second argument of the COUNTIF statement.

Secondly, and most oddly, only the first MIN statement functions. The rest return an error. Seems very strange since they are syntactically the same.


Thanks for taking a look.

Robert

Enclose <101 in double quotation marks

Range("B3").Formula = "=COUNTIF('" & shtName & "'!G:G, ""<101"")"

and correct the spelling of Formula for Range("B11") and ("B13").

 

[rschappe]

I suppose my brain was just auto-correcting it every time that I read it.

Thanks for catching that!

Everything works!

 

[JLGWhiz]

I suppose my brain was just auto-correcting it every time that I read it.

Thanks for catching that!

Everything works!

been there, done that,
Regards, JLG