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 Repliesi 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 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 am trying to add multiple lines in comment. problem is the text to this mutiline comments is stored in a range
Range("J15").Select
Range("J15").AddComment
Range("J15").Comment.Visible = False
Range("J15").Comment.Text Text:="Line1" & Chr(10) & "Line2"
(This is recorded). assuming that range "A1:A15" is where text for this comment is stored from a different VBA program.(15 number of line is the max i have programmed to accept). assuming there are 10 cells currenty full. how can i add this 10 text lines to a comment box.
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 _
)
creating breaks in the line this script now simply turns the line black.
View 9 Replies View RelatedIn my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
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 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.
Is there a way to comment code without having to introduce ' before each line? Very much like in C language, we use /* to open comment lines and */ to close comment lines. I know we can use _ [underscore] but I find it not very pratical. Both solutions for comenting in VBA aren't very pratical, specially when copying and pasting coments from a text editor for example.
View 3 Replies View RelatedI'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.
View 2 Replies View Relatedhow to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
The comment lines in Exel VBA editor is green by default, is it possible to change the color?
View 2 Replies View RelatedI have an Excel file with text records, 1000s of lines long Trancriptions of Customer sales reps and Customers, or Distributors. All data is in Collum A.
Each Record has 7-10 Entries
______________________
BILLNUM : 060501
ORIG : 12345678909090
REP : 45672222222222
AREA : LK787878000000
SD : 060401
ED : 062025
COMMENT : CUSOMTER CONVERVERSATION WITH REP
C:HELLO
R:HELLO
C:MAY I HELP YOU
R:BALANCE PLEASE
etc......................
I 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 am wondering within the Excel interface, is it possible to auto-fit the size of a comment box to its content?
I have searched the internet and found that a macro as follows could do the job:
Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
c.Shape.TextFrame.AutoSize = True
Next c
End Sub
However, this Macro applies to and resize all of the the comment boxes in the active sheet.
I would like to just auto-fit the comment box only to the selected cell, or even better, to multiple selected cells.
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 two sheets.
sheet 1 has a list of invoices.
on sheet 2 I just want to list certain invoices.
so sheet 1 may be like this:
001 rmb $25.00 1356
002 ght $35.02 4568
003 ght $45.23 5689
004 rmb $12.25 4568
005 bnj $1.25 4568
006 sdr $12.54 4568
007 rmb $65.25 4568
009 bnj $56.54 4568
On sheet 2 I need it to pull out all the rmb lines.
001 rmb $25.00 1356
004 rmb $12.25 4568
007 rmb $65.25 4568
I could make sheet 2 in to a list and sort it that way. but what I
need to do is pull the information into sheet 2, it's no good being
able to sort it on sheet 1.
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 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
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
I have data in excel sheet in the below format:
Existing view.png
How to write a VBA code or Macro to get it in below format:
Required View.png
Timestamp column is the unique key.
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 am having issues with this Macro since I have to run it on computers only supporting Excel 2003 and computers with Excel 2007. The Macro in use is as follows:
--------------------------------------------------------------------------------------------------------------------------------
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 RelatedWhat I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame.
Here is an example of the work sheet where the data is originaly.
[Removed]
Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010.
[Removed]