Code to divide whole column by a number doesn't work

[LeanneBG]

Hi Experts - I have below code as part of the workbook i am creating.. everything is working fine, except that when i am trying to divide the column by 1000000, it always turn zero after the macro is finished running. But when i run the code line by line (pressing F8), it is working properly. But when the button is clicked, it is not working fine..Can anyone help and point out what i am doing wrong? I've highlighted the code below which does the dividing step.. Thanks!

Private Function FormatOutputWorksheet()
    Application.StatusBar = "Formatting Output Worksheet ... Please Wait"
    wsRaw.Cells.Delete
    wsOutput.Cells.Delete
    
    wsActualVsBudget.UsedRange.Copy wsRaw.Range("A1")
    wbRawDataWorkbook.Close SaveChanges:=False
    
    With wsOutput
        Application.StatusBar = "Copying Data From Raw Worksheet Into Output Worksheet ... Please Wait"
        wsRaw.UsedRange.Copy .Range("A1")
        Application.StatusBar = "Copying Data From Raw Worksheet Into Output Worksheet ... Done"
        
        Application.StatusBar = "Inserting Columns In Output Worksheet ... Please Wait"
        lngOutputLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Columns.AutoFit
        .Columns("N:Q").Insert
        
      
        .Range("N1") = "$ MM"
        .Range("O1") = "Function"
        .Range("P1") = "Region"
        .Range("Q1") = "Business"
     
        Application.StatusBar = "Inserting Columns In Output Worksheet ... Done"
        
        .Range("BZ1").Value = 1
        .Range("A2", .Range("A2").End(xlDown)).Value = .Range("A2", .Range("A2").End(xlDown)).Value
        
        .Range("BZ1").Copy
        .Range("A2", .Range("A2").End(xlDown)).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
        wsMapping.Range("C2", wsMapping.Range("C2").End(xlDown)).Value = _
            wsMapping.Range("C2", wsMapping.Range("C2").End(xlDown)).Value
        .Range("BZ1").ClearContents
        
         Application.StatusBar = "Dividing data ... Please Wait"
        
        'Here we are putting value 1000000 in the range AZ1 for divding
        
[B]        .Range("AZ1").Value = 1000000[/B]
[B]        .Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row).Copy Destination:=Range("N2")[/B]
[B]        .Range("N2", .Range("N2").End(xlDown)).Value = .Range("N2", .Range("N2").End(xlDown)).Value[/B]

[B]        .Range("AZ1").Copy[/B]
[B]        .Range("N2", .Range("N2").End(xlDown)).PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, _[/B]
[B]            SkipBlanks:=False, Transpose:=False[/B]
[B]        Application.CutCopyMode = False[/B]
[B]        .Range("AZ1").ClearContents[/B]
        
  
        
         Application.StatusBar = "Dividing data ... Done"
        
        'Region
        Application.StatusBar = "Index-Matching Region In Output Worksheet ... Please Wait"
        With .Cells(2, 16).Resize(lngOutputLastRow)
            .Formula = "=INDEX('" & strMappingSheet & "'!A:A, MATCH(A2, '" & strMappingSheet & "'!$C:$C, 0), 1)"
            .Calculate
            .Value = .Value
            
            wsOutput.Range("P1:P" & lngOutputLastRow).AutoFilter Field:=1, Criteria1:="0"
            If (wsOutput.Range("P1:P" & lngOutputLastRow).SpecialCells(xlCellTypeVisible).Cells.Count - 1) > 0 Then _
                wsOutput.Range("P1:P" & lngOutputLastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Value = "Unidentified"
            .AutoFilter
        End With
        Application.StatusBar = "Index-Matching Region In Output Worksheet ... Done"
        
        'Function
        Application.StatusBar = "Index-Matching function In Output Worksheet ... Please Wait"
        With .Cells(2, 15).Resize(lngOutputLastRow)
            .Formula = "=INDEX('" & strMappingSheet & "'!O:O, MATCH(A2, '" & strMappingSheet & "'!$C:$C, 0), 1)"
            .Calculate
            .Value = .Value
            
            wsOutput.Range("O1:O" & lngOutputLastRow).AutoFilter Field:=1, Criteria1:="0"
            If (wsOutput.Range("O1:O" & lngOutputLastRow).SpecialCells(xlCellTypeVisible).Cells.Count - 1) > 0 Then _
                wsOutput.Range("O1:O" & lngOutputLastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Value = "Unidentified"
            .AutoFilter
        End With
        Application.StatusBar = "Index-Matching function In Output Worksheet ... Done"
        
        'Business
        Application.StatusBar = "Index-Matching Business In Output Worksheet ... Please Wait"
        With .Cells(2, 17).Resize(lngOutputLastRow)
            .Formula = "=INDEX('" & strMappingSheet & "'!B:B, MATCH(A2, '" & strMappingSheet & "'!$C:$C, 0), 1)"
            .Calculate
            .Value = .Value
            
            wsOutput.Range("Q1:Q" & lngOutputLastRow).AutoFilter Field:=1, Criteria1:="0"
            If (wsOutput.Range("Q1:Q" & lngOutputLastRow).SpecialCells(xlCellTypeVisible).Cells.Count - 1) > 0 Then _
                wsOutput.Range("Q1:Q" & lngOutputLastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Value = "Unidentified"
            .AutoFilter
        End With
        Application.StatusBar = "Index-Matching Business In Output Worksheet ... Done"
      
        'Delete #N/As
        Application.StatusBar = "Deleting #N/As In Output Worksheet ... Please Wait"
        With .Range("Q1:Q" & lngOutputLastRow)
            .AutoFilter Field:=1, Criteria1:="#N/A"
            If .SpecialCells(xlCellTypeVisible).Rows.Count > 0 Then _
                .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            .AutoFilter
        End With
        Application.StatusBar = "Deleting #N/As In Output Worksheet ... Done"
        
     
       
        
    End With
    Application.StatusBar = "Formatting Output Worksheet ... Done"
End Function

 

[Takae]

You need to put a "." in front of "cells(.......".

.Range("D2:D" & .cells(Rows.count, "D").End(xlUp).Row).copy