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
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 RelatedI 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 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.
i want to import data from sheet2 a1 from sheet1 a1 continously. now problem is when iam importing i shouldnt loose my previous data. eg: at time 11 when i import data to sheet2 a1 from sheet1 a1 with value 9. and at time 12 when i import data to sheet2 a1 from sheet1 a1 with value 10. value 10 will be over wrting value 9. here i shouldnt loose value 9. i want values comming afterwards should come to next row.
View 3 Replies View RelatedAs part of a research project i need to extract as much data as possible from a webpage. The problem is to access each table i have to follow lots of links which I can't get to work automatically.
Its from a greyhound-data.com. So an example would be I want to extract all the racing stats for every dog that raced in swindon between 1st jan 2010-1st feb 2010. When i put it in the search engine I get 512 races in a table. I have to follow the link on name of race for each race.. Then I have to follow the link for each dog in the racers box, 6 for each race. Then when i'm on the dogs page theres a link saying on the top of the page saying a nember of races eg. 67races (number will vary for each dog). If I press that I get a list of all the racing stats for that dog. Its that table i need. But i also need the dogs name as a constant column. I need the table for each of the 6 dogs for each race out of the 512.. So in total 3072 tables.. It a lot of data and I have no idea how to get it?!
My biggest problem is I don't know how to say follow the various links. And I don't know how to loop multiple times - once for each of the races in the original list and then for the 6 dogs.
Currently I work on a file which will be more like a form which will be filled by user and after he or she hits the button, the filled data will be written or sent to another Excel file or even Sheet in the same workbook which will store the filled data in database like form. The same as some web form which will post the data to database.
In other words, the workbook contains to sheets, one with forms to be filled in (cells) and one with specified columns. After filling in the cells on the first sheet and hitting the button, it will trigger the macro which will send data to second sheet and post data from each cell to appropriate column.
i want to searh from data sheet by writing a formule to black area.
We will write id number to yellow area.
When we write value to yellow area, the formule in black are will look column of " d ".
If it is " machinery" or " with lifting point " , the formule will search only according to id number.
If it is " lifting gear ", the formule will search according to id number and capacity simultaneously. And the value will be come up in black area from column of "a".
If there is no id number in data sheet, " no certificate no" will come up im black area.
I need in XL for a macro to run, open my word file, copy the entire thing and then paste it back in XL.
So leaving it general, Word.doc and Excel.xlsx
It's all because my company's POS system will only export to Word but everything we do is in XL.
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8
What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want
=sumif(A84:A89;"x";'X-ref'!T4:Y4)
but it doesn't work since the first range is an column-array and the second range is an row-array.
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:
Range("E7").Select
ActiveCell.FormulaR1C1 = "0"
Selection.AutoFill Destination:=Range("E7:E21"), Type:=xlFillValues
[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.....
View 9 Replies View Relatedif 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
AModDoc = ActiveDocument.Name
Documents(AModDoc).Bookmarks("Line1").Range.InsertBefore bname
Application.ScreenUpdating = True
End Sub
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? ...
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..
i.e
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.
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
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 RelatedI'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).
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.
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 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:
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.
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 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.
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............
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
'range("n5).value
Else
'range("b3").value
End If
Ok my opening Date for an account is for example 200204 (that is the way it is written in the database for all accounts YYYYMM) Now what we want to achieve is to have a macro that fills out a column in a spreadsheet taking into consideration the opening date until it reaches lets say 200601.
so this is what the result should look like
A
1 200204
2 200205
3 200206
.
.
X 200601
how do I let the Macro know this is a date so as to add a month until it reaches the final date. I've managed to create a loop but I know it probably isnt the best way to write it
Dim lCount As Long
Dim lNum As Date
lCount = 0
lNum = Sheet1. Range("D2").Value
Do While lNum < 200601
lNum = lNum + 1
lCount = lCount - 1
Loop
