I would like to know where can I find a good resource to learn about VBA syntax. I have VBA books and there are some examples of code in there but when I'm trying to read someoneelse's code I just get into commands or syntax I don't understand. For example I would like to be able to go some site and decifer what this line (or it's parts) means: If(cnt < MAXTEST, sDigSep & String(MAXTEST - cnt, "9"), "")
View 4 Replies
Need advice/opinions of where to go 1st to learn all about VBA/Macro's from a novice point of view.
View 3 Replies View RelatedI would like to build on my Excel VBA but not sure where to start. P.s I want to aim towards reporting side of things so make reports look good, copy data from sheets onto another sheet, formatting all done through vba, macros
View 8 Replies View RelatedI´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta()
Set wba = ActiveWorkbook
On Error Resume Next
If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then
Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt"
Else
Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls"
End If
Set wb = Workbooks("Projekt.xls")
wb.Activate
If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then
MsgBox "overall doesn't exist!"
Else........................................
I create excel file to collect and process huge data , it contains a lot of macros , when i run it for more than 2 times error message appears " Excel cannot complete this task with available resources , Choose less data or close other applications''
And after i press ok it continue working but charts not updated until i restart excel , and if i restart excel every time i run this macro this error not appears.
How I can release excel resources when my macro finish it's work.
I currently have a macro that opens up a specific workbook, copies data from that workbook, and then pastes the data into the control workbook. I am using this macro for a few workbooks but for this specific file I am getting the "Excel lacks resources..." error. Even when I don't run the file and just try to open both files up at the same time I get this error. I realize the best solution would be to split up the file I am copying from but I don't wish to turn to that yet.
View 9 Replies View RelatedI'm having a few issues with a macro I have written (something I am relatively new to, I might add). Whenever I run it, I get an error message of "cannot complete this task with available resources" - I am currently using Excel 2003
What I am trying to do is cycle though a list of 401 vehicle numbers (the WBSs), using each individual WBS to filter data in another worksheet (sheet "MC01"), and then copy a range of this filtered data into a separate, vehicle specific worksheet (starting at sheet "P1" and continuing to sheet "P401").
The code would run fine when I kept the Field 9 Filter Criteria1 value a constant WBS number, but when I introduced the phrase to use the different values on the WBS sheet (Criteria1:="=" & WBS.Value), the macro would run perfectly for half the vehicles before giving the error message and bugging out.
I thought this could be solved by dumping the memory on each loop by using Set WBS = Nothing but this doesn't make any difference.
Code is as follows:
Sub Update_MC01()
Dim I As Integer
Application.ScreenUpdating = False
Sheets("WBS").Select
Range("B2").Select
Set WBS = ActiveCell
Sheets("MC01").Select
Rows("1:1").Select
Selection.AutoFilter
Sheets("P1").Select
I understand that 401+ worksheets is a lot for any workbook to handle, but the fact that if I set Criteria1 as being a static WBS vehicle number Excel has no issues with it, and everything cycles fine. It is the addition of the phrase "=" & WBS.Value that causes the hiccoughs. From my very basic knowledge of VB, all I can assume is that Excel is storing this "WBS.Value" in it's memory on each loop. Am I right in thinking that the phrase Set WBS = Nothing is the right thing to use to empty this memory?
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
I have a (few) spreadsheets that I am using to gather data. One spreadsheet has over 9000 entries with 20 items for each entry (9000 rows, 20 columns). I use this as my source. In another spreadsheet I have the same column headers but, except for the one column that I enter manually, the rest of the columns are loaded from the source spreadsheet via a VLOOKUP function. As you can see there is a lot of data and a lot of action going on. Needless to say I have run into a conflect with the amount of resources I have available. I keep getting the message "Excel cannot complete this task with available resources. Choose less data or close other applications."
Since I have no other applications open, and I can't use less data, how can I increase my resources. I have been told that Excel allocates resources to a set limit regardless of the amount of ram or other memory you have. Is there any way to increase this?
I need to pull a data from 3 different SharePoint lists and present it in a new data sheet. So far I know how to create data connections and I am able to see everything in 3 different worksheets but in the same workbook. Also if we have the same Project and IT number we should pull information just from the SharePoint 1 list.
View 1 Replies View Relatedi would like to find out about:The different essential building elements for different chart types (like 3DBubble requires XValue, Value, Name and BubbleSize, for example, but what about clusteredColumn and the others?)How to address the building elementsThe optional building elements (changing background color and stuff)
View 2 Replies View RelatedI need to work with a spreadsheet with 15000 rows of data. By the time I link this file with my final file and add formulas, my file has reached a massive size and the following pop up error message appears.
View 3 Replies View RelatedA pop-up window with the next message: "insufficient resources to show all" is displayed when I try to use a combobox in a worksheet.
Details:
I have been using excel 2003 in a Pentium IV 1 GB Ram to run a large workbook (at least 40 worksheets) that uses a combobox to find a price from a database. The workbook contains lot of images and some single macros.
The error appears after I opened the workbook and I tried to use the combobox. Finally, after various clicks, I get the value requested but the pop-up window doesn’t disappear.
If I do not use the combobox I do not receive the error.
Done:
I have done unsuccessfully all the suggestion that I found on the web (Google, msn and AltaVista), and of course the Microsoft forums. I tried to clean memory, erase temporary files, increase memory values and so on.. but nothing seems to fix the issue.
I also tried to run the workbook in 3 different PCs – and 3 different RAM sizes - getting the same error.
I've been working with links the past few days and have been experiencing the subject error message. What do I need to do to avoid the error?
View 9 Replies View RelatedI have a large spreadsheet that uses the lookup() to return data from two other large sheets.
The exact formula I am using is:
=IF(B1<>"",IF(ISERROR(MATCH(B1,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$A$1:$A$16374,0)),"NA",LOOKUP(B1,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$A$1:$A$16374,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$C$1:$C$16374)),"")
I have this same (or very similar) formula repeated about 3300 times in my main workbook. When I try to update links I get the following error. "Excel cannot complete this task with available resources. Choose less data or close other applications." If I "Open Source" it will update the links but when I close the "Source" I get the same error..."Excel cannot complete this task with available resources. Choose less data or close other applications." The source will close eventually and my links have updated but I can not save the work book. Much smaller versions of the same sheet work fine. What is the limit number of such links?
Excel 2007
Trying to insert a row and window popped out -Large Operation. " The operation you are about to perform effects a large number of cells and may take a significant amount of time to complete. Are you sure you want to continue?" I pressed ok , then, says "Excel cannot complete thsi task with available resources. Choose less data or close other applications. "
I am trying to copy 8 adjacent cells in a sheet and paste them on another sheet as a picture (using copy picture method). I could do that 3/4 times, but after that I started getting the insufficient system resources error.
View 7 Replies View Relatedi am looking to create a small table from the attached worksheet that will show how many users there are for 3 different locations, the user names are in column a and the locations in column c. the thing is, the actual worksheet i am using in work contains almost 1000 lines, and is being updated daily, there can be multiple entries for a person for projects etc.. and someone can even be in the availability and pto area's without being in the main project area. each resource can only be on one location so that will not change.
also, there can be blank cells for resource which should not be counted. i was wondering if there was a formula or a macro that would count the distinct number of names and reference them to the location and give a count of resources by location?
I have VBA code that attempts to delete an entire row from my worksheet:
Code:
Cells(3,1).Select
Selection.EntireRow.Delete
This works fine on small data sets, but on larger data sets it gives me the error message, "Excel cannot complete this task with available resources". This happens even when I try to do the deletion manually (without VBA code). Clearly, the code itself is not the problem.
My document has about 250,000 rows and 2,500 columns. While this is big, it is significantly smaller than Excel's documented limit of 1,048,576 rows and 16,384 columns.
I am using Excel 2007. My computer has 2GB of RAM but even when I try it on a computer which has 8GB of RAM it gives me the same error. If I "ClearContents" instead of "Delete" it works fine. For my purposes, however, deletion is entirely necessary.
I have the following code for a sheet in my workbook that has 3 charts:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlCalculationManual
ActiveSheet.ChartObjects("RdteObs").Chart.SetSourceData ThisWorkbook.Names("GSumRdteObs").RefersToRange
ActiveSheet.ChartObjects("RdteWip").Chart.SetSourceData ThisWorkbook.Names("GSumRdteWip").RefersToRange
ActiveSheet.ChartObjects("RdteExp").Chart.SetSourceData ThisWorkbook.Names("GSumRdteExp").RefersToRange
Application.Calculation = xlCalculationAutomatic
End Sub
but whenever the sub runs, I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." Does anyone have an idea what's going on?
Im having problem with If/Then/Else Statements Not sure what the problem is. I have a text box a user inputs whatever in to create two new tabs one is the tab name then the next is tab completed. It isnt liking my syntax I have here.
View 7 Replies View Relatedif I copy and Add it created a new workbook not the current on im in. What am I doing wrong?
View 5 Replies View RelatedGetting a syntax error which and I'm not sure what I've done wrong. This is the bit thats causing the problem:
View 2 Replies View Relatedtrying to write a formula in a VB to some cells:
View 3 Replies View Relatedwhat is wrong with this syntax:
View 2 Replies View RelatedI would like to have an email generated when a cell changes to a particular value. I've developed the macro to send the email with a saved attachment.
When I try to OJT-Engineer the text for the module, I can't get it to recognize the macro. It's a syntax issue, it seems.
What I need is for my module to initiate a macro when a cell changes to a value.
This is the code EXACTLY as it appears:
A theoretical question following an empiric result.
For the command “SendKeys”, for instance, take these two lines:
What is wrong with the syntax of this formula?:
COLUMNS(INDIRECT("AverageDemand!$A7:"&A$7))+4
I'm getting #REF errors and I can't work out why...
I have the following code that loops thru and puts the word 'TEST' in column J if column B has a TEXT value of '020'
----------------------------------------------------------------------------------
With Range("J1:J" & Lastrow)
.Clear
Range(.Cells(2), .Cells(.Count)).Formula = "=if((b2)=""020"",""TEST"","""")"
End With
----------------------------------------------------------------------------------
Questions
1) why do I need double quotes "" "" on every argument in the function? Is it because I'm working with TEXT data?
2)I want to use multiple ifs (ie if cell b2 equals '020' OR '030' then put the word 'TEST' in col J). How to change the syntax to do this?
I know this syntax isn't right.
I have a form and a combo box that I want ot fill the items in from cell F22.
Here is my
Sheets(PowerAnalysis.xls).Cells("F22").Value = ComboBox2.Text
What is the best way to get this done? I have searched here and on the web and I can't seem to find any straightforward answers.
