How do I paste text from a column into a diagonal line of cells (i.e. A1, B2, C3, etc.)? Additionally (and this may be related), how do I get the cursor to move to a diagonal cell after an entry? (as opposed to down or to the side)
View 2 RepliesI have a table with data representing $income vs %Debt burden respectively. The data on the table per respective intersection provides number of accounts and total ($)value of accounts. I have tried to combine the two pieces of information into one column and have a diagonal line separating the two numbers. (sample table attached).
View 2 Replies View RelatedI have a few macros that run until line x currently. I've tried to incorporate xl down into my macro but must have misplaced it. I need to paste until the last line of text in column B.
Sub vlooktest()
'
'
Range("C4").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Raw List'!R1C1:R26000C9,3,FALSE)"
Range("D4").Select
ActiveCell.FormulaR1C1 = _
[Code] .......
Does anyone know of a macro that can take values in a daigonal and put them in a single column?
Like for example... if you refer to the attached image, I basically would like to select all the diagonal values in red then put them all in a single column.
I would like to copy several cell entries from one worksheet ("Entry") and paste then into a new line of another worksheet ("Contact DB") which will serve as a repository/database. Ideally, I would like to clear the values in the first "Entry" worksheet once this is done.
The thing that is throwing me off is that the data entered into my first "Entry" worksheet is not all on one row. However, I have recorded all the starting point references and destination cells (i.e. "B2" from "Entry" --> "A5" of "Contact DB") so it's ready to go into code.
Using the following code (which I tested for copying and pasting the first cell of interest) but I can't seem to get it to work:
Code:
Public Sub Macro1()
Application.CutCopyMode = False
Sheets("Entry").Range("B2").Copy
Sheets("Contact DB").Range("A5").Paste
End Sub
I'm brand new to vba so I'm struggling to understand the code and where it goes. That brings me to Q2 (and a dumb one it is). Where do I put this code? I already have one bit of code (for allowing multiple entries in a dropdown list) for the "Entry" worksheet under the "Microsoft Excel Objects" folder. Is this where this copy and paste could would go too? Or do I need to insert a "module?"
I have a workbook that usually looks something like this
Category Product No description Price
Balloons 12345 Red Disney balloon .50
Balloons 12567 Blue Princess balloon .86
Balloons 76521 Angry Birds Balloon .80
Kites 23456 A Big red Kite .27
Kites 22222 A small blue kite .06
Banners 10000 Party banner .33
etc..
I need to find a way to copy the category below an empty row from column A and paste it in the blank row in column B . If possible to bolden the text but I could probably work that bit out myself. I'm new to this but have used VBA before to run macros.
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies View RelatedI have a database of address which have been inputted into text wrapped cells like this:
Line1
Line2
Line3
Line 4
Can I turn these lines into columns?
I have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.
Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.
What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:
1. Looks up last row (row 30)
2. Copies last row (row 30)
3. Pastes to next empty row (row 31)
4. Pastes values only to second last row (row 30)
I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedI'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
In sheet "diary" I have data in certain rows in column A8:C10000 that contains values if a certain condition is met. I need a vba to copy and past only non blank cells in column D8:F10000. I first wrote a formula with index but it takes too long to caculate.
View 9 Replies View RelatedI am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies View RelatedI have a sheet that is set up with columns A thru G. In column A is a name and the columns B thru G is just data. How do I cut text thru vba and then paste it to another location.
View 9 Replies View RelatedI have data like this
textrows to repeatelements in row
MB1216
MB233
MB3116
MB4415
MB558
i want to paste text for MB1 in column B like from MB1-1 to MB1-16 and MB1 2-1 to MB 2-16 because MB1 has 2 rows and 16 elements to repeat, for MB2 in cloumn B like MB2 1-1 to MB2 1-3 and MB2 2-1 to MB2 2-3 and MB2 3-1 to MB2 3-3 because MB has 3 rows and 3 elements to repeat like this i want to continue upto MB5
I am expecting like this
A B
MB1 MB1 1-1
.
.
.
MB1 1-16
MB1 2-1
[code]....
and i would like to continue this for the rest of text in column A .Here i tried with commond button simple and now i am in position to paste text in a given range but not the way i am expecting.
Attached File : sample (2).xlsx
Example: A1, I go to school (001). I would like to copy only (001) and past to B1. like that and process to the last row of A1. or I would prefer manual last row.
View 9 Replies View RelatedI'm trying to copy and column of text (R4:R300) and paste the text as comments in the previous column (Q4:Q300).
View 3 Replies View RelatedCode:
Range("a" & Cells.Rows.Count).End(xlUp).Select
Range(Selection, "a1").Select
For Each cell In Selection
If Not IsEmpty(cell.Value) Then
If cell.Value "2012*" Then
cell.Copy
cell.Offset(0, 5).PasteSpecial
End If
End If
Next cell
I have a to copy from column A some cells ( e.g "000005PR RODI T.R, S.L.") and paste it in column F. The problem is that in column A i have as well empty cells and cells that have date (e.g "2012 02 14-OCT-2011 CN 100725") . Neither the IF/AND nor the "double" if constructs are working .
Essentially, I have 2 Sheets:
"SheetWSS" = data to be copied
"SheetWSD" = destination of copied data.
Below is a sample of the data in "SheetWSS"
----------A------------------------B-------------------------C
1------Trade ID -------Description---------System no.
2--------579----------------Loan ---------------------- 7
3--------580----------------Deposit---------------------22
4--------702----------------Deposit--------------------- 11
5--------703----------------Loan ----------------------- 58
6--------732 ---------------Loan------------------------66
7--------733----------------Deposit-------------------- 99
etc...(no more than 10000)
Now, an explanation of the data:
1) I work for a small bank (CORP) that takes deposits and gives loan.
2) CORP books these trades using only system no. 7 and 11; other system nos. belong to customers.
3) When a single trade is booked, the 2 sides of the transaction is recorded (by Trade IDs) . E.g rows 2 and 3 relates to one trade. So if CORP loans money with system no.7 to CUSTB, who uses system no.22, it shows for CORP a loan and for CUSTB, a deposit.
4) Everyday, the data in "SheetWSS" is updated with a different number of rows from previous, but the number of columns remain the same.
So, here's what i'ld like the macro to do:
1) Go into Column C, find system no.7 and 11.
2) For each 7 and 11 found, Copy the next row . So if for e.g, system no.11 was found in C4, i want the ENTIRE ROW 5 to be copied.
3) Paste the entire next row in "SheetWSD" until we have a list of all opposing sides of the same transactions initiated by CORP.
Finding any specified Text like "Cube" down a specified Column in this case "D" when Text has been found
Copy the 24 cells directly below and Then Paste to G1.
I want some vbs code on my 'Copy All' button in Email Data sheet which will do my work. Its a bit lengthy but who knows the concept will find it easy.
I have one sheets -
Sheet attached - Worksheet .xlsm
Workshet.xlsm [Data in 'Mod' sheet i)should convert from text to column and paste in 'Row Data' sheet ii) then transfer form Row Data sheet to 'Email Data' sheet 'Mobile Data' sheet and 'Other Data' sheet when Copy All button is clicked in Email Data sheet.
There should be some logic which will fetch appropriate date from 'Mod' sheet to the respective sheet . We can have some kind of logic like
If 'Register' found then take the value of register and workstation and paste in Email Data sheet.
All data below should be fetched from Row Data sheet which is blank now and will get the data when we click the Copy All button ,
1st Register Count:
Below Register Count (*) i.e. 16 If workstation count present copy to E15 in Email Data sheet in Table B
Below Register Count (*) i.e. 9 If register count present copy to E16 in Email Data sheet Table B
2nd Register Count:
Below Register Count (*) i.e. 8942 If workstation count present copy to C15 in Email Data sheet in Table B
Below Register Count (*) i.e. 2883 If register count present copy to C16 in Email Data sheet in Table B
3rd Register Count:
Below Register Count (*) i.e. 0 If workstation count present copy to E18 in Email Data sheet in Table B
Below Register Count (*) i.e. 1 If register count present copy to E19 in Email Data sheet in Table B
4th Register Count:
Below Register Count (*) i.e. 98 If workstation count present copy to C18 in Email Data sheet in Table B
Below Register Count (*) i.e. 999 If register count present copy to C19 in Email Data sheet in Table B
Substr:
B32:B44 will be pasted in C29:C41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B50:B62 will be pasted in B29:B41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
I am looking for a simple non ARRAY formula (OFFSET)? that will use range A1:E5 to create a new range which will include cells A1, B2, C3, D4, E5 (the diagonal). The sum of this formula would give: 30 ...
View 9 Replies View Relatedhow to stop rows resizing with text which paste into cells
View 1 Replies View RelatedI have this formula: =VLOOKUP(C:C,Sheet2!A1:A10000,1,FALSE)
that I need to put in column A from 1 to 26,xxx. Is there a way to paste that formula in every cell down the column without the part in bold changing? I'm not too keen on hitting ctrl+v over 26 thousand times.
I am trying to do, is paste a word in front of text that is already residing in cell throughout an entire column, and then automate this process by creating a macro that will do the same thing for me throughout an entire column. To best explain this, it woudl be like if you have a column 100 rows/cells long, and every cell already contains data. I need to insert something in front of what lies within each cell.
View 9 Replies View Relatedis there any easy way to take the sum of a really lond diagonal? I know i could use a macro, but I was wondering if there was a way to do it with formulas, the sums will ultimately be used for a graph,
View 9 Replies View RelatedWithout VBA, how do I sum diagonals such as N2 and N3, but also like N7 where the values are limited to row 13? Hopefully, the post illustrates better than my words...
View 9 Replies View RelatedI want to copy text from a range of individual cells and then paste all of the individual cells copied and paste this data into a single cell with all the text separated by hyphens between the texts.
Example attached
If you are a macro writer i have a project that i need to complete. you will be compensated for your time.
I want to copy a range of cells from excel sheet to a text field of a web page. Below is my macro, while executing it 'm getting run time error 70.
Sub GetTable()
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
'create a new instance of ie
Set ieApp = New InternetExplorer
[Code] ......