I want to create a macro that will run through a column and assign a name to each cell in that column that reflects the contents of another cell within each row.
for example, cell A1 = "Gerry", A2 = "Rob", A3 = "Ryan"
I would then like the Name of B1 to be "Gerry", B2 to be "Rob", etc (but to retain original contents).
Is this possible using VBA?
I have a list of 20,000 addresses and because of the way the sheet gets populated by different departments, the COUNTRY CODE and POSTAL CODE columns get their data reversed.
Instead of 48193 US, it gets put in as US 48193.
Since i have some many, and they are not in order at all (I could probably sort by COUNTRY CODE and get 95% of the in order (except for those pesky Canadian codes), is there a macro way out of this?
Even if I select two adjacent cells and run the macro and have the contents switch. Or if there is a way to do the 95% all at once.
I would like to find a text string in a document, move the information in the cell to the right of this to another cell (say A1).
View 8 Replies View RelatedBeen racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies View RelatedI have a workbook with 30 worksheets. Each sheet has 84 rows of data (start in 15 columns (A to O). I would like to create a summary sheet that only shows the most important data from each sheet.
The summary sheet would have 12 lines of headers and formatted crap at the top.
The Summary sheet header columns would be:
Site (A), Date (B) Health (C), Status (D), Critical (E), Task (F),
Dependencies (G), Owner (H), T-Date (I), Task Date (J), Mitigation Date (I)
The data in the sheets are not in that order, of course.
That
1. puts the name of the sheet I am copying from in column A
2. the deadline date in Column B (that date is always in C10 of each worksheet)
3. and copies Cells from Column A,B,G,H,I,O in any row in which the value in A is not "good" into columns C through H. I would like to paste those rows into the summary sheet. I have code that loops through the sheets and rows in each sheet to find the rows to copy. I can copy cell values directly from the active sheet to the summary sheet, but because I am copying a cell at a time, it takes 7 minutes. Yes I am impatient :)
Here is the code snippet where the copying is done:
Dim sh As Worksheet 'current worksheet
Dim DestSh As Worksheet 'worksheet in which to paste summary
Dim Last As Long
Dim CopyRow As Long 'row to copy
Dim LastCopyRow As Long
[Code] ........
ExitTheSub:
Application.Goto Sheets("KMARollup").Cells(1)
End Sub
I think there must be a way to use ranges to build an array of cell values and paste only once but I am lost here.
Here's what I'm working with:
??
5
3
4
1
Total 13
5
6
7
2
Total 20
Is there any way I can sum only the cells in the right column where the left column contains "Total"?
What I want to achieve: those cells have number 1 ( in column E) , adjacent cell values ( In Column D) are copied to G12 in the following format:: Mark,Lark ( i.e. cell value , cell value, etc)
is it possible ( with a command button) Sample attached
Here’s what I want to do in VBA
1. Put a value in cell H1 (text and numbers)
2. Find a matching value in column A (starting in row 2), error message if the is not a match.
3. Copy the adjacent cell column B (rows vary) to the clip board. It would copy until it found the first blank row.
I have attached a scaled down version of the spreadsheet, the one I use has 100's of codes. I know some VBA but not much. I searched the forum but could not find anything.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.
A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,
What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
I have text in column F (cells 20 to 199) that changes automatically based on pre-determined time constraints (this is done via an IF formula). I want the 4 cells adjacent to F to clear contents when the text in F changes. For example when cell F126 changes text automatically, G126, H126, I126 and J126 all clear. If cell F99 changes, cells G99, H99, I99 and J99 clear. And so on.
View 9 Replies View RelatedI have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
I have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
I'm trying every conceivable angle I can think of in vba and coming up empty. What I have is a spreadsheet similar to this:
Column A Column B
1. 1234567
2. 2345678
3. 3456789
4. 4567890 A0001
5.
6. 9876543
7. 9876543
8. 9876543
9. 1234578
10. 3456789 A0002
What I would like to do is to fill in B1:B3 with A0001 and B6:B9 with A0002. The number of rows in each set of data will vary, but the identifier (A000X) will always be located in the row in column B adjacent to the last entry for that set of data in column A and there will always be empty cells between data sets. The number of data sets will also vary.
I'd like to sum a range of cells if the date in the adjacent cell is last month, so... A2:A1000 holds the dates and B2:B1000 holds the values, i'm trying to figure out how to sum all of the values for the previous month (i.e. June).
View 7 Replies View RelatedI have a list of data:
2
140
1
660
10
140
0.92
660
2
130
0.18
660
4
510
0.44
820
4
510
I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)
I have a sheet that I populate with data, and I would like to create a formula (or some code) that will look down column A, and if any of the cells contain a value >1 copy the cell and the adjacent cell (column B) and paste both values into a new sheet.
View 2 Replies View RelatedTwo Sheets:
1) Top Sheet
2) IRA
- Cells A4:A10 of sheet "Top Sheet" contain dates. i want B4:B10 to show the IRA value as of the date in column A:
____A__________|_B______________________
4 | 09/22/2005 | $ Total as of date in A4
5 | 10/06/2005 | $Total as of date in A5
6 | 10/20/2005 | $Total as of date in A6
7 | 11/03/2005 | $Total as of date in A7
8 | 11/17/2005 | $Total as of date in A8
9 | 12/01/2005 | $Total as of date in A9
10| 12/15/2005 | $Total as of date in A10
- Cells A6:A12 of sheet 'IRA' also contain dates but not in the same sequence (some date ranges had several transactions), B6:B12 of sheet 'IRA' contains the transaction values during the date range:
____A__________|_B______________________
6 | 09/22/2005 | $100
7 | _ _ _ _ _ _| $200.................................
I have a spreadsheet with data populated down column B. This size will vary from day to day so is there a macro I can run that will populate Column C with a formula based on their being data in the adjacent cell.
View 2 Replies View RelatedConverting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
Attached File : LoopingVBA.xlsx‎
How can I let cell F5 for example contain both the texts or contents of cells A1 and B1? Is there a function that can do that purpose?
View 5 Replies View RelatedI am working on a sheet where I want the contents of cells D1:D10 appear if cell A1 has a text in it and the contents of cells D1:D10 disappear if cell A1 is blank. What is the right conditional formatting formula for this.
View 3 Replies View RelatedI do not have excel or an xls for a sample, I will try and restate the question.
In Cell A1 It will be used for copying the contents of and pasting the completed information into web browsers.
In Cell A1 You will find This information contained and formatted in one cell alone.
Figure-1
A1=
Information About Company
Brief Story About Company
Product Name: (X)
UPC Number: (Y)
Serial Number: (Z)
Terms Of Sale
Etc
End Figure-1
I want to essential Modify (X)(Y)(Z) using different cells one for each variable. "All completed information will be found inside of A1"
Having Cell B1-B9 Being used as labels "Product Name" "UPC Number" "etc" and using cell C1-C9 to input variables for = (X)(Y)(Z)(ETC)
In reality from cell A1 will change dynamically, but some information will need to stay constant.
Every thing In B column will just be labels.
Every thing In C will be for variables.
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.
View 7 Replies View RelatedI have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
I have a spreadsheet that can do more than one calculation on each row
ITEM
FLOW
(l/s)
[Code]....
The values in row A can only be selected from a named range drop down. If no entry is there (i.e. blank) then i'd ideally like the cells to return from black to white.
what i need is to affect the contents of cell c3.
if i select cell e15 i would like cell c3 to read 123
if i select cell e17 i would like cell c3 to read 234
i believe this could be done using an cell formula if statment but due to teh number of cells i wish to have affect c3 a vba based solution would be required.
i've built an if satement to do this under worksheet activate but not sure how to use the worksheet change way
Is it possible to place formulas in vba to control cells based on the cells contents.
A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat
What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.#
if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.
Setup: I have 2 worksheets with between 8 and 9,000 rows on each
Column A in both worksheets Sheet1 and Sheet2 have an email address in them.
Not all addresses in sheet1 will be on sheet2 and visa versa
Column J on Sheet 2 contains a date
What I need: Column M on Sheet1 is empty
I need a formula to place into Column M on Sheet1 that will
Look at Sheet1:A
Locate the corresponding value on Sheet2:A
Pull the date from Sheet2:J same row into Sheet1:M