Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA - xl Prefix

Group,
xlToLeft, xlBottom, xlSolid, xlAutomatic, and xlPasteValuesAndNumberFormats to name a few. What are these called and where can I get a list of them. I see them everywhere.

Tony


Post your answer or comment

comments powered by Disqus
I have VBA functions saved in Personal.xls, which I need to call with a 'personal.xls!' prefix, or I get the #NAME? error. I do have the Personal.xls file open but hidden.

Is there any way to have the personal.xls functions be global, and not require the personal.xls! prefix?

Hi all,

Say I have a workbook called test1.xls, where the number 1 at the end of the file name is variable. Tomorrow that character could be 2 so the file would become test2.xls etc (or could even be a letter for what it matters).

Is there a way in Visual Basic to recognize that the final character is not fixed but can get different values each time? Or in other words to recognize that the file starts with "test", and that whatever follows that prefix is not relevant?

For example....

If ActiveWorkbook.name = "'test*.xls" Then...

where * (star) could be any character.

As a Windows analogy, I am thinking of the * (star) character when one searches a certain directory for, say, Excel files (*.xls etc). Obviously, in VBE that doesn't work, and the star is just a character like any other.

Many thanks
BC

XL2007 promoted the XIRR function, previously part of the Analysis Toolpak, to Worksheetfunction status. This implies that using such function no longer needs the add-ins (ATP and ATP VBA) nor a reference to atpvbaen.xls

So far, so good. But when migrating an application from xl2003 to xl2007, I had to call the function explicitally:

2003: dblResult = XIRR (aValue,aDate)
2007: dblResult = Application.WorksheetFunction.XIRR(aValue,Adate)

A much more serious problem was that the function apparently no longer accepts direct input from VBA. The function performs as expected when the arguments are located in a worksheet range. When the arguments are VBA array Variants the program bombs with a 1004 error: Unable to get the XIRR property of the worksheetfunction class. Although Intellisense clearly shows this function during coding.

The MSDN page (http://msdn2.microsoft.com/en-us/library/bb224771.aspx) that describes the function does allow variants as arguments.

Did I missed something?

I included a Zipped folder that contains an Access Database (DBXIRREN - saved in 2003 compatibility mode) and a Workbook (XIRR2007). The latter contains three worksheets:

SCRATCH: Illustates the use of the XIRR function as an Excel Function and indirectly as an encapsulated VBA function. The latter as a proof of concept.

RANGE: The data is pulled from the data base into a workheetrange and then manipulated by an encapsulated XIRR

DYNAMIC: The one that should work, but doesn't. Data again is pulled from the database and passed directly to the function. This works in 2003. Why do I get the 1004 now?

The workbook does contains macros that encapsulate the XIRR function. These macros are called during Workbook_open event. You can adapt the path to the database by modifying cel $D$3 in worksheet SCRATCH.

Any clue that gives insight into the problem is most welcome.

PS: I didn't check out any other "promoted" ATP function.

Hi

I found a macro deleting rows on a certain condition on this board...it works great.

Just two questions.

1. I would like to macro to only delete an entire row if all values in that row are equal to "0.0"?

2. Since I'm a novice with VBA I don't understand what the icount does and would appreciate it if someone could briefly explain it to me?

------------------------------
Sub DELETEZEROS()

Dim icount As Integer
Dim strtext As String
Dim rfound As Range

Application.Calculation = xlCalculationAutomatic
Columns("b:n").Select
Set rfound = Range("b10:n500")
For icount = 1 To WorksheetFunction.CountIf(Columns(13), strtext)
Set rfound = Selection.Find(What:="0.0", After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
On Error GoTo done:
rfound.EntireRow.Delete
Next icount
done:
End Sub
--------------------------------
Many thanks

Sanet

Hi,

I have a process that does a loop through of thousands of rows of data on excel, (one at a time), and then exports the data from Excel to Access using SQL and VBA.

It will do this for a long list of records eg 10,000 which means it will go through this process 10,000 times one row at a time....ie is run a calculation on one row, export it to Access and then do to the next row and so on. When it exports the data by SQL to Access it adds each field in cell by cell.

The problem is this processes only 1500 lines per hour. I was wondering if there is any software that will quickin this up alot. Or maybe there is a way of exporting the whole line (record) to Access rather than field by field.

Thanks

Simon

I just made a custom function in Personal.xls, but it only seems to work when I write personal.xls! in front of the function name. Why? How can I make it universal?

Cheers

I am still a newbie in VBA and I hope someone could help me figure out how to optimize my code.
The execution time is too slow when processing 1000 rows.

I am not sure if the following codes have something to do with the slow performance of the program.

	VB:
	
iRow2 = 0 
For iRow = 1 To 1000 
    Sheets(conSheet_Wk1).Select 
    Rows(conRow_Wk_Siki_GokeiGaku).Select 
    Selection.Copy 
    Rows(iRow2 + conWkStartRow).Activate 
    ActiveSheet.Paste 
     
    Sheets(conSheet_Wk2).Select 
    Rows(conRow_Wk_Siki_GokeiGaku).Select 
    Selection.Copy 
    Rows(iRow2 + conWkStartRow).Activate 
    ActiveSheet.Paste 
    ... 
    .... 
     
     '1-10
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_NyuryokuKb).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_NyuryokuKb).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Hyoji).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Hyoji).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_KaisyuNo).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_KaisyuNo).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Seiban).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Seiban).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_BumonCd).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_BumonCd).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Hinmei).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Hinmei).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_KeiyakusakiCd).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_KeiyakusakiCd).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_KeiyakusakiNm).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_KeiyakusakiNm).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_JutyuYM).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_JutyuYM).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_UriageYM).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_UriageYM).Value 
    ... 
    ... 
     '141-148
    heets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Meuk_Furi4Q).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Meuk_Furi4Q).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Meuk_FuriSimo).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Meuk_FuriSimo).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Meuk_FuriTou).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Meuk_FuriTou).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Meuk_FuriRai).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Meuk_FuriRai).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_KingyoBati).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_KingyoBati).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_KoujiSinko).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_KoujiSinko).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_NyukinJoken).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_NyukinJoken).Value 
    Sheets(conSheet_Wk1).Cells(iRow2 + conWkStartRow, conCol_Biko).Value = Sheets(conSheet_Input).Cells(iRow +
conInputStartRow, conCol_Biko).Value 
    ... 
    ... 
Next 
iRow2 = 0 
For iRow = 1 To 1000 
    If Len(Trim(Sheets(conSheet_Input).Cells(iRow + conInputStartRow, conCol_KaisyuNo).Value & _ 
    Sheets(conSheet_Input).Cells(iRow + conInputStartRow, conCol_Seiban).Value)) = 0 Then 
    Else 
        iRow2 = iRow2 + 1 
         
         '入力区分
        Select Case Sheets(conSheet_Input).Cells(iRow + conInputStartRow, conCol_NyuryokuKb).Value 
        Case conNyuryoKb_KojiSinko '工事進行
            Call KoujiSinko_Set(iRow2) 
        End Select 
    End If 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you very much!

P.S. Sorry if my variable names were in Japanese.

Hi,
I have been struggling to get over XL 07 change for my XL 03 file.
Here in XL 03 I have a macro which filters data using 'autofilter' and then copies that visible data to a new sheet.
Unfortunately when I run the same in XL 07. Macro copies every single line visible & hidden to a new sheet.

Does any one have encountered similar issue? Does anyone have solution to this issue?

Thanks in advance...

Is an xla still rqd to call the COM add-in?

I am (still) considering my options wrt converting my vba xl add-in to
a COM add-in (c/- Office Developer). As I have never actually seen a
COM add-in I am curious to know how this would work. For example, do I
still need an xla module to add my commandbar to xl and then call the
subs/functions in the COM add-in or can I directly compile the entire
xla as it is?

Thanks a lot,
Andrew

Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ

MACRO :

Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date_week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table ?
In my exemple, the result would be 4.

Hi,

I want to begin learning SQL so as to complement my VBA XL skills and get myself a more development related back office banking job.

I have found an introduction course and wondered if anyone had been on a similar course or could recommend one or even just give their opinion on if its worth spending the money to go on it at all.

Here is the spec, its going to cost me about 400 over 2 days and is through "Interquad".

SQL Introduction 2 days
Profile
This course is for programmers and others who are expected to use SQL with a relational database.

Completion of this course will give delegates a sound understanding of SQL as it is implemented by the commercial databases.

Delegates will be able to interpret SQL statements and develop their own SQL processing functions. They will be able to devise sophisticated queries and manipulate the data in relational databases.

Delegates will also gain useful insights into the mechanics of relational databases.
Prerequisites
 Delegates will benefit from an understanding of programming principles.
 We recommend that attendees have some experience with Unix or Windows NT before commencing this event.
Skills
 Introduction: Defining Structured Query Language. Introduction to relational databases. Tables, Rows and Columns. Interactive SQL environments. General SQL guidelines.
 Developing queries: The SELECT statement. The WHERE clause. Specifying conditions Relational and logical operators. Pattern matching with LIKE. Other operators. The ORDER BY clause. The GROUP BY and HAVING clauses. Computed values.
 Data manipulation: Inserting new data. Modifying existing rows. Deleting data that is no longer required.
 Database Definition: Table creation. Specifying data types for columns. Examples of data types.Changing, removing tables. Creating indexes. Indexing strategies.
 Controlling access to the database: Privileges and Permissions. Privilege management. The GRANT and REVOKE statements.
 Advanced SQL features: Aggregate functions, Nested queries. Outer Joins, Views.

My rota changes every saturday. So I am using a workbook to record all the weekly rotas that I have worked.
On sheet1 I am using an input box to search for dates in column A on sheet2. All dates down column A on sheet2 are saturdays ie week beginnig Saturday.
The find method I am using works fine if the user's entry in the inputbox is always a Saturday date.

If for example the entry happens to be 05/02/2005 which is a Saturday and is recorded in column A sheet2 then find method works ok.

If ,however,the user's entry is any other date which is not a Saturday let's say 13/02/2005 which is a sunday then the record will not be found simply because the date 13/02/2005 -a Sunday- does not exist in column A sheet2.

But idealy what I want the find method to do is to search in columnA sheet2 for the Saturday prior to the date entered in the inputbox if it happens not to be a Saturday.

In other words and assuming that the date 05/02/2004 -a Saturday-is a record in column A sheet2:

if I enter in the inputbox 05/02/2005 then msgbox "Date 05/02/2005 found."
if I enter in the inputbox 06/02/2005 then msgbox "Date 05/02/2005 found."
if I enter in the inputbox 07/02/2005 then msgbox "Date 05/02/2005 found."
if I enter in the inputbox 08/02/2005 then msgbox "Date 05/02/2005 found."
if I enter in the inputbox 09/02/2005 then msgbox "Date 05/02/2005 found."
if I enter in the inputbox 10/02/2005 then msgbox "Date 05/02/2005 found."
if I enter in the inputbox 11/02/2005 then msgbox "Date 05/02/2005 found."

And the same will go for the date 12/02/2005 -a Saturday- if it is a record in column A sheet2 and all other records that need to be searched for.

Am I banging my head trying to do something that can't be done or is it within the limit of vba xl?

Your help in solving this is greatly appreciated and thanks in advance.

I have a macro that's been working for a year. Now the file I received is saved with row 33000 at the top of the sheet. My freezepane doesn't work right anymore. It freezes with 33000 as the first row. Here's my code:

Range("A1").Select
ActiveWindow.FreezePanes = True

I mistakenly thought that the range command would make it freeze at row 1...guess I thought wrong!! :-)

How can I make this freeze row 1 instead of freezing the first row viewed?

TIA...MJ

Hi

I'm a newbie to this forum and hope that someone may be able to provide a solution to what should be a simple problem. I used to have a good working
knowledge of VBA some years ago, but haven't used it for ages and am very rusty.

I have a spreadsheet with 53 sheets - one for each week of the year, together with a summary sheet. Each weekly sheet (called, arbitrarily, 01 - 52) has its
information in identical cells, reflecting cash flow for that particular week. Totals for each particular product code reside in column fashion in the same
cells (say M30..M55) in each weekly sheet.

The summary sheet itemises the totals for each product code on a weekly basis in rows (i.e. one row for each week). Each product code column needs to pick up the formula for its respective week's sheet. I've attached a screen dump as a Word doc to try and explain: [VBA Question Screen Dump.doc]

On the summary sheet, cell B8 has the formula: ='02'!M27 which picks up the total for code 3200 from sheet 02. Likewise, E8's formula is: ='02'!M30 which
similarly picks up the total for code 3201. And so on. As some items are subject to VAT (tax) and some are not, the gross figures in the summary sheets do
not necessarily lie in adjacent columns.

By running the macro recorder (using sheet 2 as an example) I get something like the following:


	VB:
	
 
Sub TestSummariseManual() 
     '
     ' TestSummariseManual Macro
     ' Macro recorded 11/01/2012 by Paul Webster - manual recorder
     '
     
     '
    Range("E8").Select 
    ActiveCell.FormulaR1C1 = "='02'!R[22]C[8]" 
    Range("H8").Select 
    ActiveCell.FormulaR1C1 = "='02'!R[23]C[5]" 
    Range("K8").Select 
    ActiveCell.FormulaR1C1 = "='02'!R[24]C[2]" 
    Range("L8").Select 
    ActiveCell.FormulaR1C1 = "='02'!R[25]C[1]" 
    Windows("Cash_Analysis_2012 (WITH VBA).xls:1").Activate 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I need VBA to do is loop through each weekly sheet, pick up the formula(e) for each code and place it in its respective cell for that given week.
Obviously as each week's figures are input to that week's sheet, they will then automatically populate the summary sheet.

I hope I've explained this sufficiently and that someone will be good enough to offer me a solution.

Thanks and regards

Webbo

hello, lm not sure how to explain this one so i will attach two things that will help me explain. Basically, l have created a form in VB6 that does exactly what we want. But, we need a copy of this in VBA. I have created one but it is slightly different and l cannot figure out how to get them the same. It is when selecting drives and folders. In VB6 its fine, but l cannot find how to do it in VBA. Can some one help please. l attach both the VBA XLS File and the VB6 .exe its the VB EXE that it should look like. Thanks.

Hi everybody,

I have written the following script to transfer a record marked as Closed in column E from "To Do List" sheet to another called "Completed Items". This is a cut & paste operation. When I fired up the macro for the first time, it worked fine. But if I use it for the second time to move another record, I have an error message - which says "paste method of worksheet class failed". I couldn't work out what's wrong. I really appreciate if anyone could pinpoint the error. Thank you so much.

Regards


	VB:
	
 
Sub Moverow() 
    Sheets("To Do List").Select 
    ActiveSheet.Unprotect 
    ActiveSheet.ScrollArea = "" 
    If Cells(ActiveCell.Row, 5) = "Closed" Then Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 7)).Cut 
    Sheets("Completed Items").Select 
    ActiveSheet.Unprotect 
    ActiveSheet.ScrollArea = "" 
    Range("A65536").End(xlUp).Select 
    ActiveCell(2, 1).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Range("A5").Select 
    lastrow = Range("A65536").End(xlUp).Row 
    Range(Range("A5"), Cells(lastrow, 6)).Select 
    Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    lastrow = Range("A65536").End(xlUp).Row 
    Selection.FormatConditions.Delete 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=MOD(ROW(),2)=0" 
    Selection.FormatConditions(1).Interior.ColorIndex = 35 
    ActiveSheet.ScrollArea = "A5:F" & lastrow 
    Cells(ActiveCell.Row, 1).Select 
    ActiveSheet.Protect 
    Sheets("To Do List").Select 
    ActiveCell.EntireRow.Delete 
    lastrow = Range("A65536").End(xlUp).Row 
    ActiveSheet.ScrollArea = "A4:F" & lastrow 
    Range("A5").Select 
    lastrow = Range("A65536").End(xlUp).Row 
    Range(Range("A5"), Cells(lastrow, 6)).Select 
    Selection.Sort Key1:=Range("A5"), Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    Range(Range("A5"), Cells(lastrow, 6)).Select 
    Selection.FormatConditions.Delete 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=MOD(ROW(),2)=0" 
    Selection.FormatConditions(1).Interior.ColorIndex = 35 
    Cells(ActiveCell.Row, 1).Select 
    ActiveSheet.Protect 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
===========================

How do you get macros to be universally accessible as functions without using the "personal.xls!" prefix? For instance I have the macro testmacro(), and if I do "personal.xls!testmacro(...)" it works fine, but if I just do "testmacro(...)" it gives me a "#NAME?" error.

I have PERSONAL.xls (which is in the XLStart folder) as a hidden window. I tried unhiding and re-hiding it and saving, but that didn't help.

Can anyone help me.
I've attached one speadsheet :- "VBA.xls". The following conditions are to be done:-
1. When D2=1, Column "F","G" are visible & Column "I","J","L","M" are hidden.
2. When D2=2, Column "I","J" are visible & Column "F","G","L","M" are hidden.
3. When D2=3, Column "L","M" are visible & column "F","G","I","J" are hidden.

Thanks

Hi,

Im currently dowloading stock figures from yahoo finance in to an EXCEL spreadsheet. Im using comboboxes to define the dates. I am using a textboxes with either d=daily, w=weekly,m=monthly to define the type of data i need. This works perfectly fine.

Problem
If I were to ask it to downlaod a years worth of data which would equal 252(trading days per year) entries, it will display these on the website however when it extracts the information to the CSV table it only gives 200 entries. I contacted yahoo and they confirmed this. I am actaully modelling the GARCH model in excell so I need alot of sample data. So instead of me running multiple searches for the data, I was wondering whether anyone could add to my coding so it will download until the require sample data for the dates is download. (Maybe some type of loop) .

Thank You

Coding

Sub DownloadStockData()

Dim YahooStr As String
Dim Symbol, dwm As String
Dim StartMonth, StartDate, StartYear As String
Dim EndMonth, EndDate, EndYear As String

Symbol = TextBox7.Value
StartDate = ComboBox1.Value
StartMonth = ComboBox2.Value
StartYear = ComboBox3.Value
EndDate = ComboBox4.Value
EndMonth = ComboBox5.Value
EndYear = ComboBox6.Value
dwm = TextBox8.Value

StartMonth = Right(100 + CInt(ComboBox2.Value) - 1, 2)
EndMonth = Right(100 + CInt(ComboBox5.Value) - 1, 2)

YahooStr = "http://ichart.yahoo.com/table.csv?s=" & Symbol & "&a=" & _
StartMonth & "&b=" & StartDate & "&c=" & StartYear & _
"&d=" & EndMonth & "&e=" & EndDate & "&f= " & EndYear & "&g=" & dwm & "&ignore=.csv"

Workbooks.Open Filename:=YahooStr
Range("A1:G500").Select

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim a As String
a = TextBox7

Range("A2:A500,G2:G500").Select
Selection.Copy
Windows("Visual Basic (GARCH (1,1) Programming VBA).xls").Activate

Sheets("Sheet3").Select
Range("A1") = ("Historical Data for " & a)
Range("A4").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("table.csv").Close SaveChanges:=False
End Sub

Hi

I have a spreadhseet whuch has needs lots of autosums.

The code below shows what I am trying to do:

In the line makred "***" , I would like to know how you sum ranges using formula function. Please could someone help me.

Code:

Sub sum()

Dim cell As Range
Dim r As Integer
Dim s As Integer

For Each cell In Range([a5], [a65536].End(xlUp))
If IsEmpty(cell) = False Then
Set r = cell.Offset(0, 4)
Set s = cell.Offset(0, 4).End(xlDown).Offset(-1, 0)

'I want to sum sum from cell r to cell s

cell.Offset(0, 4).End(xlDown).Formula = "=sum( )" ***

End If

Next

End Sub

Hi

I need to delete (from excel with VBA) .xls file.
I've lost my mind. Can somebody help me?

THNX

I have a spreadsheet that get used in a number of countries.
Any I aim to keep the amount of VBA to a minimum.

There is current year cost data sorted via a pivot table into category totals.
Then the user is given the option to use this data in next years forecast.
So I would like to have a "TRUE or FALSE" value in a field from which I can
then manipulate the pivottable data.

The advantage of using true or false is that it changes language with each
user to their default.
Trouble is that I would like to make it a bit more friendly by using data
validation.
Each language's "Yes or No" can be got from the VBA XL constants for yes and
no stored in named cells in a hidden worksheet each time the spreadsheet is
opened. The issue is to show these named text values in the Validation list
but store TRUE or FALSE in the cell.

Can this be done?

Can't seem to get Excel to quit with: Application.quit

Can anyone help per the code below?! TIA!

Sub Auto_Open()

Dim PathSrc As String, PathDest As String
Dim srcList As Variant
Dim i As Long, sDest As String
Dim bkSrc As Workbook, bkDest As Workbook
Dim srcList1 As Variant, NumFiles As Long

PathSrc = "Y:SalesTarget Customer2005 Mainframe
Download"
PathDest = "Y:SalesTarget Customer2005 Mainframe
Download - Main"

Workbooks.Open "C:TargetSupplantSupplant.xls"

NumFiles = ActiveWorkbook.Worksheets("Sheet1").Range("D1")

srcList1 = ActiveWorkbook.Worksheets("Sheet1").Range
("B1").Resize(NumFiles, 1).Value
Workbooks("Supplant.xls").Close SaveChanges:=False

ReDim srcList(1 To NumFiles)
For i = 1 To NumFiles
srcList(i) = srcList1(i, 1)
Next

For i = LBound(srcList) To UBound(srcList)
Set bkSrc = Workbooks.Open(PathSrc & srcList(i))
sDest = bkSrc.Name
sDest = Left(sDest, Len(sDest) - 4) & "M.xls"
Set bkDest = Workbooks.Open(PathDest & sDest)
bkSrc.Worksheets(1).Rows(1).Resize(1000).Copy _
Destination:=bkDest.Worksheets(1).Range("A1")
bkSrc.Close SaveChanges:=False
Application.DisplayAlerts = False
bkDest.SaveAs bkDest.FullName, xlWorkbook
bkDest.Close SaveChanges:=False
Application.DisplayAlerts = True
Next

Workbooks("RAW VBA.xls").Close SaveChanges:=False

Application.Quit

End Sub

Hi

I'm a newbie to this forum and hope that someone may be able to provide a solution to what should be a simple problem. I used to have a good working knowledge of VBA some years ago, but haven't used it for ages and am very rusty.

I have a spreadsheet with 53 sheets - one for each week of the year, together with a summary sheet. Each weekly sheet (called, arbitrarily, 01 - 52) has its information in identical cells, reflecting cash flow for that particular week. Totals for each particular product code reside in column fashion in the same cells (say M30..M55) in each weekly sheet.

The summary sheet itemises the totals for each product code on a weekly basis in rows (i.e. one row for each week). Each product code column needs to pick up the formula for its respective week's sheet. I've uploaded a screen dump as a Word doc to try and explain: [VBA Question Screen Dump.doc]

On the summary sheet, cell B8 has the formula: ='02'!M27 which picks up the total for code 3200 from sheet 02. Likewise, E8's formula is: ='02'!M30 which similarly picks up the total for code 3201. And so on. As some items are subject to VAT (tax) and some are not, the gross figures in the summary sheets do not necessarily lie in adjacent columns.

By running the macro recorder (using sheet 2 as an example) I get something like the following:

    Sub TestSummariseManual()
'
' TestSummariseManual Macro
' Macro recorded 11/01/2012 by Paul Webster - manual recorder
'

'
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[22]C[8]"
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[23]C[5]"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[24]C[2]"
    Range("L8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[25]C[1]"
    Windows("Cash_Analysis_2012 (WITH VBA).xls:1").Activate
    
End Sub
What I need VBA to do is loop through each weekly sheet, pick up the formula(e) for each code and place it in its respective cell for that given week.

Obviously as each week's figures are input to that week's sheet, they will then automatically populate the summary sheet.

I hope I've explained this sufficiently and that someone will be good enough to offer me a solution.

Thanks and regards

Webbo


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