vba. If Cell = Range("A1") do not work.

[montecarlo2012]

Hi All.
Working on:

Sub sheet3_val()

Set rngData = Sheet1.Range("B2", "B2902")
m = -1
For Each cell In rngData
    If cell = 25 Then
        Sheet3.Range("D2").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
Range("B9").Formula = "=COUNT(C2:WAK2)"
Range("C9").Formula = "=max(C2:WAK2)"
Range("D9").Formula = "=COUNTIF(C2:WAK2, 0)"
    
Set rngData = Sheet1.Range("C2", "C2902")
m = -1
For Each cell In rngData
    If cell = 25 Then
        Sheet3.Range("D3").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
Range("B10").Formula = "=COUNT(C3:WAK3)"
Range("C10").Formula = "=max(C3:WAK3)"
Range("D10").Formula = "=COUNTIF(C3:WAK3, 0)"

Set rngData = Sheet1.Range("D2", "D2902")
m = -1
For Each cell In rngData
    If cell = 25 Then
        Sheet3.Range("D4").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
Range("B11").Formula = "=COUNT(C4:WAK4)"
Range("C11").Formula = "=max(C4:WAK4)"
Range("D11").Formula = "=COUNTIF(C4:WAK4, 0)"

Set rngData = Sheet1.Range("E2", "E2902")
m = -1
For Each cell In rngData
    If cell = 25 Then
        Sheet3.Range("D5").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
Range("B12").Formula = "=COUNT(C5:WAK5)"
Range("C12").Formula = "=max(C5:WAK5)"
Range("D12").Formula = "=COUNTIF(C5:WAK5, 0)"

Set rngData = Sheet1.Range("F2", "F2902")
m = -1
For Each cell In rngData
    If cell = 25 Then
        Sheet3.Range("D6").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
Range("B13").Formula = "=COUNT(C6:WAK6)"
Range("C13").Formula = "=max(C6:WAK6)"
Range("D13").Formula = "=COUNTIF(C6:WAK6, 0)"


Set rngData = Sheet1.Range("G2", "G2902")
m = -1
For Each cell In rngData
    If cell = 25 Then
        Sheet3.Range("D7").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
Range("B14").Formula = "=COUNT(C7:WAK7)"
Range("C14").Formula = "=max(C7:WAK7)"
Range("D14").Formula = "=COUNTIF(C7:WAK7, 0)"

Range("B15").Formula = "=sum(B9:B13)"
End Sub

I am looking to shorter the code, and manage the condition from cell A1
Like if cell = range(“A1”).value then.

I tried but do not work.

Do you think it is possible to resolved, without Scripting.Dictionary Please.
On sheet 1 I have a dynamic array B2:G.
looks like this:

Thank you for reading this

I am eager to receive your feedback Please.

 

[jasonb75]

if cell = range(“A1”).value then

Try using .Value on cell as well

If cell.Value = Range("A1").Value Then

 

[montecarlo2012]

Thank you jasonb75 for your time, but do not work out.
So, I will be open to more suggestions

 

[Marc L]

Hi,​

​

so your logic fails … If at least you attach your worksheet and well elaborate what you are trying to achieve …​

 

[Michael M]

What is the initial value of "n"

Sheet3.Range("D2").Offset(0, m) = n

 

[Michael M]

You also only need the formulas once

Range("B9:B14").Formula = "=COUNT(C2:WAK2)"
Range("C9:C14").Formula = "=max(C2:WAK2)"
Range("D9:D14").Formula = "=COUNTIF(C2:WAK2, 0)"

 

[montecarlo2012]

Hi, Michael thanks and nice to see you.
n take the first value from the condition.
and now I changed the condition for
If cell = Cells(1, 1) and work only 4 times instead of 6.
again Michael
Thanks for reading my post.

 

[Michael M]

This might be better, but I still don't see the initial value of "n"
When you step through the code for the first time, what is "n" in this line
Sheet3.Cells(2, c + 1).Offset(0, m) = n

Sub sheet3_val()
Dim c As Integer
For c = 2 To 7
Set rngData = Sheet1.Range(Cells(2, c), Cells(2902, c))
m = -1
For Each cell In rngData
    If cell = 25 Then
        Sheet3.Cells(2, c + 1).Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next cell
Range("B9:B14").Formula = "=COUNT(C2:WAK2)"
Range("C9:C14").Formula = "=max(C2:WAK2)"
Range("D9:D14").Formula = "=COUNTIF(C2:WAK2, 0)"
Next c
Range("B15").Formula = "=sum(B9:B13)"
End Sub

 

[montecarlo2012]

Michael M thanks,

This might be better, but I still don't see the initial value of "n"

on the first code

If cell = 25 Then

first value for ► " n " ◄
and when I run your code I got:

on

Thanks .

 

[Michael M]

If it's going to be the activesheet change the line to

Set rngData = Range(Cells(2, c), Cells(2902, c))