Copying Based On Criteria- Case Method
Nov 11, 2009
There is a Summary sheet from where data has to be copied to 3 different sheets(for 3 accounts) I have highlighted the columns that have to be copied. The macro will be run everyday, and each day only 3 rows from Sheet ‘Summary’ will be copied to the other sheets.
Important point: Only copy rows that have Column D value = GBP
Which Sheet to copy to? Check Column A ---
Case 100 = Sheet(“ONE”)
Case 200 = Sheets(“TWO)
Case 300 = Sheet (Three)
The Orange ones – Column C,I, and N are copied as follows: C goes to Column A, I goes to Column C, and N goes to Column J in one of the 3 output sheets.
It should not overwrite previously existing data, simply copy to next empty Row (By Empty Row I mean Column A is empty, columns after M may have values in many rows)
Then the Yellow columns. I need them copied on W26:W32. So that’s cells in Column E,J,K,L,M,R going to W26:W32. These yellow column figures must OVERWRITE previous data (unlike the Orange). The Blue cells that you see in Sheets (ONE,TWO and THREE) contain formulae that I want copied down to the Next Row when there is an entry made in Column A.....
View 5 Replies
ADVERTISEMENT
Jun 22, 2014
I am trying to use VBA to move data from one column to another while using values of a third column as a reference for where each value in the first column should be moved to in the second. I will try to explain as best as possible, currently I have a macro that copies from one column to the required but due to the irregular spacing of the data it results in the data not matching with the data its being copied into.
Say I have Three columns A, B and C. I have a range of data in column A say dates that have irregular amounts of spacing between them. Column B is empty. Column C contains Data that is again spaced out irregularly and not in line with that of A. The value of column C cells is just identical values. Now the aim is to move the values from column A in the order which they are listed and place them in Column B next to each value in Column C. So say the repeated value in column c is EXAMPLE then everytime EXAMPLE appears in column C i want to move one date from column A into column B next to this EXAMPLE value, in order which it appears in column A. So the first date will move next to the first EXAMPLE value and the second date which appears next to the second EXAMPLE and so on and so on until it reaches the end of the sheet.
My thinking was placing the A column data in an array and doing some sort of sort.
View 3 Replies
View Related
Feb 16, 2007
I am have a spread sheet (sheet 1) with a number of columns and what i am looking to do is see all the people that are participating in a certain stage in the process that is not common, So what i am wanting to do is copy the names and the corresponding number of these people (on sheet1) over to a new worksheet(sheet2) based on a yes or no criteria further on in the spread sheet(sheet1). sheet 2 has additional columns that the workers here would need to fill in.
The criteria is in sheet 1 cells Y2:Y2000
The number is in sheet 1 cells D2:d2000
The names are in sheet 1 cells E2:e2000
If its at all possible i would also like to then make the names of those people on sheet 1 a hyperlink to the additional information
View 4 Replies
View Related
Sep 17, 2012
I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)
File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.
File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work
Code:
Option Explicit
Sub get_data_from_2()
Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range
[Code] .......
the bolded part gives me an error.
View 3 Replies
View Related
Feb 16, 2012
I want the select case list of a ComboBox to be treated as a variable in order to shorten the code size. To clarify the problem, i post the code with what i want to do, but don't know how to do it that way.
Code:
Select Case ComboBox1.ListIndex
Case 0: y = "AT"
For j = 0 To 26
Case "j": y = sheets("name").Range("A(j)") 'Range A(j) is a string, so y as well, as seen in Case 0.
Next j
End Select
Is something like this possible?
View 3 Replies
View Related
Aug 6, 2014
I'm fairly new to using VBA and have come into what is probably a simple problem to solve. However, I am looking to sort out my code.
VB:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$H$3" Then
Select Case Target.Value
[Code] .....
From the above the macro runs only with the Range H3, I would like have this code operate for the Range H3 to H10... I can't quite get it to work with a few formats I have tried.
View 3 Replies
View Related
Dec 24, 2013
In the attached sheet, i want one formula in column C which calculate the total cases based on the country by searching the data in sheet "Data".
When I select the specific country, the count should be populated automatically.
Book1.xlsx‎
View 3 Replies
View Related
Dec 23, 2008
It sits in the "This Workbook" code window to run each time the wbook is opened. What I want it to do, is based on the date in Sheets("Control").Range("L3") select a particular cell in another sheet called "Risk Sum". I'm assuming this is easy to do, I've come up with the following, but it just doesnt want to work ...
View 9 Replies
View Related
Aug 26, 2009
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
View 7 Replies
View Related
May 8, 2008
Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
View 6 Replies
View Related
Jan 17, 2014
I've 2 lists that contain similar info but 1 of the lists holds info I need to join.
I've a unique case sensitive id that I'd like to use to join the lists.
I usually use VLOOKUP but it is case insensitive and I cannot understand how to make it case sensitive.
The 2 lists are in 2 separate worksheets as they each hold a lot of data.
View 3 Replies
View Related
Oct 31, 2011
I'm trying something interesting with Select Case where instead of using a static Case, my Case is based on the value of a cell.
here is the example of the present code:
Code:
Select Case Range("F75").Value
Case Is > Range("F30").Value
Range("AB59").Value = "Use"
[Code].....
View 2 Replies
View Related
Oct 20, 2009
I've got a column of data that I need to split into further columns, the only way of identifying the split from the data is if a lower case character appears next to an upper case character.
For example, a cell in column A could look like;
Computer ProductsDrivesDVDExternal
And I'd need to split that out to cover columns A B and C with
Computer Products / Drives / DVDExternal
I'd need to do that for all the cells in column A.
View 9 Replies
View Related
Aug 17, 2007
Is there a way to change the text in a cell to proper apart from 2/3 letter words which I want to keep as upper? Basically can it ignore all words that are 2 or 3 letters long, but change all other words to proper text?
View 3 Replies
View Related
Jun 3, 2009
I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
View 9 Replies
View Related
Dec 10, 2008
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
View 3 Replies
View Related
Sep 6, 2013
I'm designing a sheet at work where an individuals performance is monitored then charted. However the supervisor has now decided that he wants to see their performance charted against that of their entire team.
Using Data Validation i have a drop down selecting the user. Then their performance stats are found from various sheets using INDEX and MATCH. I have also done this in another cell to locate their team number ie "Team 1" for example.
Can i use VBA or any other method to generate a chart for when the team is displayed to then generate the entire teams chart? The data to generate this graph is on its own sheet as a team overview?
View 1 Replies
View Related
Jun 25, 2014
The requirement is Copying a col. B value when the criteria that there is value 'NO' in column C or D. The value of col.B should be pasted into another excel workbook called 'Deliver_Error Log'.
I have pasted the Sample checklist and the Deliver_Error Log.
Sample checklist.xlsx and Delivery_Error Log.xlsx
Condition 1: Example in the Sample checklist row. 8, 9, 10 & 12 have value No in either of the column C or D hence the value in col. B of row 8, 9, 10 & 12 must be pasted as 4 new available next row items into the Deliver_Error log into col. L (Error subcategory ) i.e Sample Checklist ,row 8,value col.B = next available row of Delivery Log with col.L (Error subcategory )as Col.B (Q-Checker CheckList) and it must also automatically fill the Delivery_Error Log col.B(Data type) as "Error" and copy the Object Name (value in C2 of sample checklist) into col.E (Object name in log).
Condition 2: If all the values in the checklist are Yes in Column C and D then Copy the Object Name (value in C2 of sample checklist) into col.E (Object name in log) and set Date type as Delivery
Condition 3: If value in checklist is blank or NA take no action.
There will be one Deliver_Error log but multiple checklist of different Object names.
View 5 Replies
View Related
Apr 22, 2009
I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies
View Related
Jun 20, 2008
When I use a simple formula such as:
=upper(a1)
that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.
What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet
View 9 Replies
View Related
Jun 23, 2014
Example.xlsx
I am wanting to have a sheet that I use for reporting based on another sheet in the workbook but I want to exclude items that are closed.
The first sheet will contain all rows of data with a status column of either Open, On Hold or Closed
In the second sheet I only want to have listed the items which are Open or On Hold, I want to ignore the closed items.
In the example enclosed, I only want rows 2,4,6 and 7, but NOT 3 and 5 due to status.
View 2 Replies
View Related
Apr 3, 2013
I have my students down the far left column (30 rows) and along the top for each worksheet is the criteria which they must be able to do. If my in row 2 completes the criteria text above in column 5 for example, then I input a 3 which then adds the points and works out their sub level scores. However I am now in need of it being able copy the criteria above if a student on any row has a T(for target) in that box. I would then like that criteria to be copied to a master sheet which displays their name down the side and all the targets they have for that subject in the next column, so that they can be referenced for their report summary worksheet.
View 14 Replies
View Related
Sep 22, 2007
As per this screenshot,
I am trying to work out a method which will allow me to drag the forumula which is in cell B13 from B13:B17, and have in increase the date serial number by one for each row by which it descends.
In other words I want to drag the formula down to, say B14, and have it increase the date serial to = that which is in cell A14.
[the obvious benefit of being able to do this is that I don't have to manually adjust the date serial in each cell on each new row]
View 9 Replies
View Related
Mar 4, 2008
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
View 5 Replies
View Related
Oct 28, 2008
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies
View Related
Jul 20, 2006
For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....
View 9 Replies
View Related
May 15, 2007
I'm trying to compare two range values within a macro to see if they match...if they do/don't I capture and write some other data.
One list is lowercase, while the other list is UPPERCASE.
My current macro needs them to be in the same case because I'm using the following to compare:
If VPNID.Value = BuildHRName Then
How can I change the format of one of the lists to UPPERCASE or lowercase so that I am comparing apples to apples?
View 9 Replies
View Related
Nov 17, 2009
if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized
The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"
View 2 Replies
View Related
Aug 7, 2013
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies
View Related
Mar 27, 2014
I need to copy rows to sheet1 to sheet2 which matches with today's date. The code dosen't work wit date. I have attached ScreenShot of excel file.
Excel.JPG
HTML Code:Â
Function Data(strFrmSheet, strToSheet)
Sheets(strToSheet).Select
Range("B2").Select
Sheets(strFrmSheet).Select
Range("B2").Select
Do While (Not (IsEmpty(ActiveCell)))
[Code]....
View 4 Replies
View Related
