Incorrect format in formula

Tom Munsell · Jun 16th 2008

In the code below, the variables Steps1, Tested1, Steps2, Tested2, are 0 even though there is data. Thanks for your help.

Tom

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim ws As Variant, qt As String, Steps1 As Integer, Steps2 As Integer
  Dim Tested1 As Integer, Tested2 As Integer, Regress As Integer, Funct As Integer
  qt = Chr(34)
  
  For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next
    If ws.index < 8 Then
      'DoNothing()
    ElseIf ws.Range("A3").Value <> "Step" Then
      'DoNothing()
    Else
      Debug.Print ws.Name
      Steps1 = ws.FormulaR1C1 = "=COUNTA(" & qt & "A5:A5000" & qt & ")"
      Tested1 = ws.FormulaR1C1 = "=COUNTA(" & qt & "H5:J5000" & qt & ")"
      
      Steps2 = ws.FormulaR1C1 = "=COUNTA(" & qt & "L5:L5000" & qt & ")"
      Tested2 = ws.FormulaR1C1 = "=COUNTA(" & qt & "M5:N5000" & qt & ")"
      
      Funct = Steps1 - Tested1
      Regress = Steps2 - Tested2
      
      If Funct <> 0 Or Regress <> 0 Then Renumber 'Function
    End If
  Next ws


  Set ws = Nothing
End Sub

Display More

Anonymous · Jun 16th 2008

Re: Incorrect format in formula

Hi, and Welcome to OzGrid

You seem to be trying to do two thinsg in one line. First you are setting the variable equa to the formul ain the cell, and then you trying (intending) to write the formula to the cell. Try this sequence:

Code

ws.FormulaR1C1 = "= COUNTA(" & qt & "A5:A5000" & qt & ")"
            Steps1 = ws.cells(1,1)
            ws.FormulaR1C1 = "=COUNTA(" & qt & "H5:J5000" & qt & ")"
            Tested1 = ws.cells(1,1)
            ws.FormulaR1C1 = "=COUNTA(" & qt & "L5:L5000" & qt & ")"
            Steps2 = ws.cells(1,1)
            ws.FormulaR1C1 = "=COUNTA(" & qt & "M5:N5000" & qt & ")"
            Tested2 = ws.cells(1,1)
            ws.Clear

EDITED. Had thought initially you were not using ws as a range. And, I'm not sure I follow your code's syntax very well.

Tom Munsell · Jun 16th 2008

Re: Incorrect format in formula

Thanks for your reply. I am new at this.

I am using 'ws' to find a valid sheet; this works.
Per valid sheet, I want to get the count of values in a column A and store the int in a variable using the excel function "=COUNTA(". I then want to do the same for column H. I then compair and if not 0, I then execute a function.

I do not want to put a formula into a cell, if I am doing so then that is a mistake on my part.

Thanks,
Tom

Anonymous · Jun 17th 2008

Re: Incorrect format in formula

Ok. I think you are looking for something like this.

Code

Dim ws As Worksheet, qt As String, Steps1 As Long, Steps2 As Long
    Dim Tested1 As Long, Tested2 As Long, Regress As Long, Funct As Long
     
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        If ws.Index < 8 Then
             'DoNothing()
        ElseIf ws.Range("A3").Value <> " Step" Then
             'DoNothing()
        Else
            Debug.Print ws.Name
            Steps1 = WorksheetFunction.CountA(ws.Range("A5:A5000"))
            Tested1 = WorksheetFunction.CountA(ws.Range("H5:J5000"))
            Steps2 = WorksheetFunction.CountA(ws.Range("L5:L5000"))
            Tested2 = WorksheetFunction.CountA(ws.Range("M5:N5000"))
            
            Funct = Steps1 - Tested1
            Regress = Steps2 - Tested2
            
            MsgBox Funct & "  " & Regress
             
            If Funct <> 0 Or Regress <> 0 Then Renumber ' Function
        End If
        
    Next ws

Display More

By the way, your code is looking specifically for cell A3 to be filled with (exactly) the five character string of the word Step preceded by a space. Is this what you intend?

Tom Munsell · Jun 17th 2008

Re: Incorrect format in formula

Yes, that was it, it works great. Thanks you so much!

Funny, the space before "step" isn't in my code. Strange.

This forum is the best I have found, I have found 95% of my answers here. Thank you all!

Tom

Incorrect format in formula

Participate now!

Share