Excel error 1004 “Unable to get … property of WorksheetFunction class”

[amo]

Please Help Me

Excel error 1004 “Unable to get … property of WorksheetFunction class”


This My Code

Sub variasi()
Dim i, j
Dim sData As Worksheet: Set sData = Sheets("Data Scrape")

Application.Calculation = xlCalculationManual
With sData
    For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
        SKU = Sheets("Data Edit").Range("F18") & WorksheetFunction.Rept("0", 3 - Len(i - 1)) & i - 1
        produk = .Range("A" & i)
        harga = .Range("B" & i)
        berat = .Range("C" & i)
        deskripsi = .Range("D" & i)
        gbr1 = .Range("E" & i)
        gbr2 = .Range("F" & i)
        gbr3 = .Range("G" & i)
        gbr4 = .Range("H" & i)
        gbr5 = .Range("I" & i)
        AssSKU = SKU & "-" & .Range("J" & i)
        For j = 10 To 100
            n = j Mod 3
            If n = 1 Then
                Var = .Cells(i, j)
                If Len(Var) = 0 And j = 10 Then
                    x = Range("A" & Rows.Count).End(xlUp).Row + 1
                    Cells(x, 1) = i - 1
                    Cells(x, 2) = product
                    Cells(x, 3) = price
                    Cells(x, 4) = brt
                    Cells(x, 5) = desk
                    Cells(x, 6) = gbr1
                    Cells(x, 7) = gbr2
                    Cells(x, 8) = gbr3
                    Cells(x, 9) = gbr4
                    Cells(x, 10) = gbr5
                    Cells(x, 11) = SKU & "-" & "Not Specified"
                    Cells(x, 12) = SKU & "-" & "Not Specified"
                    Cells(x, 13) = "Not Specified"
                    Cells(x, 14) = price
                    Cells(x, 15) = 1000
                    Range("A5:O10000").WrapText = False
                ElseIf Len(Var) Then
                    x = Range("A" & Rows.Count).End(xlUp).Row + 1
                    Cells(x, 1) = i - 1
                    Cells(x, 2) = product
                    Cells(x, 3) = price
                    Cells(x, 4) = brt
                    Cells(x, 5) = desk
                    Cells(x, 6) = gbr1
                    Cells(x, 7) = gbr2
                    Cells(x, 8) = gbr3
                    Cells(x, 9) = gbr4
                    Cells(x, 10) = gbr5
                    Cells(x, 11) = AssSKU
                    Cells(x, 12) = SKU & "-" & Var
                    Cells(x, 13) = .Cells(i, j)
                    Cells(x, 14) = .Cells(i, j + 1)
                    Cells(x, 15) = .Cells(i, j + 2)
                    Range("A5:O10000").WrapText = False
                End If
            End If
        Next j
    Next i
End With
Application.Calculation = xlCalculationAutomatic
End Sub

 

[mikerickson]

What is the value of i when that errors? if i >= 1001, then 3- Len(i-1) is < 0 and the function will error.

Try using Format(i-1,"000") instead of the Rept formulation

 

[amo]

@mikerickson

can write the code that I should use

 

[amo]

thank you @mikerickson
I have tried and succeeded

 

[DanteAmor]

I made some adjustments to reduce your code.

I recommend you use the statement: Option Explicit, to declare all the variables. (At the beginning of all the code.)
For example, you have these variables:
Produk, harga, berat, deskripsi

but you use them with another name:
Product, Price, brt, desk

Either way, the reduced code would look like this:

Option Explicit

Sub variasi()
  Dim i As Long, j As Long, k As Long, x As Long
  Dim SKU As String, var As Variant
  
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
  With Sheets("Data Scrape")
    For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
      SKU = Sheets("Data Edit").Range("F18") & Format(i - 1, "00000")
      For j = 10 To 100 Step 3
        var = .Cells(i, j)
        x = Range("A" & Rows.Count).End(xlUp).Row + 1
        Cells(x, 1) = i - 1
        For k = 2 To 10
          Cells(x, k) = .Cells(i, k - 1)
        Next
        If Len(var) = 0 And j = 10 Then
          Cells(x, 11) = SKU & "-" & "Not Specified"
          Cells(x, 12) = SKU & "-" & "Not Specified"
          Cells(x, 13) = "Not Specified"
          Cells(x, 14) = .Range("B" & i)
          Cells(x, 15) = 1000
        ElseIf Len(var) Then
          Cells(x, 11) = SKU & "-" & .Range("J" & i)
          Cells(x, 12) = SKU & "-" & var
          Cells(x, 13) = .Cells(i, j)
          Cells(x, 14) = .Cells(i, j + 1)
          Cells(x, 15) = .Cells(i, j + 2)
        End If
      Next j
    Next i
  End With
  
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

 

[amo]

thank you @DanteAmor