I am looking for a better solution working with columns in VBA.
Currently, I refer to columns by their letter or # in my VBA code.
For example:
Columns("AW:AW").FormulaR1C1 = "=+RC[-8]"
I have a scenario where I have to work with a dynamic data source that scrambles columns around (column is not always in the same position on the spreadsheet). Currenct...Also, there may be missing columns or new columns that need to be recognized.
What I would like to do is refer to my columns by header name.
For example (PSUEDO CODE):
If column ("MTD BUD") or ("MTD REV") do not exist Then
Goto ErrorHandler
Else
Columns("MTD REV").FormulaR1C1 = "=+RC[MTD BUD]"
End if
Would the best way to go about something like this be to create an array with column header names that I can run things against? How would this look?
So I want to add some VBA Code that deletes specified columns and then insert Bolded Column headers to the remaining columns. The columns I want deleted are D,F,I,J,K. For simplicity purposes, the column headers would be A,B,C,D,E,F,G.
I tried using Columns("D,F,I,J,K").delete but I kept on getting 13 error.
I want to export some columns of my worksheet to a csv-file. But i don't want to export all of them, i just need for example: Columns B, D and G and inside the csv-file they should be in an different order like G, B, D .
I already thought about using "union" to select the specific columns, but i can't get this to work.
I have the following code that sorts each column sperately and it works, but believe this is a long way round and not the best way. I need the columns sorting alphabetically seperately and each has a header row.
In row 1 I want to have the names of servers, so we would have A C D E
Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.
Then B would have under it Start, End, Data, Time
And so on for C D ...
I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be
A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.
I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.
I am trying to find min value for a row (product price) within two columns (price lists) and return row one (company name) in third column ( see the attachmet). My first idea was to use “min” and “if” but a problem occur when there are text or zero or none values in the price list. I tried with conditional formatting, offset function, match-index and other methods without success
The column header row for a string; if the header contains one of two possible strings then copy the entire column. I've tried the following code but it's not working.
Select Case oColHeader Case InStr(oColHeader, "how much", 1) > 0 Selection.EntireColumn.Copy Case InStr(oColHeader, "level of sat", 1) > 0 Selection.EntireColumn.Copy End Select
Can I create a PivotTable with two columns of data for the same Column header?
I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.
If I searched for the value "abc123" I want it to return Headers 1 and 2 in a seperate column. It would not matter if the same value is in one column multiple times
So the results would show me the Column Heading for anything that reads: "abc123", "ABC123", "AbC123", "aBC123"
I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.
I'm using Excel 2007
The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)
The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .
It's just six columns of data. The short list is in AY from AY2 : AY15 The long list is in BB from BB2:BB505 The column header "AL" is in AY1 and NL is in AZ1
I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).
Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.
Code: Public Sub ExportToCsvFile(FName As String, _ Sep As String, SelectionOnly As Boolean, _ AppendDataOnExistingFile As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer
I am a macro newbie and I think this is beyond me.
I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.
So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.
There must be a way to do this that is easier than searching the internet all afternoon again
As you can see some columns have 1 comma, some multiple and I need each laid out into a single cel so I can do some lookups. The space values in the above data is actually a new column in the sheet.
I've got a report that has a period date in it and its in the format "ARP-12", which is not set as a date. If I highlight the column and click Text to Columns it puts it in a date format which I can use.
I recorded myself doing this to insert into a marco but the date format is not correct. When I do it manually ARP-12 comes out as 01/04/2012 which is what I need but when i run the code i recorded it comes out as 12/04/2011.
I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.
in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.
In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.
The code below is supposed to insert a column and rename it. However, when I debug, the code only renames the column, it does split or run the loop. I press debug again, and then code executes as it is intended.
I can't explain why I have to press debug twice for in order for the code to work properly.
Code: Sub renameColumns() With Sheets("byPosition")
I need a macro that will insert 2 columns to the right from Col H, add header value "Ledger" in Col I1 and "Statement" in Col J1. I then need the macro to put the values for ledger and statement next to each other as they are both related (as per below screen shot). I have highlighted which units are related so you can see that all related entries will have Ledg followed by Stmt and if they are not related then will be inputted under their relevant headings. I then want the macro to delete rows which have blank cells.
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range) If target.Column = 1 Then ThisRow = target.Row startRow = 1 i = 1 Set ws = ActiveSheet maxRow = Cells.SpecialCells(xlLastCell).Row maxCol = Cells.SpecialCells(xlLastCell).Column ActiveSheet.UsedRange.Interior.ColorIndex = xlNone Do While i
I have a booklet I want to print from Excel 2000. I'm not seeing how to have the HEADER on page one only. The header is coming up on all pages. Is there a way to tell Excel to print the Header on page one only?
Attaching the excel (QOS- ARP- Applications.xlsx) - Formula I am trying in D27, "X" value is there in the row and the corresponding application (Header ) should be the return in D27.
I have a worksheet that contains a number of rows. [Thank you, Captain Obvious!] These rows are logically grouped to represent a series of "items", with each item having one or more rows (with a "header" row at the top). The rows for a particular item represent different types of information about that item, and therefore make use of different formulas based on the row type. Due to the relative complexity of the formulas in these rows, I am creating a series of row "templates" from which I can copy and paste every time I want to create a new item or add rows to an item. As such, I am trying to avoid direct cell references to different rows, as these would easily get messed up during cut and paste. Instead, I am trying to limit myself to various lookup functions (e.g. OFFSET, MATCH, etc...) that will work regardless of where a row is added.
In order to accomplish this I am using a "key" column to group all rows for a particular item together. Until now I have had to type in the key value for every row for a particular item. What I would like to do is to only have to type the key value in the header row, and have a formula populate the key value in all subsequent rows ... until a new header row is reached, at which point the new key value will be used. Take a look at the following example: ....
it was possible to have my header which is in row1 to printed at the top of every page without having to manually put it at the the top myself. this is for printing only as i have frozen panes to make sure its always visible when in spreadsheet
Is there any way in excel 2007 to define a standard header and footer on the first sheet and use the same header and footer for all the remaining sheets within the workbook (same font and style as in first page).
Since I have around 25 sheets within the workbook i cannot do formatting and copy paste all the time. This will save my time alot.
I would like to add the column header to each of the row item and price, we maintain masters in the matrix format, butthe application supports only the row item mapping with the customer and pricing. Attached excel file
I have a document I want to be able to choose to insert a logo in the header across an whole document if it is not going to be printed on letterhead. I want the document to be transportable to different users so I don't want to insert an image with a file address but rather copy it from a hidden sheet. Is it possible to do this? If not, is there a way to hide or unhide images in a header?
I'm looking to automate an excel file, I have some vba code that formats the file. I think I have one last item I'd like to add. I'm trying to setup the header via VBA code, which I've done via the record option. I was wondering if there was a way I could have the header show the date the file was run. So, if I run it today, then it would read 2/26/2009...then when opened next week, for example, it will still read the 2/26/2009 and not the date it was re-opened.
I have a code that copies Sheet1 and send it via email. Everything works fine, besides that I can't get the header copied in the file that is going to be sent. I am attaching a sample workbook.
