I currently have the below macro:
If Cells(1, 3) = 2 Then
For a = 1 To 336
b = Round(a * 2 + 0.99)
Cells(b + 0, 3) = a
Cells(b + 1, 3) = a
Next a
MsgBox " Cell C1 is not equal to 2"
End If
where the intent is that whatever cell A1 is equal to, that is how many times over it will duplicate a number in consecutive cells before moving to the next number, ie:
1
1
2
2
3
3
etc to 336.
How can I tweak the code so that if I change the data in cell A1 to 9, I don't have to go in and change the macro?
I am wondering if anyone as created a what to do word art, using a input variable.I.e in may example I have a person, Suzie Smith who is an Chief Manager, when I box requires her signature, I would like for it eo be imprinted going diagonal across the box. Is this possible?
I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:
I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:
Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?
I am trying to create a matrix in VBA with variable matrix size. The matrix I need will be anywhere from 3 to 9 columns and 15 to 30 rows. I want to set the size as variables and then create the Matrix using these variables, similar to:
rows = 4 columns = 25 Dim XMatrix(rows, columns) As Variant
I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements: 1) filename 1mm 2) filename 2mm 3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
how do we do something like Range("A1:B2") using an index for the cells. For example Range(R[0]C[0]:R[i]C[j]) which doesn't work. I know about Cells(i,j), but that only returns a range object of a single cell.
Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?
For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro. But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile. Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile. The next target file in the folder is opened and the actions are repeated in this second target file. For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook. The error message i get is: "Run-time error 91:Object variable or with block variable not set." When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
When i try to run the code below i get the error message - object variable or with block variable not set-
Sub REFRESHXX()
'LIST Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select Selection.AutoFilter Field:=1, Criteria1:="1" 'SET RANGE Dim sFormula1 As String Dim sFormula2 As String Dim sCell1 As String Dim sCell2 As String Dim sSheet1 As String Dim sSheet2 As String Dim r As Range Dim MyRange As Range 'for testing
With Sheets("Points") sFormula1 = .Range("CY1").Formula sFormula2 = .Range("CY2").Formula End With
'FORMULA IN R1C1 STYLE strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))" 'ENTER FORMULA IN ALL CELL RANGES r.FormulaR1C1 = strFormula 'REDUCE TO VALUES Dim ar As Range 'an area is a range For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells ar.Value = ar.Value Next ar
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String Dim stopDate As String Dim startRow As Integer Dim stopRow As Integer startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)") If startDate = "" Then End stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)") If stopDate = "" Then End startDate = Format(startDate, "dd/mm/yyyy") stopDate = Format(stopDate, "dd/mm/yyyy") startRow = Worksheets("sheet1").Columns("A").Find(startDate, _ LookIn:=xlValues, lookat:=xlWhole).Row stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _ LookIn:=xlValues, lookat:=xlWhole).Row Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.
I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)
I have a workbook with 20 worksheets. I would like to create a Macro on each sheet that when you click a button it creates an email to a list of recipeients for each sheet.
I have a text document to which i need to do some changes and then turn it into CSV. Here are some lines of what the document looks like and below i will show you what i need it to look like. So i open the document with Excel and next I'm not sure how to do what i will show you below. Original:
I am working with excel 2003. I get a workbook sent to me via Email that has 12 tabs on it and each tab has to be printed out. The fastest way that I know, to get this done is start with tab 1; hit alt+f, u, alt+f, enter, Ctrl+P, enter, Ctrl+page down ( to move to next tab and do it all over again), each tab is a slightly different width so I am constantly have to use "Fit to Page" but each tab will fit on one page easy.
I was wondering can I use a key stroke macro to print this workbook. I actually get 5 workbooks like this every night, it does not really take that long, maybe I am just being lazy, I was thinking that I might be able to use a key stroke macro and then name it for what ever workbook it works with (workbook 1 print ) but I don't no if this will work or if there is an easier way to get the printing job done.
I can't upload the workbook but I could create a simple say 5 tab workbook that has no data in but has same formatting and upload that if you need to see what I am doing.
I want to create a new excel application through macro. I basically want to transfer few columns from the parent workbook to new workbook. but these new workbook must be contained in a new excel application.
Ok, I have a situation that I would love to have some help with. I have a command button that when clicked takes the user through a few questions via input boxes. The final objective that I need help with is a large typing area. Either the input box typing area needs to be larger or I need a text box to appear. After user is done typing and presses ok, I need what they typed to go into an area where I have merged some cells/rows to make a paragraph.
I am a new user into excel using macros how I can automate a worksheet, after I have about 300 rows as expenses of which I want some of these to distinguish groups. Note that the name of expense is not stable for each time which i download the file, so based on the name of the output, i would like to write a macro so that running this macro to enter a number in column B for each expense which i want to group it so that then using filter i can issue any group. Note after filtering i would like to have the total in column C. Please see below extract of spreadsheet.
I need to create a macro that will move information from a range of columns and then insert them under existing information in another column.
here's my example, this is what I'm starting with:
There are only 3 rows in this worksheet and I have numbered the columns just for reference in solving this problem: Anyway, this is how it looks when I import the records: ...
I have a macro that opens XL workbooks runs a macro, closes the workbook and opens the next workbook until completed, this is all fine.
Now for some reason and it has me stumped it is creating a file called RESUME.XLW a workspace , my problem is how do i stop this from being created i have not programmed it nor want it, it seems to be some automated thing i have never seen before and it is driving me balmy.
I'm not too familiar with VB, and I'm trying to narrow down why my macro (and how) is creating a second, duplicate chart. I only need the chart to be created (and the corresponding data fields on the 3rd worksheet) once.
I have macro for creating months. Code worked fine, but now I use merged cells in range where days should be created, and code no longer works (Error : "Cannot change part of merged cells").
Tried with unmerging cells, but then code inserts days in shorter range that I need, so code needs to be changed.
Each macro is assigned to its own button, Clear and Restore. What I'm trying to do is make it so that the macro attached to the Restore button will not activiate unless the Clear macro attached to the Clear button is clicked first and visa versa. I can't see to find anthing quite like it in my research.
So I am starting to get ambitious with Excel now and am looking at creating a macro that will automatically create a pdf file of several worksheets at the same time. However this is my problem. Using the current version of Excel there is a wonderful record macro button, however when I try to include a function that would create a PDF, it says that I need to save any changes before creating the PDF. However when I save the file, I have to stop recording which defeats the whole purpose of creating the macro in the first place.
How can I use the record macro function to create my pdf macro without learning visual basic? If I cannot, how to write what it is that I am trying to accomplish?
I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.
Sample Data:
Code: State Characteristic Value07 Value08 Value09 Alaska A 11 23 45 Alaska B 13 22 98 Alaska C 99 91 21 Alabama A 23 14 11 Alabama B 44 62 76 Alabama C 75 47 21[code]....
