Is it possible that if in excell button makro acts differently than if u run in vba dev.?

Question

I have some problems with vba. I have a button in excell, and if i run makro from excell it takes twice much time than if I run from VBA developer. If I run from VBA developer it sets my border and background color corectlly (I tried runing makro from button several times). if i ran the code from button excel it just formats(bg color, border) first 20 rows, but vba developer formats all of rows. Below is my code. Regards

Sheets("Master plan").Cells.Clear

lrm = 1
With Worksheets("Mater plan")
    For Each ws In Sheets(Array("1L", "5L", "20L"))
        LR = ws.Range("A" & Rows.Count).End(xlUp).Row
        .Range("D" & lrm).Resize(LR, 1) = ws.Range("C12:C" & LR).Value2
        .Range("A" & lrm).Resize(LR, 1) = ws.Range("A12:A" & LR).Value2
        .Range("B" & lrm).Resize(LR, 1) = ws.Range("L12:L" & LR).Value2
        .Range("C" & lrm).Resize(LR, 1) = ws.Range("L12:L" & LR).Value2
        lrm = lrm + Range("A1:A" & LR).Rows.Count
    Next

End With

I think problem starts from here...

Dim c As Range
Dim SrchRng

For Each ws In Sheets(Array("Master plan"))
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = lastRow To 1 Step -1
        If ws.Cells(i, 1).Text = "#N/V" Or ws.Cells(i, 1).Text = "stop" Then
            ws.Cells(i, 1).EntireRow.Delete
        End If
    Next i

Next

Dim LR3 As Long
LR3 = Range("B" & Rows.Count).End(xlUp).Row


Sheets("master plan").Range("A1:A" & LR3).Borders(xlEdgeRight).LineStyle = xlContinuous
Sheets("master plan").Range("B1:B" & LR3).Borders(xlEdgeRight).LineStyle = xlContinuous
Sheets("master plan").Range("C1:C" & LR3).Borders(xlEdgeRight).LineStyle = xlContinuous
Sheets("master plan").Range("D1:D" & LR3).Borders(xlEdgeRight).LineStyle = xlContinuous
Sheets("master plan").Range("B1:B" & LR3).NumberFormat = "ddd dd.mm"
Sheets("master plan").Range("C1:C" & LR3).NumberFormat = "ddd dd.mm"
Sheets("master plan").Range("A1:A" & LR3).Interior.Color = RGB(253, 233, 217)
Sheets("master plan").Range("B1:B" & LR3).Interior.Color = RGB(218, 238, 243)
Sheets("master plan").Range("C1:C" & LR3).Interior.Color = RGB(218, 238, 243)
Sheets("master plan").Range("D1:D" & LR3).Interior.Color = RGB(235, 241, 222)
Sheets("master plan").Columns("A:E").ColumnWidth = 15

Srry, it isnt that. I just translate to master plan. that's why i typo... — sandi, Aug 24 '15 at 15:32

score 0 · Answer 1 · answered Aug 24 '15 at 15:31

0

You have an unqualified range here:

lrm = lrm + Range("A1:A" & LR).Rows.Count

which sheet do you mean that to reference?

By default it will point to the ActiveSheet

answered Aug 24 '15 at 15:31

Tim Williams

154,628
8
97
125

i thought of master plan – sandi Aug 24 '15 at 15:35
You're missing the `.` before `Range()`, so it will not refer to the sheet referenced by the `With` block... There's also a similar line in your second code block. – Tim Williams Aug 24 '15 at 15:36

score 0 · Answer 2 · answered Aug 24 '15 at 15:54

The change is speed is probably due to the code actually doing different work.

You must ensure that your code references the sheet you intend it to. For instance the following line will refer to cells on the active sheet as this is what the Range object will do.

LR3 = Range("B" & Rows.Count).End(xlUp).Row

You need to change this.

Also the code is a bit difficult to read eg:

 lrm = lrm + Range("A1:A" & LR).Rows.Count

can be replaced with

 lrm = lrm + LR

Also this loop

For Each ws In Sheets(Array("Master plan"))

can be replaced with

Set ws = Sheets("Master plan")

!

score 0 · Answer 3 · answered Aug 24 '15 at 16:09

0

I found it. Thank you all for your proposals. it all helped me. But the proble was with

LR3 = Range("A" & Rows.Count).End(xlUp).row

I changed it with

LR3 = Sheets("Podatki plana").Range("A" & Rows.Count).End(xlUp).row

answered Aug 24 '15 at 16:09

sandi

23
6

Is it possible that if in excell button makro acts differently than if u run in vba dev.?

3 Answers3