Free Microsoft Excel 2013 Quick Reference

Quit vba excel Results

Hello Everyone,
I've been using these forums for quite some time but I am completely stuck, so this is my first post. I'm currently attempting to Copy rows from sheet "A" to sheet "B" based on a Boolean in column A sheet "A".

The Code I have works, but I'm trying to figure out how to modify it so it doesn't sort the data on Sheet A and can be run from Sheet B.

I continue to screw this up and do not know VBA well enough yet. Thank you in advance for any assistance you are able to give me it is much appreciated. Please let me know if I'm not making sense or need to clarify anything I'm trying to do. (Excel 2010 and Windows 7)

Thanks,
VS

Sub test1()
Dim r As Range, filt As Range
With Sheets(2)
.Range("B5:I17").Cells.Clear
End With
With Sheets(1)
Set r = .Range("A4").CurrentRegion
r.Sort key1:=Range("A4"), order1:=xlDescending, Header:=xlYes
r.AutoFilter field:=1, Criteria1:="TRUE"
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

filt.Areas(1).Columns("C:J").Copy
With Sheets(2)
.Range("B5:G17").PasteSpecial
End With
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub


When I write VBA macros which call for forms to be displayed on the screen, invariably if the user drags the form over the screen it leaves a trail of images of the form in its original place with repeated images along the drag path. Looks quite pretty but very irritating. The reason that the user is dragging the form in the first place is because he wants to see what is in the cells behind the current location of the form, but currently dragging the form does not reveal those cells. Quite the reverse, it just fills up more and more of the screen with images of the form.

It is a bit like the opening credits of the UK sit-com "the IT crowd", if that means anything to you.

So, is there something that I can do to inhibit this behaviour, please? Excel 2003.

Thanks

I am posting in the programming section because I think that this could be quite complicated. I have only tinkered with very simple VBA.
Is there a way to do the following:
Imagine an office planner. In excel this will be names across the top and dates down the side. Now imagine it filled with jobs for various individuals. Jobs which will take say 2 or 3 weeks. Once completed, these jobs will be given to other people to check, say another week or so. (The times spans are not important) These jobs have been inserted using different colour fills together with a ref no for that job. Great. Thats easy peasy until someone goes sick and the whole schedule has to be moved on say 2 days.( or for many other reasons). Is there a way to move an entire workflow without amending the boxes manually.
many thanks in advance.
Paul

I have created a macro that will import external data from a USB drive using a querytable. The problem is that after the info is imported from the USB drive, then the "Safely Remove Hardware" option doesn't work for the USB drive. I am guessing the Excel is still using the USB drive, as once Excel is closed, then the "Safely Remove Hardware" option is viable again.

What I need is to be able to automatically re-enable the "Safely Remove Hardware" feature without having to close Excel, or without having to close the worksheet. What I think I need is some way to end the querytable connection from the worksheet to the USB drive.

I don't need to be able to constantly refresh the query. Once the query is performed the first time, and once the data I need in imported, then I am done with importing data.

Is there such thing as using a "querytable.quit" in my VBA code?

Thank you in advance!

Hi folks,

I've got a problem which is quite complex and I know little or nothing from VBA so...

At work we use an excel sheet for our waste water, and instead of having to copy the
excel file to a stationary email in lotus notes on a shared email account, I was hoping I could add a button which does that trick for me.

So, the excel sheet is just one sheet, called "Sloot Silo2.XLS".
The entire workbook should be added to the email as an attachment.

We all can acces a shared email account in lotus notes, which is called LABO Sluiskil,
located on server APP02/Srv/Yara with the filename GMAILya001009.nsf.

and the stationary email has a subject line of Sloot Silo2 segm4.

Is there any way to do this ?
If it could open an email, attach the file and add the "to", "CC" subject line and body message (which can be stored inside the script I suppose) I would be happy too.

If it could simply attach the file to this stationary it would be even easier.

However it would also be needed that the script only sets the email ready to send,
but that the user still has to press the send button so that he can oversee the thing and check
for possible mistakes he made.

Quite a thing to tackle, and I wouldnt be surprised if this wouldn't be possible or if nobody knows how to do this.

anyways: thanks for trying !

kind regards

Hi all,
I'm quite newbie for VBA for excel, my friend asked me a favor to filter the data on his excel, so when he enter some keyword, the table would sorted (only show the row that have that keyword), and if possible that he enter it few keyword.

keyword example VB, oracle

table example

project description
school making oracle dtbase
<blank> VB project
java database

when he enter vb, oracle then the table become

project description
school making oracle dtbase
<blank> VB project

any idea how? or someone would mind to give me example/make what the macro would like?
since i never use VB before it really difficult for me to built it from scratch..

thanks for your help

Hi,

Using Excel 97 on Win XP.

I receive orders for service milestones awards and use a macro to format the spreadsheet and perform some analysis of the data. I'm stuck with one of the things I need to do. I need to compare the employee number on the new order with the All Orders worksheet which contains previously ordered awards.

It is quite possible that the employee has previously been provided with an award, however, it may be for a different milestone and is legitimate.

I need to identify if the employee is ordering a second award for the SAME milestone. What I'm trying to achieve is to use CF to highlight the employee in the NewOrders book where an award for the SAME milestone has already been ordered (AllOrders.xls). I have attached a couple of sample spreadsheets to try to explain this better. Is CF the best way to do this and what type of formula can I use in VBA to achieve this?

Many thanks.

Hello,
I've a quite complex (at least for me :-)) problem I should solve within my excel (possible not using VBA).
Suppose you have this sheet:

week 1/1/06 1/8/06 1/15/06 1/22/06

john 3 5 3 5
----> s s s s
jim 5 5 5 5
----> i i i i
micky 5 5 5 5
-----> a a a a
mouse 5 5 5 5
-----> w w w w
sara 5 5 5 5
----> s i i i

where, for each week, I've listed the number of days a single resource (ie micky) and the task (s,i,a,w) he/she will work on (example: john will be present 3 days on week 1/15 and he will work on task "s")

Now, what I need to do is to summarize how many people/days will be spend for each task for each week

Something like this:
task i
week 8-Jan
people/days ==> 10

the diffculty is that the "week" and the task for which I need to summarize the people/days spent, are inputs from user.

I was thinking to use the SUMPRODUCT, but it doesn't work since the presence of chracters in the interested area ...

Hope the problem is clear enough...
*pimar

Hi
I know this is not the place to ask about this
but maybe someone can help

In vba for excel i can close the application with
appliction.quit
but in Visual basic where i want to create a exe file
how can i shot this down, i cant't find the code anywhere

hope someone can help?

regards alvin

Hello all.

I've seen this thread solve more than one problem for other people (and piggy-backed my woes onto that success rate too, thanks) so wondered if you could help me with this issue.

I would like to export each row of data in an Excel spreadsheet. I would like to export them separately, and I would like to export them to word documents, one per row.

Is there any way of automating this?.

I am not too good at formulas, and am useless at VBA. I tried using Mail Merge, but that didn't work.

My spreadsheet is quite large, at some 22,000 rows and five columns.

Any help would be appreciated.

Regards.

Al.

Hye everyone,

as I am new here maybe a quick introduction. I'm in a graduate in financial economics and am currently studying an in depth course concerning fixed income securities. It will come as no surprise to you guys that you need advanced knowledge of Excel to successfully master "every" concept in the book. I am not an internet noob and have tried to find answers to these questions using both the search engine here and google but could not find any satisfying answers.

My first question is whether excel can handle the standard summation sign as we encounter it in mathematics.

For instance, I get to work with a lot of mathematic formulas like in the duration or convexity formulas. But sometimes I also encounter functions such as the Par yield function:

Looks like this http://upload.wikimedia.org/math/a/3...7df84ebb69.png

http://en.wikipedia.org/wiki/Par_yield

Now my question is: Say I have this table

k p
1 0.8
2 0.9
3 0.5
4 0.6

is there a formula that allows me to use the summation sign as in the equation and define k and n? so that if I put the formula in the next column I can simply drag the formula?

Also, is there any quick VBA plugin to define the convexity of a bond? I think this is a very FAQ but have not found a satisying answer, if I had the knowledge of VBA programming I think you could easily implement this? However, as I found no really good answer I suppose there isnt :D? At the moment I do this manually but this is quite cumbersome...

Thanks in advance,

unfortunately many questions will follow :D

Hello everyone!

I have a simple problem that I have to solve with an excel macro. The thing is simple and I would know how to solve it with, for instance, C++, but I am quite a noob with VBA so I need your help. I have the following situation:

Row_number; Level; Parent_row;
1 1 0
2 2 1
3 3 2
4 2 1
5 4 3
6 2 1
7 3 6
8 4 7
9 2 1
10 2 9

Every row is associated to a level and, in parent_row I need to calculate the numbers in Parent_row colum: the number of the first row above "row_number" that has the level-1.

Examples: Row number 7 has level 3. Which is the first row above 7 that has level 2? Answer: row 6.
Row number 6 has level 2. Which is the first row above 6 that has level 1? Answer: row 1.

Starting from the bottom line, the algorithm would be:

-Get row level.
-Check row-1 level.
-If level(row-1) is smaller, then parent_row(row)=row_number(row-1).
-Else, check above...

Thank you in advance!

I am really new. Just started writing VBA in excel a few hours ago and I'm quite weak in using the loop.

I have a my values in column and I want to transpose it into a row - in another workbook.
However I want the transposed value to skip 3 columns i.e.

Paste in A and skip B,C,D, and then paste E and skip F,G,H and paste in I

A
------
1| ABC
2| DEF
3| GHI

into

A B C D E F G H I J
----- ----- ----- ------ ------- ------ ------ ----- ----- -----
1| ABC DEF GHI

Please help me out.
I've tried going thru tutorials but I'm still left dumbfounded.

Folks,

I am looking for excel vba macro to help me find files bigger than 2mb. I couldn't find the criteria size. Can anyone help me please?

Here it is my search file, without size criteria.

Thanks, Bruno

Sub FindFile()

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objSearch = objExcel.FileSearch
objSearch.LookIn = "D:TmpTest"
objSearch.SearchSubFolders = True
objSearch.Filename = "*.mp3"
objSearch.Execute


For Each strFile In objSearch.FoundFiles
    MsgBox strFile
Next

objExcel.Quit

End Sub


Let me first inform you that I am quite new to VBA programming but have decent skill with Excel. I was going through an online tutorial about macros and VBA and came across this bit of code. It's purpose is to combine full names in column C when the first name is input into column A and the last name into column B.

I attempted to debug the code myself. I was able to 'Step Into' the first two lines, but the "Error 9: Subscript Out of Range" error popped up everytime I tried to step into a line below the first two. can someone please assist me? Thanks!
Sub proTest()

    Sheets("Sheet1").Select
    Range("A1").Select
    
    Do Until Selection.Offset(0, -2).Value = ""
        Selection.Value = Selection.Offset(0, -2).Value & " " & Selection.Offset(0, -1)
        Selection.Offset(1, 0).Select
    Loop
    
    Range("A1").Select
    
End Sub


I am using this code in my Excel VBA application to execute a mail merge routine in MS Word.

Sub merge()
    
    Set objword = Nothing
    Set odoc = Nothing
    Set Odoc2 = Nothing
    Set objword = CreateObject("Word.Application")
    objword.DisplayAlerts = True
    Set odoc = objword.Documents.Open(Range("A37").Value)
    objword.Visible = True
    'Odoc.Application.Visible = True
    odoc.MailMerge.Destination = wdsendtonewdocument
    odoc.MailMerge.Execute
    Set Odoc2 = odoc.Application.Documents("Catalog1")
    odoc.Close False
    mypath = "e:Integrity12Workorders" & Format(Range("A26"), "ddd dd-mmm-yy")
    If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath
    Odoc2.SaveAs mypath & "" & (Range("A36").Value & ".doc")
    objword.DisplayAlerts = True
    objword.Application.Quit True
    
    AppActivate "Microsoft Excel"
    
End Sub
The application hangs at the line in red. I am not able to manipulate anything in Excel at this point, it does not react. Microsoft Word is not visible, yet it is there in Task Manager. I have to close Word via task Manager to regain control.

The value in worksheets("varhold").range("A37") is E:Integrity12ReportsDRDR_CUL2.dot

Any ideas as to what I am doing wrong?

I've got a spreadsheet in my Excel 2010 workbork with a lot of data. This page can be sorted by the build in autofilter. On another sheet I have made a button which do some of the sorting. FX:

Sub andenKvartalAftaleIndgaet()
'

    Sheets("AMRM01").Select
    ActiveSheet.Range("$A$12:$P$76").AutoFilter Field:=2, Criteria1:=">01-04", _
        Operator:=xlAnd, Criteria2:="<01-07"
    Sheets("Ark2").Select
End Sub
When I do this the sheet shows the second quarter of the year. I've got a button which show the third quarter of the year which looks like this:

Sub TredjeKvartalAftaleIndgaet()
'

    Sheets("AMRM01").Select
    ActiveSheet.Range("$A$12:$P$76").AutoFilter Field:=2, Criteria1:=">01-07", _
        Operator:=xlAnd, Criteria2:="<01-10"
    Sheets("Ark2").Select
End Sub
My problem is: When I press the first button (Second quarter) it filters and show the second quarter. But when I press the "Third Quarter" button it removes the Second Quarter filter and views only the Third Quarter.

Is it possible to write some kind of VBA code which make it possible to have both button pressed at the same time, like it's possible to do with the build in autofilter buttons?

If you're not quite sure what I mean, please ask for better explanation.

Thanks in advance

Dear all,

I have written a dll with a class with startex() and closeex() procedure.
In excel programming VBA, I have declare the sub of startex and closeex. They will refer to the dll.
But would startex and closeex share the same object e.g. xlApp and
would closeex close the object (xlApp) which set by startex????

On the other hand, is it possible to declare the class in excel programming VBA? If yes, then I am sure that they are accessing to the same instance of the class.

public class class1
{
Excel.Application xlApp;
...
private void startex()
{
    xlApp=new Excel.Applicationclass();
..........
}

private void closeex()
{
    xlApp.Quit();
    releaseobject(xlApp);
...............
}
}


Hello,

I am seeking an help for a vba Macros script which can save a particular worksheet as .xls & pdf format from the same workbook. Below is the explanation.

I have download a sample file from the internet which have the following below option and are working fine:-
- Print preview the selected sheet.
- Print the selected sheet.
- Quit when click on Exit button.

Now I have added 2 buttons on the same form which have to do the following:-

- The selected sheet should Save as .PDF when I press Save as PDF button in the same place with below mentioned format. If the selected sheet is blank the it should give message “Cannot save sheet as PDF because the sheet is blank”.
- The selected sheet should save as .xls when I press Save as Excel button in the same place. If the selected sheet is blank the it should give message “Cannot save sheet as excel because the sheet is blank”.

NOTE: I AM USING EXCEL 2010 AND OTHER USERS USING EXEL 2007. SO IF POSSIBLE SAVE AS PDF CAN BE WORK FOR ALL THE VERSIONS I WILL BE VERY THANKFUL TO YOU.

The format should be save as below mentioned format.

Example: 1000 NRP Excelforum.com

Where as:-
1000 - Is the value of NRP sheet E23
NRP - Is the selected sheet name
Excelforum.com - Is the value of NRP sheet A23

I hope the above mentioned details are the clear explanation to help me and this is the last assignment. I have attached sample file for more clarification.

Hello - apologies if i have given this a wrong title (please feel free to amend)

I have extracted a report from a web package into Excel - basically a list of documents held on our website. The Cells in Column B would have the word Title or Description, Column C would have the actual title of the document, Column D would be blank on rows with Title in Column B and Column E has a Document ID (DocID)Number.

To make this easier to read is it possible to filter Column B using Title and then move the DocID number from Column E to Column D.

I have attached a shortened version of the spreadsheet with the filter applied in Column B already. The real spreadsheet is 1000 lines long and would take quite a while to drag and drop each DocID number from Column E to D.

I assume this can only be done with VBA so if anyone has any suggestions i would really appreciate it.

Thanks

Philip


No luck finding an answer? You could always try Google.