CODEWITHSUNDEEP
vbaexcel
Basho
Basho

Reputation: 47

.End(xlUp).row goes way too far

Thanks to you i found a command so it runs different command on my table sadly when I launch it it goes to row 200+ everytime while in this case my table stops at 34 rows

Sub test0()
Dim ws As Worksheet
Set ws = Sheets("Detail")

Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Range("F:I").NumberFormat = "General"
Dim arr
arr = Array("MFR", "CUSTLINE#", "PRICE (DYP)", "DELIVERY")
ws.Range("F1:I1").Value = arr
Dim ty
ty = Array("=IF(H2=""NB"","""",AY2)", "=A2", "=IF(P2="""",""NB"",P2)", "=IF(BR2>(D2+AM2),""STOCK"",IF(AR2=""0 Weeks"","""",SUBSTITUTE(AR2,"" Weeks"","" WKS"")))")
ws.Range("F2:I2" & lr).Formula = ty
End Sub

This is the code i use to make it work i also tried this one just to see if I don't know data was not visible or anything :

Sub LastRowWithData_xlUp_1()

Dim lastRow As Long
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

MsgBox lr
End Sub

But it gives me the right row number.

Same when i try with this :

Sub test1()
Dim ws As Worksheet
Set ws = Sheets("Detail")

Dim lr As Long
lr = InputBox("Last Row")
Range("F:I").NumberFormat = "General"
Dim arr
arr = Array("MFR", "CUSTLINE#", "PRICE (DYP)", "DELIVERY")
ws.Range("F1:I1").Value = arr
Dim ty
ty = Array("=IF(H2=""NB"","""",AY2)", "=A2", "=IF(P2="""",""NB"",P2)", "=IF(BR2>(D2+AM2),""STOCK"",IF(AR2=""0 Weeks"","""",SUBSTITUTE(AR2,"" Weeks"","" WKS"")))")
ws.Range("F2:I2" & lr).Formula = ty
End Sub

If someone has an idea I would be grateful I don't really know what I missed there Thanks in advance!

Upvotes: 0

Views: 702

Answers (2)

user4039065
user4039065

Reputation:

It seems you require the maximum populated row number from any column mentioned as principle criteria in your formula(s).

Additionally, if lr was 99, then "F2:I2" & lr becomes F2:I299.

Single use vars are rarely expedient or efficient unless they enhance code legibility. arr did none of these things but I would say ty is justified in the latter.

Sub test1()

    Dim lr As Long, ty  AS VARIANT

    with workSheets("Detail")

        lr = application.max(.cells(.rows.count, "H").end(xlup).row, _
                             .cells(.rows.count, "P").end(xlup).row, _
                             .cells(.rows.count, "AR").end(xlup).row, _
                             .cells(.rows.count, "BR").end(xlup).row)

        .Range("F:I").NumberFormat = "General"

        .Range("F1:I1").Value = Array("MFR", "CUSTLINE#", "PRICE (DYP)", "DELIVERY")

        ty = Array("=IF(H2=""NB"", text(,), AY2)", "=A2", "=IF(P2= text(,), ""NB"", P2)", _
                   "=IF(BR2>(D2+AM2), ""STOCK"", " & _
                   "IF(AR2=""0 Weeks"", text(,), SUBSTITUTE(AR2, "" Weeks"", "" WKS"")))")

         .Range("F2:I" & lr).Formula = ty

    end with

End Sub

Upvotes: 1

Basho
Basho

Reputation: 47

The problem was the range of ty that was F2:I2 while it should have been F2:I 

Sub test0()
Dim ws As Worksheet
Set ws = Sheets("Detail")

Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Range("F:I").NumberFormat = "General"
Dim arr
arr = Array("MFR", "CUSTLINE#", "PRICE (DYP)", "DELIVERY")
ws.Range("F1:I1").Value = arr
Dim ty
ty = Array("=IF(H2=""NB"","""",AY2)", "=A2", "=IF(P2="""",""NB"",P2)", "=IF(BR2>(D2+AM2),""STOCK"",IF(AR2=""0 Weeks"","""",SUBSTITUTE(AR2,"" Weeks"","" WKS"")))")
ws.Range("F2:I2" & lr).Formula = ty
End Sub

Upvotes: 1

Related Questions