Macro correction

[zyxw1234]

Code: Select all

Sub STEP7()
Dim Wbap As Workbook
 Set Wbap = Workbooks.Open("C:UsersWolfieeeStyleDesktopap.xls")
Dim Wsap As Worksheet
 Set Wsap = Wbap.Worksheets.Item(1)
Dim Lrap As Long: Let Lrap = Wsap.Range("B" & Wsap.Rows.Count & "").End(xlUp).Row
Dim Arrap As Variant: Let Arrap = Wsap.Range("A1:Y" & Lrap & "").Value2
Dim arrH2pc() As Variant, arrI2pc() As Variant
 Let arrH2pc() = Evaluate("=2/100*H2:H" & Lrap & "")
 Let arrI2pc() = Evaluate("=2/100*I2:I" & Lrap & "")

Rem 2
Dim arrS() As Variant: Let arrS() = Wsap.Range("S1:S" & Lrap & "").Value2
Dim arrU() As Variant: Let arrU() = Wsap.Range("U1:U" & Lrap & "").Value2
Dim Cnt As Long
    For Cnt = 2 To Lrap
        If arrS(Cnt, 1) >= 0 Then
        Dim BgstHI As Double
        Let BgstHI = arrH2pc(Cnt - 1, 1)   '                                                                                                                       Cnt - 1  is  because our arrays for the H and I columns start at row 2 , so the indices will be one less than the roe to which they apply . I chose to do this to avoid trying to get 2% of the header , as that would error
            If arrH2pc(Cnt - 1, 1) < arrI2pc(Cnt - 1, 1) Then Let BgstHI = arrI2pc(Cnt - 1, 1) '  If I column is largest, use that, otherwise H will be taken   NOTE: H will be taken if the H and I columnns are equal
            If arrS(Cnt, 1) < BgstHI Then Let arrU(Cnt, 1) = -1
        Else ' S < 0
        '  column S should be positive, so don’t considere the no. which are negative
        End If
    Next Cnt
    
Rem 3 paste out
 Let Wsap.Range("S1:S" & Lrap & "").Value2 = arrU()
 Wbap.Save
 Wbap.Close
 
End Sub

I am getting error with this macro plz see the sample pic
& when i run the macro it paste the result as -1 & I need, it should paste the result as A only


https://drive.google.com/file/d/1Kp3re5 ... sp=sharing
https://drive.google.com/file/d/1QEVLAC ... sp=sharing

[HansV]

Since you haven't made the workbook available, I have no idea.

[zyxw1234]

https://drive.google.com/file/d/1UKO8x9 ... sp=sharing

Plz download the file HansV Sir

[Leif]

https://drive.google.com/file/d/1UKO8x9 ... sp=sharing

Plz download the file HansV Sir

Can you upload the file to this forum please, along with any graphics (such as you have linked to in your first post).

It is likely that the links will disappear at some point in the future and this whole thread would then become meaningless.

[HansV]

If row 2 is the only row with data, Evaluate("=2/100*H2:H" & Lrap & "") is not an array but a single value, hence the type mismatch.

In your "real" workbook, will it ever occur that row 2 is the only row with data?

[zyxw1234]

No all it depends , it can be more also , there can be more data also
I am unable to upload the file in the forum i dont know why so i shared the link

[HansV]

See Tip: adding an attachment for instructions on how to attach a file to a post.

[HansV]

Does this work better?

Code: Select all

Sub STEP7()
    Dim Wbap As Workbook
    Set Wbap = Workbooks.Open("C:UsersWolfieeeStyleDesktopap.xls")
    Dim Wsap As Worksheet
    Set Wsap = Wbap.Worksheets.Item(1)
    Dim Lrap As Long: Let Lrap = Wsap.Range("B" & Wsap.Rows.Count & "").End(xlUp).Row
    Dim Arrap As Variant: Let Arrap = Wsap.Range("A1:Y" & Lrap & "").Value2
    Dim arrH2pc As Variant, arrI2pc As Variant
    Let arrH2pc = Evaluate("=2/100*H1:H" & Lrap & "")
    Let arrI2pc = Evaluate("=2/100*I1:I" & Lrap & "")

    Rem 2
    Dim arrS() As Variant: Let arrS() = Wsap.Range("S1:S" & Lrap & "").Value2
    Dim arrU() As Variant: Let arrU() = Wsap.Range("U1:U" & Lrap & "").Value2
    Dim Cnt As Long
    For Cnt = 2 To Lrap
        If arrS(Cnt, 1) >= 0 Then
        Dim BgstHI As Double
        Let BgstHI = arrH2pc(Cnt, 1)   '                                                                                                                       Cnt - 1  is  because our arrays for the H and I columns start at row 2 , so the indices will be one less than the roe to which they apply . I chose to do this to avoid trying to get 2% of the header , as that would error
            If arrH2pc(Cnt, 1) < arrI2pc(Cnt, 1) Then Let BgstHI = arrI2pc(Cnt, 1) '  If I column is largest, use that, otherwise H will be taken   NOTE: H will be taken if the H and I columnns are equal
            If arrS(Cnt, 1) < BgstHI Then Let arrU(Cnt, 1) = -1
        Else ' S < 0
        '  column S should be positive, so don’t considere the no. which are negative
        End If
    Next Cnt

    Rem 3 paste out
    Let Wsap.Range("S1:S" & Lrap & "").Value2 = arrU()
    Wbap.Save
    Wbap.Close
End Sub

[zyxw1234]

Thnx Alot HansV Sir for ur Great Help

Code: Select all

If arrS(Cnt, 1) < BgstHI Then Let arrU(Cnt, 1) = "A"

I changed this line bcoz i need result as A & i ran the code , the code is doing perfect job & i let u know about the changes that i made, so plz let me know this will not affect anything correct?

[HansV]

That should work.

[zyxw1234]

Thnx Alot HansV Sir for ur Great help