2

Good evening,

I'm struggling with my code. Main problem is not calculating formula automatically. Formula is inserted by VBA (Left function) I've tried multiple solutions, but nothing works:

  • automatically calculated formulas are switched on
  • I've tried ActiveCell.NumberFormat = "General" - doesnt work
  • I've tried Application.Volatile - doesnt work
  • Recording text to columns and assign cells to "general" (works, but not when Im putting code into my script)

Could you please help me where lies the problem?

Sub QualityCheck()


'załóż filtry, wyszukaj nagłowka "vendor" i posortuj

    Rows("1:1").Select
    Range("F1").Activate
    Selection.AutoFilter
    Selection.Find(What:="Vendor", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Worksheets(1).AutoFilter.Sort.SortFields.Clear
    Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range _
        ("K1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Worksheets(1).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'statystyki

    'znajdz wszystkie te same numery vendora i ostatni aktywny ROW
    i = 2
    r = 2
    koniec = Range("K" & Rows.Count).End(xlUp).Row

    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Do Until Range("K" & r) < koniec

            Do Until Range("K" & i) <> Range("K" & i + 1)

                    Range("N" & i).Select
                    Formula = "=left(M" & i & ";1)"
                    Range("N" & i).Formula = Formula  'OFFSET DODAC ALBO COS

                    ActiveCell.NumberFormat = "General"

                i = i + 1 'i to bedzie nasz ostatni row
            Loop

        r = r + 1
    Loop

    Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True


End Sub
Kris
  • 75
  • 8

1 Answers1

4

Try changing

Range("N" & i).Select
Formula = "=left(M" & i & ";1)"
Range("N" & i).Formula = Formula

To

Range("N" & i).Formula = "=left(M" & i & ",1)"

I suspect that the problem is that you've used ; instead of , in separating the parameters of the left function.

It's worth noting that you're going in a round about way of setting the formula by setting it to a variant variable and then setting it to the formula, that isn't declared by the way. Adding Option Explicit to the top helps catch a lot of errors like misspelled variable names and never really hurts.

On another note, it's never a bad idea to learn how to avoid select as it poses its own problems. Please see How to avoid using Select in VBA

Jchang43
  • 891
  • 6
  • 14