0

I currently have a function (which I am automating into a macro) =COUNTIF(export!$A$1:$CD$250000,"*Cycle*") It scans a sheet and returns how many times the word cycle has been written.

My sheet name is "export" but this is constantly changing for each spreadsheet so I want to be able to pass the name of the Excel sheet into the formula automatically like so =COUNTIF(Sheet name put here automatically!$A$1:$CD$250000,"*Cycle*")

Thank you for any replies

Edit: Full Macro

Sub Running_Mode()
'
' Running_Mode Macro
'

'


    ActiveCell.FormulaR1C1 = "Cycle-Sentry"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Continuous"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Standby"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(export!R1C1:R250000C82,""*Continuous*"")"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B2"), Type:=xlFillDefault
    Range("B1:B2").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(export!R1C1:R250000C82,""*Cycle-Sentry*"")"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B3"), Type:=xlFillDefault
    Range("B2:B3").Select
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(export!R1C1:R250000C82,""*Standby*"")"
    Range("A1:B3").Select
    Range("B3").Activate
    ActiveSheet.Shapes.AddChart2(251, xlPie).Select
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Running Modes"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Running Modes"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 13).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 13).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
End Sub```
ReeceG
  • 49
  • 6
  • Possible duplicate of [How can I insert variable into formula in VBA](https://stackoverflow.com/questions/42503316/how-can-i-insert-variable-into-formula-in-vba) – BigBen Nov 21 '19 at 15:20
  • Just concatenate the worksheet name in - see the dupe target. Make sure to add a single quote `'` before and after the sheet name. – BigBen Nov 21 '19 at 15:20
  • Sorry I have absolutely no prior experience of VBA . By concatenating the worksheet name in, does that have to be done manually for every sheet. I have to use this macro for about 1000 different documents, so the sheet name will change every time – ReeceG Nov 21 '19 at 15:27
  • No - you should use the `.Name` of your `Worksheet` *variable*. No manual hard-coding. – BigBen Nov 21 '19 at 15:28
  • @BigBen I added my Macro above . I Have no work sheet variable. I tried unsuccessfully to make one and pass it into the COUNTIF. Thank you for your help – ReeceG Nov 21 '19 at 16:00
  • 1
    Use `ActiveSheet.Name` then. – BigBen Nov 21 '19 at 16:10
  • 2
    Side note: you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. Understanding how to assign objects and strings to variables will make the task you have much easier – cybernetic.nomad Nov 21 '19 at 16:24

0 Answers0