VBA Code behind sheets not running correctly - very weird

[Sanzie99]

I'm working on the code behind some sheets, nothing to technical it pulls some data from a SQL Server 2005 DB then moves data and some calculations, but I'm having a really weird issue. If I run the code with a break or step into it the code runs perfectly fine, however if I simply run the code or run it as a macro it screws up horribly. I have gone through my code multiple times but can't see anything wrong with it. What would cause it, I have read in a couple of places that it is recommended to delete the QueryTable and Connection, but am unsure if this will work or what the code would be. Yes/No?

So yeah if anyone can help that would be great as it is extremely frustrating at the moment. Excel version is 2007.

Cheers,

Sanzie

 

[Michael M]

Hi Sanzie and Welcome to the Board.
I reckopn you may have to post the code for anyone to have any idea of what's wrong.
Use code tags to get the best result
square brackets with the word code between them at the beginning
AND
square brackets with fwd slash and the word code at the end

 

[Sanzie99]

To explain what it is for is very hard so please don't ask haha. But where GetMaxRows is, is where the first problem occurs as the following if statement does not run correctly. Which means the SummarySheet and Report sheets data is incorrect / not complete. But also when it copies this line back to the ForestStiffnessSummary Sheet it kinda of forgets the line it just found.

I hope that helps a little. Again what it is actually for is very hard to explain but I can if I need to.

Public Sub SummarizeMetriguard()

    Dim NumOfRows As Integer
    Dim GetMaxRows As Integer
    Dim cFind As Range
    Dim cFind1 As Range
    Dim cAllocate As String
    Dim cAllocate1 As String
    Dim bFound As Boolean
    Dim r As Long

    Sheets("DownloadSheet").Select
    
    sqlString = "SELECT datapcno, datauptus, dataucupt, datauptrdgs, dataavguptsig, dataavguptsn, dataskew, datamc, datamcmax, datasg, datarfscans, dataegpa, dataerdgs, datatempc, datawidthcm, datathickmm, datasg1, datasg2, datasg3,datadatfile, datasaptype, dataforestry FROM tbldata WHERE datareported='N' and datadatfile = (select min(datadatfile) from tbldata where datareported='N')"
    connString = "ODBC;DSN=REPORTSYSDB"
    With ActiveWorkbook.Sheets("DownloadSheet").QueryTables.Add(Connection:=connString, _
        Destination:=Range("A6"), Sql:=sqlString)
        .Refresh
    End With
    
    Sheets("DownloadSheet").Select
    
    GetMaxRows = ActiveWorkbook.Sheets("DownloadSheet").Range("A65536").End(xlUp).Row
    
    NumOfRows = GetMaxRows
    
    If NumOfRows > 6 Then
        Sheets("DownloadSheet").Range("W6:AM6").Copy
        Sheets("DownloadSheet").Range("W7:AM" & NumOfRows).PasteSpecial Paste:=xlPasteFormulas
        Application.CutCopyMode = False
    End If
    
    Sheets("DownloadSheet").Range("T7").Copy
    Sheets("SummarySheet").Select
    Sheets("SummarySheet").Range("A24").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Sheets("SummarySheet").Rows("24:24").Copy
    Sheets("Report").Select
    Sheets("Report").Range("A3").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Sheets("DownloadSheet").Select
    Set cFind = Sheets("DownloadSheet").Range("V7")
        cAllocate = cFind
    
    
    Sheets("DownloadSheet").Select
    Set cFind1 = Sheets("DownloadSheet").Range("U7")
        cAllocate1 = cFind1
    
    Sheets("ForestStiffnessSummary").Select
    
    r = 0
    While Not bFound And r < 65536
        r = r + 1
        If Sheets("ForestStiffnessSummary").Range("A" & r).Value = cAllocate Then
            bFound = True
    
    End If
    
    Wend
        bFound = False
    
    While Not bFound And r < 65536
        r = r + 1
        If Sheets("ForestStiffnessSummary").Range("A" & r).Value = cAllocate1 Then
            bFound = True
    
        End If
    
    Wend
        bFound = False
    
    While Not bFound And r < 65536
        r = r + 1
        If Sheets("ForestStiffnessSummary").Range("A" & r).Value = "" Then
            bFound = True
    
            Sheets("Report").Select
            Sheets("Report").Rows("3:3").Copy
        
            Sheets("ForestStiffnessSummary").Select
            Sheets("ForestStiffnessSummary").Range("A" & r).PasteSpecial Paste:=xlPasteValues
            ActiveCell.Offset(1).EntireRow.Insert
            Application.CutCopyMode = False
    
        End If
        
    Wend
    
    With ActiveWorkbook.Sheets("DownloadSheet")
        .Range("A6:V65536").ClearContents
        .Range("W7:AM65536").ClearContents
    End With
    
    With ActiveWorkbook.Sheets("SummarySheet")
        .Range("A24").ClearContents
    End With
    
    With ActiveWorkbook.Sheets("Report")
        .Range("A3:T3").ClearContents
    End With
    
    Sheets("ForestStiffnessSummary").Select

End Sub

 

[Michael M]

Sanzie
Are you sure the code is failing where you say ??
The GetMaxRows and following IF statement works fine, but....is that what you want it to do ??

IF the number of rows is greater than 6, then copy row 6 over everything else to the NumOfRows ???

Seems a bit pointless doing that, and maybe that IS the problem.

 

[Sanzie99]

Yeah that is correct because there is not data in W6:AM6, what it's doing is copying a calculation according to how many rows there are so that each row there is running the same calculation. However when I run it, it never seems to actually run that code. It's as if it skips the IF statement, however there is always more than 6 rows so it should always run.

 

[shg]

Does this help?

.Refresh [COLOR=red]BackgroundQuery:=False[/COLOR]

 

[Sanzie99]

Thanks shg4421, that seems to have worked ( for now ) with this working I can carry on and hopefully everything else will still continue to work.