VBA Loop - If Cell is not empty then....

[root01]

Hi,

I'm relatively new to VBA and would appreciate it if someone could give me a hand with a loop please.

I'm exporting some data from Qlikview into excel to create a report.

The order of things go like this:

1. Copy table from Qlik
2. Count no of rows in table and put it into a variable
3. Insert a no of rows into excel (depends on number stored in the variable above)
4. Paste Table from Qlik (pasted into b,c,d columns)
6. Select cell E6 and a add a forumula

Now this is where I struggle. I want to do a loop where I go through C6 to no of rows pasted (example 20 rows = C6:C25) and copy+paste forumla from E6 into E6:E25.

I currently have the following but am not getting anywhere. The loop gets me a type mismatched error.

'//////////FUNCTIONS//////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


function ActivateSheet(SheetID)
        oSheet = ActiveDocument.Sheets(SheetID).Activate
        ActiveDocument.GetApplication.WaitForIdle
end function


function GetBitmap(SheetID,ObjectID)
        ActivateSheet(SheetID)
        ActiveDocument.GetSheetObject(ObjectID).CopyBitmapToClipboard
end function


function GetTable(SheetID,ObjectID)
        ActivateSheet(SheetID)
        ActiveDocument.GetSheetObject(ObjectID).CopyTableToClipboard true
end function


function GetText(SheetID,ObjectID)
        ActivateSheet(SheetID)
        ActiveDocument.GetSheetObject(ObjectID).CopyTextToClipboard
end function


function GetValues(SheetID,ObjectID)
        ActivateSheet(SheetID)
        ActiveDocument.GetSheetObject(ObjectID).CopyValuesToClipboard
end function

'//////////START SUBS//////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////




sub ExportExcel
    on error resume next
    
    set vFilePath = ActiveDocument.Variables("vFilePath")
        FilePath = vFilePath.GetContent.String


        excelFile = FilePath & "Output_Templates\Template.xlsx" 'file path for template file
        excelSave = FilePath & "Output_Files\" 'save location
    
    
    Dim objApp, objWbs, objWorkbook, objSheet
         
    Set objApp = CreateObject("Excel.Application")'opens excel    
    Set objWbs = objApp.WorkBooks
    objApp.Visible = FALSE'hides excel file creation into the background - true = excel is shown
    objApp.DisplayAlerts = FALSE
    
    
    Set objWorkbook = objWbs.Open(excelFile)'opens workbook
    Set objSheet = objWorkbook.Sheets("Sheet1")'sets worksheet name
    
    
    '////////////////Sheet1//////////////////////////////////////////////////////    
    objWorkbook.Sheets("Sheet1").Activate 'activates "sheet




                    call GetTable("MACROTEST","CH03") 'gets text from a specific object in a specific sheet
                    
                    set chart=ActiveDocument.GetSheetObject("CH03")'gets the specified object
                        ActiveDocument.Variables("vRowCount1").SetContent chart.GetNoOfRows, true 'gets a count of rows from the object specified above and dumps it into a variable            
                        set vRowStorage1 = ActiveDocument.GetVariable("vRowCount1")'declaring rowcount variable. getting data from qlik variable
                        vNoOfRows1 = CInt(vRowStorage1.GetContent.string) 'getting the contents of vRowCount variable and converting to an integer            




                    objSheet.Range("B6").EntireRow.Resize(vNoOfRows1).Insert'inserts rows based on the amount dictated by the vRowCount variable
                    objSheet.Range("B5").Select 'selects a range in excel (a range is a set of fields with the same alias)
                    objSheet.PasteSpecial (xlPasteValues)'Paste Values only so it keeps destination formatting
                    
                    objSheet.Range("E6").Select 'selects a range in excel (a range is a set of fields with the same alias)
                    objSheet.Range("E6").Formula = "=C6+D6" 
                    'objSheet.Range("E6").Copy
                    'objSheet.PasteSpecial 'Paste Values only so it keeps destination formatting
    

                                'Loop Starts here
                                 Dim x as Integer
                      ' Set numrows = number of rows of data.
                      NumRows = Range("C6", Range("C25").End(xldown)).Rows.Count
                      ' Select cell.
                      objSheet.Range("C6").Select
                      ' Establish "For" loop to loop "numrows" number of times.
                      For x = 1 To NumRows
                     objSheet.Range("E6").Copy
                    objSheet.PasteSpecial 'Paste Values only so it keeps destination formatting
                         ActiveCell.Offset(1, 0).Select
                      Next










    '////////////////Moving on to saving the file///////////////////////////////////
            
    'saves excel doc to excelSave path
    objWorkbook.SaveAs excelSave & "Results - " & Left(Replace(Replace(Now,"/","-"),":","."),16) & ".xlsx"'xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
    'msgbox(excelSave & "Results - " & Replace(Replace(Left(Now,"/","-"),17),":","") & ".xlsx")
    'displays error message if more than 0 errors are detected
    if err.number > 0 then
        msgbox("No. " & err.number & " Src: " & err.source & " Desc: " & err.description )
    end if


    objWorkbook.Close False
    objWbs.Close 
    objApp.Quit 
     
    Set objSheet = Nothing
    Set objWorkbook = Nothing
    Set objWbs = Nothing
    Set objApp = Nothing


    Set objApp = CreateObject("Excel.Application")
    Set objWbs = objApp.WorkBooks
    objApp.Visible = TRUE
    
    Set objWorkbook = objWbs.Open(excelSave)
    Set objSheet = objWorkbook.Sheets("Split of Revenue Transactions")
    objWorkbook.Sheets("Split of Revenue Transactions").Activate


end sub

Thanks

[offthelip]

change your loop to the following:


[VBA]For x = 1 To numrows
rowno = x + 5
te = "E" & rowno
ce = "C" & rowno
de = "D" & rowno
objSheet.Range(te).Formula = "=" & ce & "+" & de

Next
[/VBA]

[snb]

In which program do you run this (horrible) code ?

[root01]

Thanks for this. Mods, please close the thread. This worked.

[root01]

In which program do you run this (horrible) code ?

Qlikview. Could you please help me make it better if its horrible?

[Paul_Hossler]

Thanks for this. Mods, please close the thread. This worked.

At the top of your first post, there's [Thread Tools], which has the option for you to mark your thread Closed

[root01]

At the top of your first post, there's [Thread Tools], which has the option for you to mark your thread Closed

Thanks. I'll close it now.


Update: I couldn't find a close option. I did mark it as solved though. Not sure if that is what you meant.