Slow Writing To Cells

Jul 6, 2007

A macro in workbook 1 creates a report by reading & manipulating data from two workbooks(WB 1 and WB 2).

A new blank report sheet is copied from WB1 to WB2. The data (some text, some numeric) is collected in a 20 element, single dimension array & written to the new sheet in 20 columns using a for/next loop. This is repeated for many rows(can be more than 1000).

The array is "erased" before each row. Screenupdating is turned off, calculation is turned off. The report takes longer & longer each time it is run - Why? With the one-and-only "Write" line "remmed out" the report takes just 2 secs however many times it is re-run - including the copying of the blank master report page. If the first report takes, say, 1 min the second time takes 2 mins & the 3rd - 3 mins etc. Reset is only achieved by exiting excel. The code in question is:- For col = 1 to 20 : cells(ro,col) = d(col) : Next col. (ro = the current row number which is incremented for each pass, d() is the data array)

View 9 Replies


Slow Writing To Large Number Of Cells

Mar 1, 2007

I heard there is an efficient way of writing large amounts of data at once to a worksheet. My program is writing alot of data (e.g. 600+ items in each row) and takes over an hour to run because it writes to each cell one-at-a-time. I seached this forum and couldn't find the answer. Here is an excerpt from an old 1998 article by Dermot Balson and William Mercer that used code to write to cells in large chunks but it doesn't work for me - I get a method failed message.

'to read in
Dim A As Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)

'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..

Here is the link to the article: A more detailed explanation how this trick works

View 4 Replies View Related

Writing In Two Cells One Word?

Apr 22, 2013

I'm trying to write a word or more but i want it to be in different cell.

Like: this sentence "Excel Program" write it in cell a1 but it slip to the cell a2. (so in cell a1 adn a2 this sentence).

View 4 Replies View Related

Writing Sub That Reads All Cells In Matrix?

Apr 11, 2013

How to write a sub that read all cells in the matrix ( a block of cells let's say: A5:F15).in two dimensionals. This is what I've done so far, but i got nothing:

Sub myarray()
Dim myarray() As Variant
myarray = Range("A5:f15").Value
End Sub

View 2 Replies View Related

Use More Than 1 Range Of Cells When Writing A VBA Macro

Nov 18, 2008

Just wanted to know if I could have a range or ranges which are split. The current range i have is N5:AV6. I need to miss out every 6th cell so am looking to do a number of ranges like this - N5:R6, T5:X6, Z5:AD6 etc etc. If this can be done could you show me how I would need to lay it out?

View 2 Replies View Related

Writing The Contents Of Cells Into A File

Apr 12, 2009

I have a bunch of values in column A, and I need to write (export) the entire column into a file called "ColumnA.dat".

create an Excel Macro Script that would accomplish that?

View 9 Replies View Related

Writing Formula In Dynamic Number Of Cells?

Feb 13, 2014

I'm trying to create a "master file" that can be used later (a tool other uses can pull up) on with datasets of varying size. I'd like to "data dump" a table of individuals with subsequent columns of various characteristics, then automatically compute various things. So then a user can dump their data into my file and "get the answer" immediately. I've seen some stuff on running one calculation on a dynamic number of cells, but let me illustrate why this is different. I want to perform a dynamic number of calculations, see below:

Each row (record) is a person, with some characteristics (numerical). Ultimately I have a few columns on the end with some computations (Excel formulas) I'd like to do. I'd like to do this for each person (that is, each row) in that data that I dump in. But the thing is, ahead of time we don't know how many people will be dumped in, and that changes (because I'd like this to be a tool...). So how do I tell Excel to compute things if there is a person in that row, but do nothing and leave cells blank if there isn't a person? Ideally I don't have millions of ifs testing for blank cells either. I'd like to do this with Excel functions only and not VBA, if at all possible.

View 1 Replies View Related

Subscript Out Of Range Writing To Cells In Loop

Oct 4, 2006

i'm trying to get my code to work. Basically my code opens up workbook and extract the relavant data into the main workbook. Previously Its just a single For loop and it works perfectly after changing to double for loop its stuck.

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim rCell As Range
Dim lCount2 As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook..................

View 6 Replies View Related

Slow VBA - Refresh Button That Enter Formulas Into Cells

Jun 13, 2014

I built a very basic refresh button that enters formulas into cells.

.Range("C12").Value = "=SUMIFS(Inventory!$R:$R,Inventory!$M:$M,'Frozen Dashboard'!$D$3,Inventory!$A:$A,
'Frozen Dashboard'!C$11,Inventory!$Q:$Q,'Frozen Dashboard'!$B12)"

Basically, I have around 50 of these individual cells that I am pasting the formula into.

Currently is taking 28-32 seconds to refresh these 50 cells.

Anyways to expedite these refreshes ?

I am also using the :

application.enableevents = false, and application.screenupdating = false

but still way to slow..

View 4 Replies View Related

Writing Value 0 In Different Ranges?

Nov 13, 2012

I have a table in which some of the ranges have to be set to "0" (write "0" inside, not delete) when I click on a button. All the other cells expect these ranges are locked. For now I use the very long way, with the following VBA Code:

ActiveCell.FormulaR1C1 = "0"
Selection.AutoFill Destination:=Range("E7:E21"), Type:=xlFillValues


There are more columns for which I have to do that, and it goes until row 500+ .. I used "Register Macro" to find out this code, that's why I guess it's obselete.

Is there any way I can do it, at least write all the ranges I have to fill with "0" next to each other instead of every time selecting, then inserting 0, then autofill the rang with...?

View 9 Replies View Related

Writing A Between Statement

Dec 15, 2006

I have a list of items in a pivot table where each item has a date. I want to see how many items occur for each month. I am trying to use the COUNTIF formula, but I'm having trouble figuring out how to write the criteria for the dates.

How do I write the criteria part to include the entire month?

View 9 Replies View Related

Writing Macro

Jul 3, 2007

i have an report in which i need to delete the entire row if the column contains values more than 250K, but the chanllenge here is; if any of the cells in the column contains a formula then the macro should not delete the row.....

View 9 Replies View Related

Writing To Word

Sep 25, 2008

if I have another instance of word open my macro will not work correctly. I am at a loss on how to figure this out. Also some times I get the error of virtual machine is not available when getting

Sub TestTemp()
Application.ScreenUpdating = False

Dim bname as string

bname = Range("B6").Value

Dim WdApp As Object, WdDoc As Object
Set WdApp = CreateObject("Word.Application")
WdApp.Documents.Add "C:TemplatesTest"
Application.Visible = True
WdApp.Visible = True

AModDoc = ActiveDocument.Name

Documents(AModDoc).Bookmarks("Line1").Range.InsertBefore bname

Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Writing Subroutine

Oct 27, 2008

in writing a subroutine that needs to ask the user

1) tax file number
2) Income
3) tax withheld

It then needs to use a function that i already made called 'incometax' that calculates 'taxpayable' and use that to calculate taxreturn (difference between 'taxpayable' and 'taxwithheld').

I then need it to output like this in a message box

Estimate for tax file number: .....
Created on ......
Total income: ......
Tax witheld: .......
Tax payable: ......Tax return: .......

View 9 Replies View Related

SelectCase Not Writing Value

Nov 15, 2008

The following code is supposed to write values from one file into another file - depending on the value of Current_Rider_Name. However, although the code runs and values are being picked up (this is checked through the line that is being commented out).

However nothing is being written to the file. Can anybody point me in the right direction? ...

View 9 Replies View Related

Writing An IRR Function In VBA

Aug 16, 2009

way to write your own IRR function using VBA.

Say if I call it Function MYIRR(cfs,per). Where cashflows occur in the periods specified..
Periods: 0 1 3 4 6
Cashflows: -1000 300 500 100 200

I'm having trouble making sure the initial cash outlay and then cash inflows assumptions hold (problem with the -/+ sign).

I've been told by a friend to try and use abs() function, 'For..Next' loop construct inside a 'Do...While' loop.

View 9 Replies View Related

Writing Udf For If Color Then Else

May 2, 2007

I am trying to write a UDF that will go like this:

IF A1 is Purple then B1=A1 Else B1=A2.

This is what I have written so far and I cannot figure out how to get it to work:

Function SumIfPurple( Range As Variant, Range1, Range2)
Dim SumAnswer
If Range.Interior.ColorIndex = 39 Then
Range1 = SumAnswer
Else: Range2 = SumAnswer
End If
SumIfPurple = SumAnswer

End Function

View 7 Replies View Related

Writing Text In A Cell

Jan 21, 2010

I have a problem, basically im writing in the cell but it keep going along, i want it so when i press enter it creates a new line in the cell, so i can have two lines of text in one cell. do i have to do something in the text settings?

View 2 Replies View Related

Writing Formula In Cell Using Vba?

Apr 8, 2014

I have a reference written as a string in cell "AS4", the string is results!A4

I want to write that same reference in cell "as5". I have written the following

[Code] .....

However, cell "as5" returns the string as =results!'A4', with single quotes around A4

Therefore the reference isn't working.

View 2 Replies View Related

Writing VBA Macro Or Function

Nov 5, 2007

I have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J).

If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class.

Should we write the macro or can it be accomplished by any Excel Function?

View 11 Replies View Related

Writing Array To Cell

Apr 15, 2009

I'm working on creating an array of letters. I'm then writing that array into a cell. The following code is for writing the letters "ABC" into a cell with an array.

View 4 Replies View Related

Writing Numbers Using Macro

Oct 16, 2009

I'm trying to use code to write the numbers 1 to 100 in 10 rows of 10

1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16......................

So far i have the following which works:

View 4 Replies View Related

Writing ActiveWorkbook Name To A Variable

Nov 17, 2009

I run the macro on a currently open workbook (name could be anything) and open another workbook (name known) and step through the sheets copying various blocks of data across to the first workbook. I need to Activate the 2 workbooks alternatively as I move data back and forth and move from sheet to sheet. What I can't work out is how to determine in code what the "unknown" workbook name is so that I can make sure I activate the right workbook in the code.

I tried Activating the workbook with the "unknown" name and setting a variable = ActiveWorkbook.Name. However the value of this variable changes during the code run when you activate the other book. Is there any way to collect this value and "freeze" it so it won't change as the code runs? At the moment I have got round it by ensuring the "unknown" book is the only one open before I start and refer to it as Workbooks(1) in the code.

View 2 Replies View Related

Writing Macros Sheet

May 6, 2006

i've a macro which will read the data from worksheets and copies that data
and paste the copied data into new workbook sheets.

it is copying the data into new work sheet. whats my problem is my original
application contains formatted data like having cloros and some of the
columns having big font size etc and some of sheets contains merged cells.

i'm totally having 8 worksheets with different layouts and the macro copying
the data into 8 new worksheets but the look and feel is not similar to the
original application. But i want as it is.

View 13 Replies View Related

Writing Data From Excel To PDF

Mar 9, 2012

I have been working with Excel, access and vba from 4 years. But never worked with PDF files along with vba.

Now I have a requirement to write data from excel to pdf. I know about using Acrobat distiller to create Post Script file and then converting into pdf files.

But I wanted to know if there is any way to write data directly into PDF from excel using VBA.

View 6 Replies View Related

Writing Data Into Array?

Mar 2, 2013

In a userform I have 2 combobox, a textbox and a checkbox. In Checbox1 have data in A2: A550 and checkbox2 have data in B1: B50. I want the number entered in textbox1 to be written - by pressing the SAVE button - to the intersection data of combobox 1 and 2 and once written to can not be rewritten. Therefore, if you enter a wrong number, it will check that checkbox and where to enter the cell number, to be colored in red.

View 6 Replies View Related

Writing To Sheet From Array

Sep 29, 2013

I have an single dimension array called "Test". I have a range of one column called "Vol8_P1". Both are 1200 in size. I want to write the array to the sheet.

This code works:

i = 1
For Each c In [Vol8_P1]
c.Value = Test(i)
i = i + 1
Next c

But this code give me all zeroes:

Dim Destination As Range
Set Destination = Range("Vol8_P1")
Destination.Value = Test

I tried resizing "Destination" in the second code piece and that did not work.

View 4 Replies View Related

Writing Vlookup Into VBA Code

Aug 15, 2007

I need help writing the VLookup function into a VBA macro.

I currnetly have a macro that generates a list of companies in column D on Sheet1. There could be a different number of rows populated every time the macro runs.

I also have a list of all the possible companies next to their e-mail address on Sheet2 (company in column A, corresponding e-mail in column B).

I know Vlookup can search sheet2 and populate the correct e-mail address on sheet1, but I want a VBA solution in which it will automatically see how many rows of companites I have, perform Vlookup for each company, and place the corresponding emails in sheet1, column E.

View 9 Replies View Related

Writing/modifying An Macro

Oct 18, 2007

Every month I download a report from a state agency that I need to reformat, create charts from and send reminder emails based on some of the data. The report is downloaded as an EXCEL workbook containing a single worksheet. The worksheet is preformatted as a printed report. I have no control of this formatting and I can not get the data as a .csv file. The report contains 3 sections. See detailed report description below.

What I want to do:
1.Is copy the 3rd section of the report to a new worksheet.
2.Sort the new worksheet by name, and date.
3.Filter some names based on resource type.
4.Create a new file, which will be attached to an email to my boss for follow-up.

Originally I used the built-in EXCEL macro recording function and this worked for a couple of months. Then the number of rows in the 2nd section varied and my generated worksheet failed to sort correctly, and I lost the column headings so my filter no longer worked. The results were not something I could forward. I do not know how to modify the macro to search for the literals which end section 2 or start section 3 so I can copy the correct data to the new worksheet. I am not a VBA programmer, just an old-fart trying to do some administrative volunteer support for an important program, and really do not want to become one.

Detail Report Description:
Section 1: Headings –contain fixed headings in merged & centered cells.
Row 1 – Report heading 1 in merged cells A1–I1
Row 2 – Report heading 2 in merged cells A2-I2
Row 3 – Report heading 3 in merged cells A3–I3
Row 4 – Blank row............

View 9 Replies View Related

If Statement When Writing A Macro

Dec 10, 2008

I am trying to write an if statement in my macro. It is not returning what I want. Can anyone tell me where I have gone wrong.

Dim D As Variant
D = Sheets("Instructions").Range("n3").Value
If D = 1 Then
End If

View 9 Replies View Related

Copyrights 2005-15, All rights reserved