I have an excel spreadsheet that I am trying to format. Each time the spreadsheet is of the same format but with differing amount of numbers. I.e. sometimes it will be a set of 3, the next time might be 6.
I want to write a macro that will find a set piece of text, move it offset(-1,2) and then find the next one.
For example....
I have the onerous task of dividing up a series of steps in one cell into multiple cells. I tried the text to columns utility, but it only delimits once, not multiple times. Below is a data example:
"1) Load App
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) click on subcategory 'Administration'"
"1) Load App
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) Click on subcategory 'Administration' .
5) Click on any Organization name from 'Organization' section in the list.
Or click on 'Add Organization' icon in section 'Organization' .
6) Validate field 'Parent Organization' in 'New Organization' or 'Edit Organization' form"
"1) Load Documoto.
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) Click on subcategory 'Administration' .
5) Click on any Organization name from 'Organization' section in the list.
Or click on 'Add Organization' icon in section 'Organization' .
6) Validate field 'Tenant ERP Accessible' in 'New Organization' or 'Edit Organization' form"
1) Load App
2) Login using Administrator Access details .
3) Click on icon 'Admin' on the right hand side of page.
4) Click on subcategory 'Administration' .
5) Click on any Organization name from 'Organization' section in the list.
Or click on 'Add Organization' icon in section 'Organization' .
6) Validate field 'DMZ license key expiration period ' by click on checkbox or by 'Uncheck' checkbox"
I need to put each step into it's own cell.
I have written a macro, big cheers as it's my first, and it even works as I wanted it to.
Trouble is when you run it, it shows you all the steps it is making. Is there any way to stop this and purely for the end result to appear? It may well also have something to do with my PC needing more RAM to make it faster. Ever since upgrading (?) to 2007, my PC feels like it is stuck in quicksand.
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 RelatedRange("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.3"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.4"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.5"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.6"
etc . . .
Basically, I just recorded a macro to change the cell value. This code continues to 4.8 . . .Is there a more efficient way to code this??
Also, I'd like to add a time delay (say +-5seconds) between each step. So basically it would say:
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
I have a monthly report. Worksheets for each month, each row is a type of fruit (column A), and the number sold (column B). A new worksheet is added each month with the information. I also have a Summary worksheet - running total of all months: Type of Fruit (column A), Number sold (column B), and Total of all fruits - number sold. I would like a formula for the Summary worksheet - to match that fruit (column A) to any of the other worksheets - matching the fruit (column A), and bring back the number sold (column B). (note some apples are Fuji and some are Honey crisp - those would be totaled together)
ex:
Tab = Jan 14
Fuji apples3
Bananas2
grapes9
TOTAL14
[Code]....
I am looking for a macro that works like the ASAP Utility (Transpose data from one column to several columns in steps). To elaborate the work done by the macro it should transpose the values in a column to the number of steps that is user defined (Using InputBox) that is if there are 103 values in the column and the user enters the number of steps as 24 then the macro should transpose the data up to 24 columns and the rest in the next row up to 24 columns and so on unless the complete data is transposed.
For more clarity refer the attached excel sheet or the "Transpose data from one column to several columns in steps" utility of ASAP Utility.
I am doing a Regular Expression search on a string variable assigned to the HTML content of a Wikipedia search. However I am currently manually going to Wikipedia, searching for the term, saving the html page, opening the saved page with Notepad and then copying the content into a cell.
Can the above process be automated with VBA, how to assign the html content of a Wikipedia search to a string variable.
This works: row(x).select But this doesn't: row(x:"100").select. I also need to do substitute both beginning and end range with a variable: row(x:n).select And multiple ranges: row(x:n, a:b)
I've tried tried numerous combinations of quote marks and parentheses and nothing works. What is the proper syntax?
I have searched high and low for a macro that can move cell values to other worksheet based on a variable serial number. I have had limited luck with my search but nothing quite reflects my needs. Any help or suggestions would be great. The way that I envision it to work is simple to say but to hard for me to figure out. I must add that I am fair to good with macros; I am using Windows XP and Excel 2003.
Here is the concept:
-I am trying to track several vehicles, their status and there general locations using a variable serial number and an excel macro. The macro would execute the calculated data on sheet “My Taxi Cars” and the results would appear on a separate worksheet called “Location” that is in the same workbook. Please see attached.
-The serial number is comprised of a cell range starting at A2 thru L2 and then concatenates in M2 it would look something like this 123N1110011011. This serial number would allow me to do simple long-range analysis for each of the vehicles. Also in theory at the end of 30-day period I could “replay” my data.
The Breakdown for the alpha numeric serial reads like this:
1) The 123 is the number of the cab.
2) The “N” is for North, etc. and it would equate to a range of cells on a separate worksheet (“Location”).
3) The Ones and Zeros are just simple yes or no answers to the common questions in the Garage at work.
Once the macro is executed the result would be the taxis number (taxi number only, the rest of the number is stripped off) being placed the correct location based off the serial number. I have no idea if this is possible and I would like to thank you in advance for your help. Thanks.
I understand that there are about 1024 different combination's for a binary number that has ten place values. Maybe the answer is to use VLOOKUP or HLOOKUP, but then that changes my analysis scheme. But if you can just help point my nose in the right direction or show me with one example I can take it the rest of the way.
I'm trying to write a macro with a variable number or rows depending on the total number of rows the workbook has.
I tried like this:
Code:
Sub provaanova()
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range(("$J$2:$J$") & Range("J" & Rows.Count).End(xlUp).Row) _
, ActiveSheet.Range(("$J$2:$J$") & Range("J" & Rows.Count).End(xlUp).Row), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
and:
Code:
Sub Provaregress()
r = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$r") _
, ActiveSheet.Range("$K$2:$M$r"), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
But it gives me an error. The only way i was able to do it without error was:
Code:
Sub Provaregress()
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$J$2:$J$53968") _
, ActiveSheet.Range("$K$2:$M$53968"), False, False, 99, "ANOVA", False, _
False, False, False, , False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
But as u can see it has a fixed number of rows. ( I need variable number of rows detected on column J, and then run regression with K,L,M as depending variables).
I would like to create a VBA macro which would do the following:
There will be three columns A, B, and C. Each cell in each column will have a number with two places after the decimal. Each integer corresponds to a letter code. In the fourth column (D), I would like the letter codes from the corresponding three numbers in the same row to be combined in one cell as follows (if possible):
It is important that the macro only pay attention to the whole number, and ignore the numbers after the decimal. However, the numbers after the decimal must remain in the chart.
A
B
C
D
[Code]....
I am trying to build a macro to work with a template file.
The template file has set sheet numbers and names (with one exception, see 4) below).
There are close to 40 sheets in all.
Some sheets are never printed.
Some sheets are always printed.
Many sheets are printed only if they are used.
Most of the sheets are 'break out' sheets and a variable number of them will be used.
So... Here is what I'm trying to accomplish.
1) Always print Sheet4(Overview1)
2) Always print Sheet6(Overview2)
3) Print Sheets 11 thru 40 IF value in cell G50 on these sheets is > 0. Note that this cell has a name (Total) and I would like to reference the name if possible. (It could happen that further evolution of the Workbook moves the cell up or down a row.)
4) Print Sheet38(Data Sort) IF it exists. Otherwise, print Sheet1(Data).
If the information on the Data sheet is limited (eg only 1 or 2 pages), we print it off directly. If the information is more extensive, we copy it to a new sheet and sort it (Data Sort).
I can achieve 1) and 2). I'm not quite sure how to go about 3) as I'm still not very good with macro loops. 4) I haven't tackled yet.
I am working on a macro where I am creating a formula to string together some text columns and then copy the formula down the entire column. The data source I will be performing this on will change in number of rows period to period. The data would be in columns A,B & C and the formula is in D. The formula in D is stringing together the data in AB & C and then I want to copy and paste that formula down to the bottom of all of the data. What would the code be for the copy and paste with variable rows?
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies View RelatedHow to plot a step graph?
I have attached a drawing by illustrating the graph that I wish to plot.
Attached Image : jpg Capture.JPG‎
I am trying to produce a list of numbers (preferred Sheets Function and not VBA).
Every group should be of 5 Identical Numbers - starting with 2 and step of 2.
(An Example is shown in the attached picture).
I would like to produce that list with the help of ROW() and all available build-in Functions.
If possible, I will appreciate very much some explanation as for the principal of producing such a list when, for inatance, the step is 3 , starting from 3, while every group includes only 4 identical numbers and the first cell, to present the first value, is A11 and down to A26 - presented here in Horizontal layout:
(3,3,3,3,6,6,6,6,9,9,9,9,12,12,12,12)
I have 2 columns of data with 7,000 rows that I am trying to manipulate in the following way. The first column contains a list of car manufacturers (not really my example, but involves the same concept). The second column contains a list of different models of cars. For instance,
Ford | Taurus
Chevrolet | Avalanche
Saturn | Sky
Ford | Explorer
Honda | Accord
Ford | Taurus
What I want is a list of 2 columns with UNIQUE car manufacturers in first column with a list of UNIQUE car models (comma delimited) in second column. So for this example, the first row could read " Ford | Taurus,Explorer". I don't want Taurus to appear twice. I got a lot of help from JBeaucaire from this post. I just can't add the ability to remove car models from the list also. Thanks in advance for all the help!
I would like to do is search for a piece of data within a different specific row, and return true or false if it is there. In the attachment cells D15 and D16 would be true if the ID is in the correct row, and false if it is not.
View 3 Replies View RelatedI have a project with over 600 steps which we are trying to track when each part progresses by having 52 weekly columns. Their are 8 users which we have allocated a colour so when they complete one of the steps they select which step they have completed and which colour they are. This works well but I would like to put the date that the change was made in a separate cell in that row.
My question is can this be done with a formula as I am in favour of doing this as soon as someone changes a cell in the week range. If not I will need to nut through a VBA sub routine.
I know that this formaul does not work but it is along the lines of what I am testing for: =if(sum(T55:BM55)>" ",Date(),0) where if any of the cells in the range T55 to BM55 have any value or letter in them then put todays date in cell L55 else do nothing.
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
I have a variable that can contain in no predefined order, a number of letters and one two digit number (code). I would like to extract this two digit number from this variable and based on its the value perform a task. I can test for the specific value of this number as there are only about 10 of these 2 digit codes. Im quite happy to have the routine test for the number by comparing them all one by one as there are only 10 it wount take too long.
The best scenario would be to extract the value into another variable which I then could use to construct a "select case" table from which I could then initiate the required task based on that number.
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.
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
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
'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1
End Sub
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
End Sub
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.
View 7 Replies View RelatedI am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.
View 1 Replies View RelatedHow to assign a variable of type Long to the below code.
1) Dim searchField as LOng
ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200=""" & searchField & """,$C$2:C$200))"
2) ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200= searchField,$C$2:C$200))"
1) code failed because I want the variable searchField to have a data type of LONG.
2) code failed because it seems like excel thought the search criteria is of same searchField.
How to convert date to general format and store it in a variable of data type Long. I.E if I convert 3/15/2008(data type of Date) to data type of General, it should be 39522. What is the VBA code for doing this conversion?
I have an excel file with a large amount of employee data in it and want to create a search facility that will run on variable search functions and display the information on the screen
I want to be able to enter variable search functions as follows:
Employee Number; shows all information on employee
Division: shows all employees in division (possible from a list of all divisions)
Appraisal Eligibility : Applicable shows all the applicable employees
Job Role: shows all the employees with the same job role (possibly from a drop-list of all roles)
I know its easier in Access, but all records in Excel as a legacy and don't have time to create an access database currently.