Macro To Act Differently On Selected Multiple Columns?
Jan 18, 2014
I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.
I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...
So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..
I made my own small code but dont know how to make it act differently on selected columns....
I would select the entire matrix and then want the macro to run differently, taking into consideration the column names
[Code].....
I also need to add a vlookup formula for some column based on the column B's value...
Ex: This is for Col C
[Code] .....
So how do I write the code in such a way that the code acts on its own differently.
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:
I have a row of sub-headings at row 12 that require Sort Filters. My problem is that I need the filters on selected columns only. It seems that with the standard filter button I get all or nothing.
When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?
Original Sub sessions() Dim a As Integer Dim x As Long x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
Sheets(1).Unprotect Password:="password"
For a = 2 To x
If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then Cells(a, "f").Resize(1, 6).Select Selection.Locked = True ElseIf Cells(a, "m") Cells(a, "d") Then Cells(a, "f").Resize(1, 6).Select Selection.Locked = False
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
I have a work book with 97 sheets, all of the sheets are identical in format but contain different data. I need to multiple column F (which is a price) by column G (which is a quantity). I then need to sum the results and spit this number out on a new sheet. So in then end I would have 97 entries on the new sheet with this new sum which would give me are total stock value on hand.
I am trying create a macro that will create a new work sheet on an existing workbook. I have minimum experience writing a macro, but I do know how to effectively record one. The issue is, I need the macro to copy multiple columns down to the end of the data and pasting it into the new workbook at the end of the data already pasted-- rows of data are frequently added to the workbook.
When I wrte a macro as above, i get error "the command cannot be used with selections that contain rows or columns and also other cells. Try selecting only entire rows, columns or just group of cells".
Actually i want delete all columns except B:B and F:F
What I need to do is group and collapse certain columns when Cell D4 in my Date tab reads Bud. If it doesn't read Bud, I need it to ungroup those columns and group and collapse other columns.
So for example: If I have 6 columns (A - F), when Date tab cell D4 reads 'Bud'. I want to group columns A and B, and only show C-F. If the cell doesn't read Bud, I want columns A and B to be ungrouped, and Columns E and F to be grouped and collapsed.
I've been racking my brain, using the Record Macro and just cannot get it correct. Granted I have created something similar to HIDE my columns, however I need it to be grouped so that higher management can ungroup to see some details if need be.
Code: Sub GroupColumn() Dim s As Worksheet For Each s In ActiveWorkbook.Sheets(Array("Name1", "Name2, "Name3", "Name4")) If Worksheets("Date").Cells(1, 4).Value = "Bud" Then
In 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 need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
I'm selling a product to clients. The first one is $20, and every subsequent one is $10. If they don't order any, I need the cell to read $0. I've been trying to get the IF function to work, and can't quite make it work correctly.
I currently have the following in a cell: =BDH(AHD!A3,"px_last","-5cm","-5cm")
This currently displays as -32 pulling this number out of bloomberg.
I now want to write a formulae in this cell that will display this as 32c if it is negative... and 32b if it is positive.
So if the number pulled out is negative I want it to have a c after it (and please have it delete the minus sign) and if positive, then have a b after it.
I want to import one worksheet from different workbooks to Master workbook. In the source workbook, I need to import only ' Code' sheet and in this code sheet , I need only 4 columns(A, E, F, and I ) to import. I also need cell C3and E3 in source worksheet to master workbook
I want to import like 'Code(1), Code(2), Code(3),, and so on. I don't want to import whole thing in the one sheet in the master workbook.
IN the master workbook, the header is on A3, so starting row will be row 4 in master workbook.
I'm prompting the user for what two ranges they want to keep in a excel sheet and then I want to delete the rest of the columns. There may be 5 total columns and there may be 30, it will vary. The reason I want to do this is because I will then save data to CSV file and it can only have two columns of data to be passed on for other data processing.
I'm trying to sum selected columns of a specific row in a spreadsheet. The row name is to be matched to the same name in a different sheet; the column headings are also found on that separate sheet. I've tried SUM using INDEX and MATCH, but it's not working (most recently I get #N/A). I'm trying to do this sum for each separate row, first through sixth, with varying columns for each row.
I'm not opposed to getting into VBA, but wanted to see if there was something that I missed with the regular formulas.
My most recent iteration is: =SUM(INDEX('Current'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($B6,'Current'!$A$1:$A$16,FALSE)):INDEX('Curre nt'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($C$2,'Current'!$A$1:$A$16,FALSE)))
I have a data sheet which I need to print everyday, I need to print Column A plus other individual columns on separate pages. For eg. Column A + B, Column A + C, Column A + D etc until the last column. Besides hiding and unhiding, is there any way to do it via vba? For eg, pop up to ask user which column to print?
I am looking for a way to get a weighted average to work without the need of helper columns, when my weights are stored in one range, and the actual values are stored in another range, but might not be sorted in the same fashion.
For example; range 1 might be:
Weight Homework 120% Pre-Exam 30% Exam 50%
But the range for student's scores might be in a slightly different order:
Scores Exam 90 Pre-Exam 72 Homework 1 81
(Note that this is a greatly simplified example and that just resorting would be impractical).
I have two sources of data (of electricity use) that I need to merge to plot detailed consumption. (Source B below needs to have calcs done on it and then be subtracted from Source A. Trust me, it just does). But their formats are transposed relative to one another, and one takes readings every half hour and the other once a minute. Plus, I'll need to append to the data periodically as more gets generated.
I'd describe my Excel level as "novice plus", i.e. IT- and database-literate but as far as Excel's concerned still trying to fully master pivot tables...
I have a spreadsheet with numerous transactions that contain the same name but are often spelled differently or inconsistently. I would like to pull all the transactions with the similar name and in a new sheet, make all the inconsistent ones, the same e.g. (space)mr smith or mr smiths or mr smith's or mr smith would all be mr smith. I am thinking of using the match command as a possibility but want it to repeat each row.
I am attaching a spreadsheet to show the existing table and the desired result which is similar to what I am showing below.
Example:
Existing Names and data A B C D E 1mr smiths xyz10020% 2 mr smith abc12430% 3mr smith's def20040% 4mr smith xyz7550% 5simon inc zbc14065% 6simon's inc xyz20070% 7simon abc9525% 8shows zbd18045%
Want it to show Names and data:
A B C D E 1mr smithxyz10020% 2mr smithabc12430% 3mr smithdef20040% 4mr smithxyz7550% 5simon inczbc14065% 6simon incxyz20070% 7simon incabc9525% 8showszbd18045%
My boss wants me to create a macro to hide columns if there is a "X" on top of the column labels. The macro should be in toggle mode whereby the next pressing of the macro will unhide the hidden column.
There are more than 50 columns in my actual worksheet. The macro has to check column by column whether there is "X" marked on top and hide it if so.
The next pressing of the macro should then unhide all the hidden columns.
I have a worksheet which contain a lot of data in it. I want only certain rows and columns to be printed when I click a button. Actually I am trying to print a report.
Suppose I have data across A10:M100. Lets say from columns A to D are permanent, if the cell colour of any cell from E to M is not Red then that is to be printed along with the contents in A to D. Since I have bunch of data in that sheet almost 10000 rows I separated data by giving them name. If it is possible to have an option to choose from before printing that would be much much better. Because even though there are many cells left uncoloured I don't want them to be in the print as they are belong to another table (I have given name for each table)
If all columns from E to M in row 20 are Red then that row should not appear in the print. If J20 is left uncoloured then it has to be printed along with the data from A20 to D20 ( which I said permanent). This colour I fill by a macro as when required only for the range E to M.
I am using Excel 2010 64-bit (with SP2 and all other updates installed).
I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)
I need to sort this worksheet as follows:
1. By background color, so yellow is on top, white is on bottom. 2. Then, within the yellow rows, data in column J. 3. Then, within the white rows, data in column A.
Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.
Is there a way to combine these steps, perhaps with a macro?
I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.
I have written code that allows the user to open a file. To make things easier I changed the file path to point directly to the folder where all the needed files will be placed. The problem is everyone that will be using this work book map their drives differently. One user may have it as H, and the other as J. Also users can have the same drive mapped but at two different starting points. Ex: H:DataReportsxxxyyy or H:Reportsxxxxyyy this causes the code to crash. Is there a way do default the path so it will not matter how the drives are mapped?
Here is what I am using so far:
Dim filt As Variant ' flit to be used in the getopenfile. variant is need for its multiple Dim FilterIndex As Integer Dim Title As String
