Integer to string in MsgBox message

[wasntme]

Hello,

I have a macro, activated by button click, that moves the entire row to another sheet ("Archive") if cell value in column F is "Finalised". Macro works fine.

I want to add message after it finishes with information how many rows have been moved. The problem i am encountering is that it always show 0. Hoped CStr() will fix it, but no success.

 Sub Update() 'moving finalised projects to archive

Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long, status As Integer


Application.EnableEvents = False
Application.ScreenUpdating = False


status = Application.WorksheetFunction.CountIf(Range("F3:F100"), "Finalised") 'count finalised projects moved to archive
lastrow = Worksheets("VAT registrations").UsedRange.Rows.Count


For r = lastrow To 2 Step -1
    If Worksheets("VAT registrations").Range("F" & r).Value = "Finalised" Then
       Worksheets("VAT registrations").Rows(r).Cut Destination:=Worksheets("Archive").Range("A" & Rows.Count).End(xlUp)(2)
       Worksheets("VAT registrations").Rows(r).Delete
    End If
Next r


Application.EnableEvents = True
Application.ScreenUpdating = True


MsgBox "File updated, projects moved to archive: " & CStr(status)
End Sub

Thanks in advance.

 

[Fluff]

You don't need the CStr, but which sheet is active when you run the code?

 

[Joe4]

Why not just add a counter right inside your loop, so you are actually counting how many rows you are moving as you move them?

Sub Update() 'moving finalised projects to archive

Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long, status As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

lastrow = Worksheets("VAT registrations").UsedRange.Rows.Count

For r = lastrow To 2 Step -1
    If Worksheets("VAT registrations").Range("F" & r).Value = "Finalised" Then
       Worksheets("VAT registrations").Rows(r).Cut Destination:=Worksheets("Archive").Range("A" & Rows.Count).End(xlUp)(2)
       Worksheets("VAT registrations").Rows(r).Delete
       status = status + 1
    End If
Next r

Application.EnableEvents = True
Application.ScreenUpdating = True

MsgBox "File updated, projects moved to archive: " & status

End Sub

 

[Marcelo Branco]

Maybe...(NOT TESTED)

Try changing this

status = Application.WorksheetFunction.CountIf(Range("F3:F100"), "Finalised") 'count finalised projects moved to archive
lastrow = Worksheets("VAT registrations").UsedRange.Rows.Count

to

With Worksheets("VAT registrations")
    lastrow = [B][COLOR=#ff0000].[/COLOR][/B]UsedRange.Rows.Count
    status = Application.WorksheetFunction.CountIf([SIZE=3][COLOR=#ff0000][B].[/B][/COLOR][/SIZE]Range("F[B][COLOR=#ff0000]2[/COLOR][/B]:F" [COLOR=#ff0000]& lastrow[/COLOR]), "Finalised") 'count finalised projects moved to archive
End With

Hope this helps

M.

 

[wasntme]

Joe4, you are a superstar!

Fixed 2 of my problems in just few minutes!

Many thanks!

 

[Joe4]

You are welcome.

As Marcelo pointed out, note that in your previous code, your formula was working on a hard-coded range ending in row 100, but your loop was calculating the last row.
So the ranges may not have been the same too.

 

[wasntme]

You are welcome.

As Marcelo pointed out, note that in your previous code, your formula was working on a hard-coded range ending in row 100, but your loop was calculating the last row.
So the ranges may not have been the same too.

I have used this range to avoid any slowing down (the sheet will never have so much entries, 50 tops), but i learn with every thread here . Once again many thanks!