I want to create a column such as shown in column 'A' which concatenates names from column 'C' for every department. How to write this command?
I'm getting a #NAME? error when I paste your formula in directly. I'm not sure how to edit it because I'm not sure what it's meaning is.
Group
Dept.
Name
John; Mary; Jack
295
John
295
Mary
295
Jack...................
I am playing around with code to extract stock prices from Yahoo! Finance.
The data is extracted from Yahoo in the form of a CSV string, for example:
"HSBC HLDG",596.80,"-0.22%"
I've written code to split the string into cells as I'd like, but just realised that if I copy the above text manually from the Immediate Window (on the back of a Debug.Print) and paste it anywhere on the worksheet Excel automatically splits the string and reformats the values.
Although my code works perfectly I was wondering whether there is a shorter way of doing this, i.e. by just coding up the above action? I've tried a few routes but haven't found a solution yet.
1. In whatever cell is selected when the macro is run, enter a new row.
2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.
3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.
I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
Lets say we have prices in column (B) and in column (F) I want to insert a formula that says:
In the same row, look at cell in column (B). If price is between 1000 and 8000 then put 2.00 if price is between 500-999 then put 0.50 if price is between 1-499 then put 0.00
I need to achieve this using something like =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1) but I am not sure how its done.
I will be using this formula in a find and replace macro. So I can NOT make the typical drag and fill formula.
with a macro. I am looking to copy row 2 to the last row and past the copied rows directly below the copied contents.
View 9 Replies View RelatedA spreadsheet im currently working on has the following formula
=SUMPRODUCT(--('Data Entry'!G2:G6000="AAAA"),--('Data Entry'!L2:L6000="Jun 14"),--('Data Entry'!M2:M6000))
It works fine, apart from the bolded text. In the Data Entry worksheet, column L has dates in the format dd/mm/yyyy. I'd like to get any date from June to be included without manually changing every date in June to Jun 14.
I think theres 3 ways of doing this, any of which would be great. Heres what I think. Where "Jun 14" is searching for exact text, I thought maybe I could change it to MONTH=6 or something to pick out June direct from a dd/mm/yyyy format. I couldnt get this to work. Know of anything I could replace "Jun 14" with?I tried to change the format of the entire column L in 'Data Entry' to a 3 letter month and 2 letter year. how to change the cell date format to fit this?Or if I changed the format of the dates to "01 June 2014" could I change my formula to try and just pick out the specific text "June" ignoring the day and year?
I know there must be a way how, but I can't seem to get it right.
The other option I thought of was to create a new column in the Data Entry sheet using the formula =MONTH(A1), then changing the formula to search for "1", "2", "3" etc for each month. I didn't do this as for the purpose of this the exact date is NOT important, only the Month is.
I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.
The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.
I am using the following ....
This macro works fine on my machine but not with other users:
This should copy/paste certain cells then paste 3 sheets into a new work book.
ON other computers it seems to paste in a picture? works OK for me?
Sub ValidationTests()
'
' ValidationTests Macro
' Macro recorded 21/12/2011 by '
'
Sheets("Score Sheet").Select
Range("A8:M18").Select
Range("H18").Activate
Selection.Copy
[Code] ..........
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
I m working with office 2003 pro in Windows XP.
2 Excel questions:
Is there a way to make the "paste" option in excel "sticky" (that is, so that you can do some other work in excel then come back and click "paste"? currently excel makes you go back and recopy then only can you paste. You cannot do anything between "copy" and "paste".
Is there a way to paste so that the cell is exactly copied? What i mean is, so that the formulas that are copied are pasted exactly as they were (without changing references)? (i get the right result when draggin and moving a cell, except that the original cell is emptied - but u get the idea). A work around is to add absolute refrences, and then take them away later - or to copy the formula in the formula bar and then paste it into another cell (one cell at a time ... )
I have looked at previous posts and found some information about ActiveSheet.Paste errors, which appear to be related to having activate, select and paste functions in the wrong order.
I am still getting an error at the ActiveSheet.Paste point of my code which I cannot seem to overcome, although this same code works on another macro doing exactly the same action...
ChDir _
"\workgroupHighlight_reportingTemplates"
Workbooks.Open Filename:= _
"\workgroupHighlight_reportingTemplatesProgramme report template.xls"
I'm trying to copy an ELOOKUP formula down a column in a sheet. The formula is losing part of the table array when I carry it down and returning an N/A error. Interestingly, it will work when I copy it across a row.
View 9 Replies View RelatedI can't figure out whats wrong with this code:
[Code] ......
Its give me error 1004 on this line:
[Code] ........
Its copying the data just fine, but its having issues with pasting it. The data should be pasted into the same corresponding columns (so also starting in A Column), in the next available row (in Column C from row 2 on).
I'm running Excel 2003 and I have a workbook with an Auto List in Column G. When I attempt to paste a row of data I get the following message.
The operation is not allowed. the operation is attempting to shift cells in a list on your worksheet.
To work around this, I have to convert the list to a range, copy the data and then reapply the Auto List.
Is there another way to work around this or will I have to create vba code or record a macro?
I have a problem with a macro that runs on a command button. When I click the button, I get a 'Run Time Error 1004, Paste mathod of worksheet class failed' The code is as follows:-
View 3 Replies View RelatedWhy this code doesnt work. I'm trying to copy data from one workbook into a specific row in a different workbook. Stepped through the code and get caught out at the last step, which is pasting as values in the 2nd sheet.
The code is posted below. Note: this macro is called from another one and therefore both files are already open. First bit of the code relates to the opened file (not referenced here)
PHP Code:
Sub compilation()Dim lastrow As LongDim r As RangeDim AWB As WorkbookDim lastrow2 As LongSet
AWB = ActiveWorkbookWith AWB.Sheets(1)
lastrow = Range("A" & Rows.Count).End(xlUp).Row
[Code] .........
All works fine until the pastespecial bit. Then I get a 1004 PasteSpecial method of Range class failed.
The procedure below worked quite happily for two months. Now it (usually, but frustratingly not always) fails, with the following error on the asterixed line:
Paste Method of Worksheet Class Failed
If anyone has the inclination, please could you put me out of my misery and suggest what is going wrong, as I have been trying on and off for two weeks to rectify this. What disheartens me is, as I say, I have had no problems with this procedure up until a couple of weeks ago. It takes the ordering info for a particular day and pastes it into the correct day's sheet on the invoicing spreadsheet. Sometimes, when I re-run the procedure it then works......???
Sub Transfer_To_Invoicing()
Dim answer As Variant, active_workbook As Variant
day_num = Range("a127").Value
answer = MsgBox("Do you want to transfer " & Weekday(day_num) & "'s figures to the INVOICING SYSTEM now?", vbYesNo)
If answer vbYes Then Exit Sub
answer = MsgBox("Have you updated the values from the despatch sheets?", vbYesNo)
If answer vbYes Then Exit Sub
Range("a1:dj129").Copy
active_workbook = ActiveWindow.Caption
For Each w In Workbooks.............
I was wondering if it's possible to unhide a row directly below one that is visible?
I am creating a template for users that allows them to choose which lines (rows) they would like to use. They then hit a button to hide all the other lines they don't wish to use.
For each row that is visible I then need the row directly below it to become visible so that the user can enter associated information.
I'm stumped on how to do it. I have the user entering "a" in column A for any row they wish to keep - but I'm not sure how to incorporate this into unhiding the row below (or if I'm on the wrong track with my thinking).
I am trying to use the Special Cells Goto and Paste, but I am getting an error saying 'this operation requires the merged cells to be identically sized' when I do the Copy the value that I want, Goto Special|Constants, then Edit|Paste Special|Values|Multiply. The cells containing constants are selected and if they are together, e.g. B7 & B8, then there is no problem, but if they are apart e.g. B7 & B9, I get the error.
View 5 Replies View RelatedI've created a form and would like to use a macro to transfer the form information details (form) from an intermediate worksheet (database intermediate) to the main database worksheet (database).
So far I've only been able to record a simple macro that will do just that. However, when I click on my assigned macro again, the information is washed away and the new form details have replaced it. Been trying to use the x1down coding to make it such that each new data goes onto the subsequent line? But I can't seem to get it right without getting an error message.
I've attached the file to give you a better picture,
I'm working in a rather large (20meg) Excel file with multiple worksheets, references, formatting, etc.
Suddenly, when I attempt to Copy > Paste Special > Formats using more than a few cells at a time, I receive this error message...
"Selection is too large."
That's it - no reference, no nothing. Doesn't matter if I select a few cells, or entire rows, I get the same message everytime. However, I can copy-n-paste the entire 8000+ rows of data into a NEW file with no problems whatsoever. It handles everything - data, formatting, formulas, etc. But within my working file (same worksheet even) I get the error everytime.
I'm not using a macro. This file has worked fine for months. I'm doing absolutely nothing differently than ever before. This error literally just started today.
I know there is some issue with copy/paste of 8100+ non-contiguos cells, but I'm using contiguos cells. And again, I can copy nearly 10K entire rows of data and paste into a new workbook - just not within the same workbook and worksheet.
I've searched online - here, Mr.Excel, Microsoft, everywhere I can think of. Of all the problems I've found answers too, I have yet to find ANYTHING dealing with this problem - except in the context of a macro, which I am not using.
How I can control many loops directly after each other. For example I have this script:
If (Cells(x, column) > 35 And Cells(r, column) < 25 Then
If (cells(x, 75) = "GotU") Then
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Else
If (cells(x, 85) = "GotYah") Then
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Else
If (cells(x, 95) = "GotYou") Then
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
End if
Is this script correct if I want the loops to check all data that I write (for ex. GotYou, GotYah etc.) or do I need to end every if before next if? I want that the loops makes all 3 controls.
I've been working with this script to import and paste a webpage to a worksheet. Once the webpage is on a worksheet I can pull it into VBA easily enough but I was wondering if there's a way to "paste" the webpage directly into an array in VBA and skip the paste to worksheet step?
Code:
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate theURL
IE.Visible = False
End With
[Code]....
I have a validation box that has 371 names it using the full range of the alphabet.
Is there away within the validation box that instead of using the scroll bar that you can insert a letter or letters to take you to the name directly?
I have developed an extensive program running on excell but with the user only seeing userforms. I would now like to build in a "print screen" button, but have no idea how to do this.
What it needs to do is capture the userform and all its entries (as it is displayed on the screen) and send this to the printer to be printed as a picture.
I am assuming that there must be code available so that when ever the user clicks this button, the standard windows (or excell) print screen appears from which he will be able to choose the printer etc.
I have found some code that allows me to email directly from within a workbook, but the example only contains one addressee, I wish to send to 3 or 4 different addresses, these addresses would remain constant once entered. How do I alter the code?
Sub sendactiveworkbook()
ActiveWorkbook.SendMail _
Recipients:="type-in-email-address", _
Subject:="New Quote Raised - Test Transmission " & Format( Date, "dd/mmm/yy")
End Sub
I have a spreadsheet which I have formatted with validation rules for each cell. I have protected the sheet so that only certain cells can have info entered.
I also have a second spreadsheet with a bunch of data. If I copy this data and Paste Special it into my specially formatted sheet (Values only), all of the data copies over regardless of my preset validation rules.
I would like error messages to pop up for every incorrect value though.
I have been working on this macro all day and no matter how many different ways I write it I can't seem to get it to finish correctly without giving me an error message. The error messages are different depending on how I write the macro, so what the message says is not important.
I have attached a sample of my work which contains the macro, but I'll post the code below as well. There are two tabs in the workbook. One contains the data("download") and the other is the template("Blank") per say. I need the macro to create a new worksheet using the template for each line of the data ("download")until it gets to the "finalrow." It names the worksheets the value of D3 currently, but I'd like to figure out how to name the worksheets by C3. c3 contains the names of customers, and is duplicated for each product they have purchased. I'd just like to attach a number after the name if it is a duplicate. i.e. Adam, Adam1, Adam2, Adam3 for all "Adam"s.
Look into the code and see if that makes sense. if not, feel free to ask questions.
I am looking for some example code that when I get an error using workbook.open, because a file is corrupt, I can cut from the list and paste into a range to show that file had errors, then delete that row and continue.
For example
My list:
Column A Column B Errors List Error
File path B and name Information File Path A Information
File path C and name Information
File path D and name Information
I can cut and paste File path C into an errors list, then delete the row A and B and shift the below lines up.
I am trying to execute the following code, but I get a runtime error 1004 (paste special method of range class failed).
Code:
Sub ReviewedStatusReport()
'Define variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
[Code] ........