I stumbled on this and wasn't sure why the code was placed on different lines and how the highlighted sections in red affect the basic round function.
ColorCompare = Round( _
(111111 + Blue + Red + Green) / (Green + LenPaint) _
, 0 _
)
Adding ' in the beginning of a line converts the rest into comment line. I wonder if there is an easy way to convert a huge area into comment line to try something on code. I couldn't find such an option in the menu.
View 2 Replies View Relatedcreating breaks in the line this script now simply turns the line black.
View 9 Replies View RelatedI have 2 columns in Excel wich havn't the same amount of lines.
One colom is 65.000 lines and the other one is 64.000
I am looking for a option to delete 1 line after each 65 lines.
Is this possible?
I need to create a line graph that has 2 lines for each person, one with the amount, one with the points. The X-Axis is by Weeks going up. How can I do this without making a separate series for each? I've attached an example.
example.xlsx
I have is a list of the over 500 people with one line per college degree. Some have up to 4 or 5 degrees. Each have SSN as unique identifier.
I need to consolidate into one like per person with multiple columns one for each degree.
See attached : Test Degrees.xlsx
I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job.
First of all I would like to insert 2 lines under line 6 in the active sheet.
I then need to drag the formulas found in line 6 down over the two new lines.
Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"
I import data into an excel spreadsheet where a unique job number may have multiple costs against it. These import as separate lines, repeating the job number.
The data imports on a sheet called 'DataImport'. From that sheet I transfer some of the imported fields onto another sheet.
What I need to be able to do is transfer the job number and a sum of the costs onto the other sheet.
The data is run regularly and I delete all the fields on the DataImport sheet in the macro each time the import is run.
I have 2 lists of codes with values tied to them, columns A & B, and columns C & D. Column A is a list of the codes in use, and column C is every code possible, I need to line up the codes and shorten column C to match column A, while retaining the value that is tied to each code so that I can compare Columns B & D easily.
View 2 Replies View RelatedSee the code below. It works fine but in the report that gets printed off, it doesn't display grid lines and line numbers .
[Code] .........
I have been trying to chart temperature differences over 10 day's time for six cities using a line graph. For some reason, the lines are all clustered at the bottom of my graph, with values of 0. My spreadsheet is accurate, without any blank cells. I'm using Excel 2010.
View 1 Replies View RelatedOne has around 129 lines for the first years 2006 in sheet1.
The other links to the first 129 rows. Then I've linked the top 129 in sheet2 130 down for 2007 and so on for 5 years.
How would I add a line in Sheet1 then would insert 5 lines in sheet2 keeping the same order.
e.g
Sheet1
Name Rev Year
John £120 2006 Row 5
Jack £150 2006 Row 6
Sheet2 (linked)
Name Rev Year
John £120 2006 Row 5
Jack £150 2006 Row 6
John £130 2007 Row 134
Jack £160 2007 Row 135
John £140 2008 Row 263
Jack £160 2008 Row 264
John £150 2009 Row 392
Jack £170 2009 Row 393
John £155 2010 Row 521
Jack £180 2010 Row 522
The Rev changes by formulas in Sheet 1
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
I am brand new and I don't know a ton about macros and programming in Excel.
What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.
Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is difficult.
See attached : TEST2_2014 Sales & Commission Spreadsheet.xlsx‎
I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
View 2 Replies View RelatedI merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.
As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)
All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:
company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx
I atteched an example of the full list and the result i want
I was wondering if anyone knows code that will check to see if there is a certain value in a cell, and if there is place a "1" in another cell in that row.
View 2 Replies View RelatedMy VBA code will copy and paste several workbooks into master list. The cells to be pasted will be starting on cells(13,9) until cells(13,501) or Range("I13:SG13"). While the max row will be determined by Cells(Rows.Count, "F").End(xlUp).Row
Here is the progress bar code I found in internet, maxrow/column has been changed to my requirement but how to place my VBA code to this script? the one highlighted in red, I guess it
Code:
Sub ShowProgressInStatus()
Dim Percent As Integer
Dim PercentComplete As Single
Dim MaxRow, MaxCol As Integer
MaxRow = Cells(Rows.Count, "F").End(xlUp).Row
MaxCol = 501
[code]....
Upon user making selection from data validation list in Active Row Selection & Column A : i would like to do the following (is possible)
,,,,,,,,,,,,,,,
Place formula in Active Cell Row & Column S
=MT4|BID! (followed by cell A1 but replace the "/" with "") followed by "m"
I have tried several combinations but with no success.
Also - where would i place this code so that it automatically updates the formula in Column S as soon as the user changes selection in Cell A?
I have 26 ranges defined on my worksheet sheet3. Each range is 10 rows high and 4 columns wide. On my sheet1, I'd like to be able to go automatically to a specific range on sheet3 (I need to provide the choice to go to any of the 26 ranges), selected perhaps by a combobox. The ranges are building occupancy groups like A1, A2, B, E, R1, R2, etc. (26 of them). Then I'd like the user to look at each row in that particular range, select one, and then have the four values in that row placed in corresponding cells on sheet1.
I have this set up now using a four column combobox, and it works just like I want, except that only the first column of data is displayed after a row is selected. If I could get those four cells of data as they appear in the combobox inserted on sheet1, I'd be fine. But I can't. So I'm looking for a work-around. Basically, my goal is to get a specific row of data (4 cells wide) from my named ranges copied into cells on sheet1. But I need to be able to get to the correct range automatically.
find a way to place a value of 1 (or a text "email sent: mm/dd/yy") in a specific cell on each row when an email is sent out through some VBA code I currently have. Then when the workbook is opened on another day, the code will look to see if that cell (target) is populated so that it doesn't trigger a second, third (repetitive) email every time the workbook is opened.
The spreadsheet contains rows of many clients, and growing every week. The code needs to be designed to search through each row, along a specific column (lets suppose column R beginning at row 7). When it finds the target rows blank, the code will turn each target cell red, as well as trigger a message box alerting that follow-up action is required.
I have created a user form and I am trying to get the active control colored. I am using the code from Mr Excel's VBA book - starts on page 454, and I have entered all the code, but now when I activate the user form I get an error ....."Compile Error. Invalid attribute in Sub or Function." and when I click OK, it takes me to this line of code.....
Private WithEvents objForm As clsCtlColor
In the book, this is where I am supposed to start entering the code "behind the userform" rather than in the class module. So, I assume this means that this code goes with all the other code for the user form (in VBA project click on form, then view code). Am I wrong? Should I be adding a module? Not sure what I am doing wrong.
Here is the code I have in the class module....
Public Event GetFocus()
Public Event LostFocus(ByVal strCtrl As String)
Private strPreCtr As String
Public Sub CheckActiveCtrl(objForm As MSForms.UserForm).......
Have created a userform for people to use to populate a spreadsheet and based upon the values filled in cells, and date segregators, formulae determine whether the line of data is classified as new / increased or reduced.
Obviously I can sort this in excel quite easily by this criteria.
At the moment it is about 500 rows deep,and is sorted by N/I/R.....
What I ideally want to be able to do is create 3 new worksheets, N/I/R and then select all the N's and bung them in sheet N, row 2, and then repeat selecting all I and then all R, and pasting in respective sheets.........
Obviously it can be done, but I am far too rusty to do this.
At the moment, all is sorted in one worksheet, alpha ascending...
I have two lines of code which I want to consolidate into 1. for example
Sheets("Snapshot").Select
Range("A1").Select
I've tried combining but get errors.
can they be combined
My excel program on my home computer has a default number format so that when I type in a number such as "1", the spreadsheet displays 1E-20. If I enter the number as "1.0", the display will show correctly. I have tried changing the formats for the spreadsheet, but I still cannot get the program to work correctly. I tried reinstalling office, but nothing changed.
View 6 Replies View RelatedCode:
Sub Final()
Dim NameCell As Range
For Each NameCell In Sheets("! Names").Range("B1", Sheets("! Names").Cells(Rows.Count, "B").End(xlUp))
[Code]....
I want to pull data from ! Names. This code already Renames a new sheet from the ! Names sheet and also puts there name in cell C2 and phone number into cell C3 of the created sheet. I want to put more data from the ! Names sheet. How would i go about adjusting current code to do that?
I've written a piece of code that is so long, I have to use the scroll bar to see the whole of it, which isn't very user friendly
if there's a way of splitting long lines of code over say 2 or 3 lines, so I can read the whole thing without having to use the scroll bar? I've noticed some people use _ at the end of the code and then continue writing on the next line, but when I do this, I get an error message saying
"Compile Error : Expected : line number or label or statement or end of statement"
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies View RelatedI have spreadsheet application which have a ton of sheets and macro into it, and i work on devlop and program it , and add new feature and codes
i wana Module_Macro to find and replace lines of vba code and replace it with lines of codes .
i know ,i should use vbide and vbcompnent , but i dont know from where to start ?
I have the following line from my code of which I am having trouble with I get an error:
If Left(IRange, 1).Value "1" Then
I am trying to find the first string in cell range ...IRange ... I know the Left function = Left(string, length) but can I reference a Range or perhaps cell location within the string argument??? This can be done in excel formulas, but can it be done with VB code??
IRange = Cells (iRow,iCol) and value in cells are Long
My goal is to try and find out how I can use my excel formula:
Left(D3,Find("1",D3)) and impliment this to my above VB line