Invalid use of a property-error

[(T_T)]

Hi,

I have this that should take the column and check if each value are greater than mx, if so then subtract bl from them and return them in new column. Then check them again and if Value is smaller than bl then change that value to bl and return in next column.

but it doesn't work and I keep getting "Invalid use of a property" error.

p.s. str supposted to count how many values are greater than mx.

Private Sub CommandButton1_Click()
 
 Dim bl As Long
 Dim mx As Long
 Dim pw As Range
 Dim LastColumn As Long
 Dim str As Long
 Dim pkSh As Long
 

LastColumn = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
 

 str = 0
 
 mx = TextBox_maxP * TextBox_PeakS / 100
 bl = TextBox_maxP * TextBox_baseL / 100
 
Set pw = UsedRange.Find("Power", , , 1, 1).Columns
  
 With Range(pw, pw.End(xlDown))
    If .Value >= mx Then .Value -bl
        str = str + 1
    ElseIf .Value < bl Then .Value = bl
    End If
End With
    
 Next
 .Range("peak shaving" & LastColumn + 1) = pkSh
 .Range("battery" & LastColumn + 2) = .Value
 Range("K1").Value = str

End Sub

[mohadin]

Hi
Try

mx = TextBox_maxP.Value * TextBox_PeakS.Value / 100
 bl = TextBox_maxP.Value * TextBox_baseL.Value / 100

[HaHoBe]

Hi (T_T),

you cannot compare the values of cells in a range in one go (except using Evaluate) but should loop through the cells instead. And I doubt the next error would occur as rows 4 and 5 are text.
Later on in the code you refer to a with statement which is not provided as well as no information about what ranges "peak shaving" and "battery" refer to and what pkSh is.

Maybe you should give this code a try:

Private Sub CommandButton1_Click()
 
Dim bl As Long
Dim mx As Long
Dim pw As Range
Dim LastColumn As Long
Dim str As Long
Dim pkSh As Long
Dim rCell As Range
Dim oCTRL As Object

LastColumn = Sheets("Tabelle1").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1

str = 0

mx = TextBox_maxP * TextBox_PeakS / 100
bl = TextBox_maxP * TextBox_baseL / 100

Set pw = Sheets("Tabelle1").UsedRange.Find("Power", , , 1, 1)
Set pw = Range(pw, pw.End(xlDown))
Set pw = pw.Offset(2).Resize(pw.Rows.Count - 2, pw.Columns.Count)

For Each rCell In Range(pw, pw.End(xlDown))
  With rCell
    If .Value >= mx Then
      .Value = -bl
      str = str + 1
    ElseIf .Value < bl Then
      .Value = bl
    End If
  End With
Next rCell
'/// No named ranges for peak Shaving nor battery
'/// could be replaced by Column letters
'/// no With set for the ranges so either omitt the dot or refer to a range
'/// what is pkSH, which range do you rely on as you want to fill in a value?
' .Range("peak shaving" & LastColumn + 1) = pkSh
' .Range("battery" & LastColumn + 2) = .Value

Sheets("Tabelle1").Range("K1").Value = str

For Each oCTRL In Me.Controls
  If TypeName(oCTRL) = "Textbox" Then
    oCTRL.Value = vbNullString
  End If
Next oCTRL

Set pw = Nothing
End Sub

Please be informed that the last For...Next loop clears the contents of the userform if you want further entries to be made (or simply use Unload.Me for just one entry).

Ciao,
Holger

[(T_T)]

Hi (T_T),

you cannot compare the values of cells in a range in one go (except using Evaluate) but should loop through the cells instead. And I doubt the next error would occur as rows 4 and 5 are text.
Later on in the code you refer to a with statement which is not provided as well as no information about what ranges "peak shaving" and "battery" refer to and what pkSh is.

Maybe you should give this code a try:

Private Sub CommandButton1_Click()
 
Dim bl As Long
Dim mx As Long
Dim pw As Range
Dim LastColumn As Long
Dim str As Long
Dim pkSh As Long
Dim rCell As Range
Dim oCTRL As Object

LastColumn = Sheets("Tabelle1").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1

str = 0

mx = TextBox_maxP * TextBox_PeakS / 100
bl = TextBox_maxP * TextBox_baseL / 100

Set pw = Sheets("Tabelle1").UsedRange.Find("Power", , , 1, 1)
Set pw = Range(pw, pw.End(xlDown))
Set pw = pw.Offset(2).Resize(pw.Rows.Count - 2, pw.Columns.Count)

For Each rCell In Range(pw, pw.End(xlDown))
  With rCell
    If .Value >= mx Then
      .Value = -bl
      str = str + 1
    ElseIf .Value < bl Then
      .Value = bl
    End If
  End With
Next rCell
'/// No named ranges for peak Shaving nor battery
'/// could be replaced by Column letters
'/// no With set for the ranges so either omitt the dot or refer to a range
'/// what is pkSH, which range do you rely on as you want to fill in a value?
' .Range("peak shaving" & LastColumn + 1) = pkSh
' .Range("battery" & LastColumn + 2) = .Value

Sheets("Tabelle1").Range("K1").Value = str

For Each oCTRL In Me.Controls
  If TypeName(oCTRL) = "Textbox" Then
    oCTRL.Value = vbNullString
  End If
Next oCTRL

Set pw = Nothing
End Sub

Please be informed that the last For...Next loop clears the contents of the userform if you want further entries to be made (or simply use Unload.Me for just one entry).

Ciao,
Holger

Thanks
What does this part do?

Set pw = pw.Offset(2).Resize(pw.Rows.Count - 2, pw.Columns.Count)

[beyond Excel]

Hello.
Is the 'Maximum Power' value calculated as the largest value in column C or will you write it "by hand"?

[(T_T)]

Hello.
Is the 'Maximum Power' value calculated as the largest value in column C or will you write it "by hand"?

The user will give the max.

[HaHoBe]

Hi (T_T),

Set pw = pw.Offset(2).Resize(pw.Rows.Count - 2, pw.Columns.Count)

Set the range down 2 rows and resize the range to be 2 rows short of the original range (exclude the headers) and match the number of columns.

BTW the first error was raised by

 With Range(pw, pw.End(xlDown))
    If .Value >= mx Then .Value -bl
        str = str + 1
    ElseIf .Value < bl Then .Value = bl
    End If
End With

as an = was missing. Adding this will lead to a new error as your If-clauses are different from what the compiler expects.

Ciao,
Holger