PDA

View Full Version : [SOLVED] VBA Loop - If Cell is not empty then....



root01
06-06-2016, 02:14 AM
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
06-06-2016, 02:55 AM
change your loop to the following:


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

Next

snb
06-06-2016, 03:08 AM
In which program do you run this (horrible) code ?

root01
06-06-2016, 03:09 AM
Thanks for this. Mods, please close the thread. This worked.

root01
06-06-2016, 03:10 AM
In which program do you run this (horrible) code ?

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

Paul_Hossler
06-06-2016, 06:29 AM
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
06-06-2016, 07:10 AM
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.