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)
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: http://www.avdf.com/apr98/art_ot003.html. A more detailed explanation how this trick works
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
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?
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.
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 Worksheets("Data").Select Range("a2:ac65536").Clear Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook..................
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:
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...?
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?
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.....
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 activedocument.name.
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 Letter1.dot" Application.Visible = True WdApp.Visible = True
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: .......
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? ...
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
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?
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?
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.
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.
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.
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.
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.
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.
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.
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.
