Vba formula help

[Giggzz]

Sub AllGood6()
ActiveSheet.Unprotect ("as")
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim r As Range
Dim s As Range
If UserForm6.TextBox1 > 0 Then
    For Each r In Range("H26:H35")
    [B]For Each s In Range("A26:A35")
[/B]        If r.Value > 0 And s.Value > 0 Then
        r.Value = r.Value - ((r.Value * UserForm6.TextBox1.Value) / 100) / [B]s.Value
[/B]        
        Next r
        
  Unload UserForm6
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Protect ("as")
End Sub

Im trying to count the number of cells in A26:A35 > 0, taking that number and dividing it into the r.value.

 

[Derek Brown]

You did not say what the problem is, but you need both an "End If" and a "Next s" before "Next r".

 

[Giggzz]

Sub AllGood6()
ActiveSheet.Unprotect ("as")
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim r As Range
[B][U]Dim s As Range
[/U][/B]If UserForm6.TextBox1 > 0 Then
    For Each r In Range("H26:H35")
    [B][U]For Each s In Range("A26:A35")[/U]
[/B]        If r.Value > 0 [B][U]And s.Value > 0[/U][/B] Then
        r.Value = r.Value - ((r.Value * UserForm6.TextBox1.Value) / 100) / [B][U]s.Value
[/U][/B]        [B][U]Next s
[/U][/B]        Next r
        
  Unload UserForm6
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Protect ("as")
End Sub

Everything in bold I added thinking this was a easy change, wrong.

1. with s.value - Im trying to count all the cells in A25:A36 > 0

2. than take that number and divide it into the r.value

3. than have applied to each cell in H26:H35 > 0.

 

[Kenneth Hobson]

Worksheetfunction CountIf seems to work as I understand what you want.

Sub test()
  MsgBox CountGTZ(Range("A26:A35"))
End Sub

Function CountGTZ(r As Range) As Long
  CountGTZ = WorksheetFunction.CountIf(r, ">0")
End Function

 

[Trebor76]

Hi Giggzz,

Hard to test without creating the form, but let me know if incorporating the following into your existing code does the trick:

Dim rngCell As Range

For Each rngCell In Range("A26:A35")
    If rngCell.Value > 0 And rngCell.Offset(, 7).Value > 0 Then
        With rngCell.Offset(, 7)
            .Value = .Value - ((.Value * UserForm6.TextBox1.Value) / 100) / rngCell.Value
        End With
    End If
Next rngCell

HTH

Robert