There is a given number in cell U1, which equals to 9, so I would like to copy 9 cells starting from cell V3, which should copy from V3 to V11 and paste text only into A3 to A11. If the given number in cell U1 is 6, then I would like to copy 6 cells starting from cell V3, which should copy from V3 to V11 and paste text only into A3 to A8. Does anyone have any suggestions on how to code this macro in excel 2003? Thanks in advance for any suggestions Eric

0 |

4/30/2010 5:29:01 AM

Hi Eric Try.. Range("V3").Resize(Range("U1").Value).Copy Range("A3") -- Jacob (MVP - Excel) "Eric" wrote: > There is a given number in cell U1, which equals to 9, so > I would like to copy 9 cells starting from cell V3, which should copy from > V3 to V11 and paste text only into A3 to A11. > > If the given number in cell U1 is 6, then > I would like to copy 6 cells starting from cell V3, which should copy from > V3 to V11 and paste text only into A3 to A8. > > Does anyone have any suggestions on how to code this macro in excel 2003? > Thanks in advance for any suggestions > Eric

0 |

4/30/2010 5:41:01 AM

When you say "text only"... do you mean cells with numbers should be formatted as text, or is that your way of saying the cells you are copying have formulas in them but you only want to copy the values the formulas are displaying? -- Rick (MVP - Excel) "Eric" <Eric@discussions.microsoft.com> wrote in message news:B7278965-9B70-4C51-A512-D0A4344BB914@microsoft.com... > There is a given number in cell U1, which equals to 9, so > I would like to copy 9 cells starting from cell V3, which should copy from > V3 to V11 and paste text only into A3 to A11. > > If the given number in cell U1 is 6, then > I would like to copy 6 cells starting from cell V3, which should copy from > V3 to V11 and paste text only into A3 to A8. > > Does anyone have any suggestions on how to code this macro in excel 2003? > Thanks in advance for any suggestions > Eric

0 |

4/30/2010 5:43:35 AM

What if I would like to copy a range V3:Z3, For example There is a given number in cell U1, which equals to 9, so I would like to copy 9 rows starting from cell V3:Z3, which should copy from V3:Z3 to V11:Z11 and paste text only into A3 to E11. Does anyone have any suggestions? Thank you very much for any suggestions Eric "Jacob Skaria" wrote: > Hi Eric > > Try.. > > Range("V3").Resize(Range("U1").Value).Copy Range("A3") > > -- > Jacob (MVP - Excel) > > > "Eric" wrote: > > > There is a given number in cell U1, which equals to 9, so > > I would like to copy 9 cells starting from cell V3, which should copy from > > V3 to V11 and paste text only into A3 to A11. > > > > If the given number in cell U1 is 6, then > > I would like to copy 6 cells starting from cell V3, which should copy from > > V3 to V11 and paste text only into A3 to A8. > > > > Does anyone have any suggestions on how to code this macro in excel 2003? > > Thanks in advance for any suggestions > > Eric

0 |

4/30/2010 6:04:01 AM

Try the below...I hope you meant A3 to B11 and not A3 to E11. Range("V3").Resize(Range("U1").Value,2).Copy Range("A3") Also check out help on Resize() and Offset() functions.... -- Jacob (MVP - Excel) "Eric" wrote: > What if I would like to copy a range V3:Z3, > For example > There is a given number in cell U1, which equals to 9, so > I would like to copy 9 rows starting from cell V3:Z3, which should copy from > V3:Z3 to V11:Z11 and paste text only into A3 to E11. > > Does anyone have any suggestions? > Thank you very much for any suggestions > Eric > > > "Jacob Skaria" wrote: > > > Hi Eric > > > > Try.. > > > > Range("V3").Resize(Range("U1").Value).Copy Range("A3") > > > > -- > > Jacob (MVP - Excel) > > > > > > "Eric" wrote: > > > > > There is a given number in cell U1, which equals to 9, so > > > I would like to copy 9 cells starting from cell V3, which should copy from > > > V3 to V11 and paste text only into A3 to A11. > > > > > > If the given number in cell U1 is 6, then > > > I would like to copy 6 cells starting from cell V3, which should copy from > > > V3 to V11 and paste text only into A3 to A8. > > > > > > Does anyone have any suggestions on how to code this macro in excel 2003? > > > Thanks in advance for any suggestions > > > Eric

0 |

4/30/2010 6:08:02 AM

After I try following code, and see the code display on running macro, but text disappears after re-calucation, do you have any suggestions on what wrong it is? Thanks in advance for any suggestions Eric "Jacob Skaria" wrote: > Hi Eric > > Try.. > > Range("V3").Resize(Range("U1").Value).Copy Range("A3") > > -- > Jacob (MVP - Excel) > > > "Eric" wrote: > > > There is a given number in cell U1, which equals to 9, so > > I would like to copy 9 cells starting from cell V3, which should copy from > > V3 to V11 and paste text only into A3 to A11. > > > > If the given number in cell U1 is 6, then > > I would like to copy 6 cells starting from cell V3, which should copy from > > V3 to V11 and paste text only into A3 to A8. > > > > Does anyone have any suggestions on how to code this macro in excel 2003? > > Thanks in advance for any suggestions > > Eric

0 |

4/30/2010 6:14:01 AM

Do you have any other worksheet/workbook events .. Try this in a new workbook -- Jacob (MVP - Excel) "Eric" wrote: > After I try following code, and see the code display on running macro, but > text disappears after re-calucation, do you have any suggestions on what > wrong it is? > Thanks in advance for any suggestions > Eric > > "Jacob Skaria" wrote: > > > Hi Eric > > > > Try.. > > > > Range("V3").Resize(Range("U1").Value).Copy Range("A3") > > > > -- > > Jacob (MVP - Excel) > > > > > > "Eric" wrote: > > > > > There is a given number in cell U1, which equals to 9, so > > > I would like to copy 9 cells starting from cell V3, which should copy from > > > V3 to V11 and paste text only into A3 to A11. > > > > > > If the given number in cell U1 is 6, then > > > I would like to copy 6 cells starting from cell V3, which should copy from > > > V3 to V11 and paste text only into A3 to A8. > > > > > > Does anyone have any suggestions on how to code this macro in excel 2003? > > > Thanks in advance for any suggestions > > > Eric

0 |

4/30/2010 6:20:01 AM

For values.. Range("V3").Resize(Range("U1").Value, 2).Copy Range("A3").PasteSpecial xlPasteValues Application.CutCopyMode = False -- Jacob (MVP - Excel) "Jacob Skaria" wrote: > Try the below...I hope you meant A3 to B11 and not A3 to E11. > > Range("V3").Resize(Range("U1").Value,2).Copy Range("A3") > > Also check out help on Resize() and Offset() functions.... > > -- > Jacob (MVP - Excel) > > > "Eric" wrote: > > > What if I would like to copy a range V3:Z3, > > For example > > There is a given number in cell U1, which equals to 9, so > > I would like to copy 9 rows starting from cell V3:Z3, which should copy from > > V3:Z3 to V11:Z11 and paste text only into A3 to E11. > > > > Does anyone have any suggestions? > > Thank you very much for any suggestions > > Eric > > > > > > "Jacob Skaria" wrote: > > > > > Hi Eric > > > > > > Try.. > > > > > > Range("V3").Resize(Range("U1").Value).Copy Range("A3") > > > > > > -- > > > Jacob (MVP - Excel) > > > > > > > > > "Eric" wrote: > > > > > > > There is a given number in cell U1, which equals to 9, so > > > > I would like to copy 9 cells starting from cell V3, which should copy from > > > > V3 to V11 and paste text only into A3 to A11. > > > > > > > > If the given number in cell U1 is 6, then > > > > I would like to copy 6 cells starting from cell V3, which should copy from > > > > V3 to V11 and paste text only into A3 to A8. > > > > > > > > Does anyone have any suggestions on how to code this macro in excel 2003? > > > > Thanks in advance for any suggestions > > > > Eric

0 |

4/30/2010 6:28:01 AM

Value only without formula Thank everyone very much for suggestions Eric "Rick Rothstein" wrote: > When you say "text only"... do you mean cells with numbers should be > formatted as text, or is that your way of saying the cells you are copying > have formulas in them but you only want to copy the values the formulas are > displaying? > > -- > Rick (MVP - Excel) > > > > "Eric" <Eric@discussions.microsoft.com> wrote in message > news:B7278965-9B70-4C51-A512-D0A4344BB914@microsoft.com... > > There is a given number in cell U1, which equals to 9, so > > I would like to copy 9 cells starting from cell V3, which should copy from > > V3 to V11 and paste text only into A3 to A11. > > > > If the given number in cell U1 is 6, then > > I would like to copy 6 cells starting from cell V3, which should copy from > > V3 to V11 and paste text only into A3 to A8. > > > > Does anyone have any suggestions on how to code this macro in excel 2003? > > Thanks in advance for any suggestions > > Eric > > . >

0 |

4/30/2010 6:44:01 AM

It works now, thank everyone very much for suggestions Eric "Jacob Skaria" wrote: > For values.. > > Range("V3").Resize(Range("U1").Value, 2).Copy > Range("A3").PasteSpecial xlPasteValues > Application.CutCopyMode = False > > -- > Jacob (MVP - Excel) > > > "Jacob Skaria" wrote: > > > Try the below...I hope you meant A3 to B11 and not A3 to E11. > > > > Range("V3").Resize(Range("U1").Value,2).Copy Range("A3") > > > > Also check out help on Resize() and Offset() functions.... > > > > -- > > Jacob (MVP - Excel) > > > > > > "Eric" wrote: > > > > > What if I would like to copy a range V3:Z3, > > > For example > > > There is a given number in cell U1, which equals to 9, so > > > I would like to copy 9 rows starting from cell V3:Z3, which should copy from > > > V3:Z3 to V11:Z11 and paste text only into A3 to E11. > > > > > > Does anyone have any suggestions? > > > Thank you very much for any suggestions > > > Eric > > > > > > > > > "Jacob Skaria" wrote: > > > > > > > Hi Eric > > > > > > > > Try.. > > > > > > > > Range("V3").Resize(Range("U1").Value).Copy Range("A3") > > > > > > > > -- > > > > Jacob (MVP - Excel) > > > > > > > > > > > > "Eric" wrote: > > > > > > > > > There is a given number in cell U1, which equals to 9, so > > > > > I would like to copy 9 cells starting from cell V3, which should copy from > > > > > V3 to V11 and paste text only into A3 to A11. > > > > > > > > > > If the given number in cell U1 is 6, then > > > > > I would like to copy 6 cells starting from cell V3, which should copy from > > > > > V3 to V11 and paste text only into A3 to A8. > > > > > > > > > > Does anyone have any suggestions on how to code this macro in excel 2003? > > > > > Thanks in advance for any suggestions > > > > > Eric

0 |

4/30/2010 7:11:01 AM

Is there a way to set the default to autoformat comments to move & size with cells? In a big file, it can take a long time to edit comment by comment. Not that I know of. But Debra Dalgleish has a macro approach that puts them back in there place! http://www.contextures.com/xlcomments03.html#Reset KMiles wrote: > > Is there a way to set the default to autoformat comments to move & size with cells? In a big file, it can take a long time to edit comment by comment. -- Dave Peterson ec35720@msn.com ...

i have a formula composed of 3 lines in one cell: =" IF c_MSISDN_NDC" & $E3 & " IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 1, c_oper_name);'); utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 2, c_oper_name);');" this produces a text string, e.g. as below: " IF c_MSISDN_NDC70 IS NOT NULL THEN utl_file.put_line (l_file_handle, ' '||c_pkg||LOWER(l_curr_t...

there is a column with values like 23 67 1 89 34 56 now the min() would give me 1 . I discard 1 and I want to search fo the smallest value in the remaining values. how do I do this? Please suggest a formula regards, betz -- betz ----------------------------------------------------------------------- betzi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1508 View this thread: http://www.excelforum.com/showthread.php?threadid=26718 Hi what is the logic to skip values=? do you want to skip only '1' -- Regards Frank Kabel Frankfurt, Germany "be...

Hello all, I have a row of 9 cells, which I want to fill with the numbers 1 to 9. Each time I put a number in a cell I want excel to compare all 9 cells in the row. If I type in a number that is already in one of the other 8 cells I want it to light up in red. I tried using "conditional formatting" but I can only compare 2 cells at a time. Does anybody have an idea? Thanks in advance, Lucas try the countif function in your conditional formating "Lucas" wrote: > Hello all, > > I have a row of 9 cells, which I want to fill with the numbers 1 to 9. Each >...

I have a spreadsheet with 13 worksheets. On the first worksheet ("summary") I have a cell (A1) that contains the year, "2005", in 24point red font. I would like to reference it in each of the other 12 worksheets as "=summary!a1" and have this copy the value, "2005", and the formatting. Is this possible? Thank you. Hi! When you link to a cell with a formula, (=summary!A1 is a formula), only the value of that cell is returned, not the formatting. Try this... Right click any sheet tab and select: Select all sheets In the window title bar you w...

I am working with a spreadsheet created from our mainframe and need some assistance in converting it to a file easily used for data mining. The records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for each header row, 3=sum count of the # of detailed records. Here is a sample of the data: A B C D E F 1 Test Texas PlanNumber RedLight 2 John Addy State ZipCode Expense 2 Sally Addy State ZipCode Expense 2 Jake Addy State ZipCode Ex...

Hi all. When i print an excel document, i realise that 2 cells have data that is not printed out. However when I use print preview, i do not find any missing data. I am using excel 2003. How can i solve this? ...

Can anyone help me with this question? I want to enter text on merged cells. The wrap text box is checked. When I enter the text it looks ok and like it should fit within the merged cells but when I go out of the merged cells it displays: ###############################. Thanks Try formatting the cell as General (instead of Text). David Pincus wrote: > > Can anyone help me with this question? I want to enter > text on merged cells. The wrap text box is checked. > When I enter the text it looks ok and like it should fit > within the merged cells but when I go out of ...

I have entered in the cells A1:B10 numbers in column A, and names in the column B. In the range A20:A25 there are six phone numbers and I want Excel to show -if the number does exist- the names in the cells B20:B25 or -if not- an empty string I was trying the array formula: {=IF(A20=$A$1:$A$10;$B$1:$B$10;"")} written in the cell B20 to -if worked- be then copied and pasted on the following B21:B25 cells. But doesn't work. Is the first time I tried an array formula. Could somebody tell me how to do it? Thanks Jaime Oviedo-Spain ...

I track jobs for techs page one example. Excel 2003 JOB TECH ACCT# STAT ADDRESS 1B 603 162395-7 CP 6844 N DE CHELLY LOOP 3B 617 164655-11 CP 10700 N LA RESERVE DR # 2106 5B 603 267454-1 CP 7270 S SAND DUNE VLY D 4B 634 131976-3 CP 5702 N CAM LAGUNA 3B 609 193005-2 CP 4961 N DIAMOND PL 16B 650 267451-1 XO 9950 N CAM DEL PLATA 1B 636 206822-7 CP 7525 W SUMMER SKY DR 10B 607 120813-8 CP 3028 W WYOMING ST 2B 609 100114-2 RS$ 6430 N MONTROSE DR 3B 603 168731-9 CP 4156 N RIO CANCION # 33 3B 626 231452-8 CP 7990 E SNYDER RD 11 107 I want to take all of ie 603 info in...

Hi, I have an excel file that always come with a line at the end with "downloaded:" in the cell. How can I write a macro to search for that cell and delete the row that cell is in? Thanks for all your help! Any time you are in doubt on how to interact with excel, try recording a new macro and then physically do the steps you are trying to code. Stop the recording and then go look at the recorded macro www.jameswesleybluesband.com Hi "mchen" mchen@gellerco.com, Delete rows with "N" in Column 31 (col AE) from my http://www.mvps.org/dmcritchie/excel/delem...

Hi, I have a PivotTable with two columns - student names and tuition. I highlighted the **grand total tuition** with fill color. Please note that only ONE cell had this format. However, when I changed the column of student names to a row of student names (by moving the "button"), all the tuition (and not just the grand total) had the fill color. Is this a bug? Is there a way to get around this? I know I can remove the fill color first, change from column to row then put back the fill color. Just wondering if there is a better way. Appreciate advice. Epinn ...

i have a worksheet with very long rows (a to cf). For printing i would like to wrap the rows to fit on a single page, and then seperate each set with a space. I would also like to wrap the headers too, of course. is this possible? Say you're creating 10 columns per new row. So it kind of looks like this: $A$1 $B$1 $C$1 $D$1 $E$1 $F$1 $G$1 $H$1 $I$1 $J$1 $K$1 $L$1 $M$1 $N$1 $O$1 $P$1 $Q$1 $R$1 $S$1 $T$1 $U$1 $V$1 $W$1 $X$1 $Y$1 $Z$1 $AA$1 $AB$1 $AC$1 $AD$1 $AE$1 $AF$1 $AG$1 $AH$1 $AI$1 $AJ$1 $AK$1 $AL$1 $AM$1 $AN$1 $AO$1 $AP$1 $AQ$1 $AR$1 $AS$1 $AT$1 $AU$1 $AV$1 $AW$1 $AX$1 $AY$1 $...

Excel 2000 I have a spreadsheet with four columns: July, August, September, October. Various cells have a number in them, some are blank. When all four cells in a row are a blank, I want to delete that row. So, if row 10 for July, August, September and October are blank, I want to delete that row. How do I do that? -- Howard Hi one way: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows -- Regards Frank Kabel Frankfurt, Germany "Howard" <DFM@discussions.microsoft.com> schrieb im Newsbeitrag news:668B7816-587C-45D4-8840-CD701F379CB5@microsoft.com... > Excel 200...

Hello, Sorry for posting 2 times. Date & time in my computer were not correctly set. In addition to what I've written before, here is the code I wrote: Sub Dataacquire() Workbooks.Open "data.xls" Worksheets("sheet1").Range("b1:d19").Copy _ ThisWorkbook.Worksheets("sheet1").Range("b1:d19") ActiveWorkbook.Close SaveChanges:=False End Sub My problem that empty cells are not copied as empty and because of this I get an error messages in other worksheets (like #VALUE). How Can I solve this problem ? Thank you David Cohen ...

This is a line of code that i use to create folders on my computer with what ever value B3 is, now what i would like to do is by clicking a button have it look under a certain folder and audomaticlly enter the next 5 digit number and create a folder with that number and put it into a cell in my worksheet like this - 10001, 10002, 10003, etc...... MkDir "C:\\Globe Photo Engraving\Jobs\" & Range("B3").Value David, I have assumed the sheet name is Sheet1 - enter the starting number in cell B3 there, and run this macro: Sub CreateFolder() Dim MyFilePat...

I want to set the number of recently used files in Publisher to nine from the four that it shows me at present. I can't find anywhere to change this. I'm using Publisher 2003. I don't think you can do that in Publisher but I'm sure that someone will chirp up if I'm mistaken. -- JoAnn Paules MVP Microsoft [Publisher] "Ken" <Ken@discussions.microsoft.com> wrote in message news:07E5ABDF-9C44-4091-8620-1AA38931EF91@microsoft.com... >I want to set the number of recently used files in Publisher to nine from >the > four that it shows me at prese...

I need to convert address information, which is on word and listed vertically, to an excel spreadsheet, and have the information list out horizontally. Is this possible to do? I am familiar with the basics of each program, but using "range,formula,value" options in excel confuses me. Here's an example: (My data in word) Mel's Tire Store, Inc. 300 Culbertson Avenue Worland, WY 82401 (307) 347-3601 Need to copy and paste so data spreads horizontally in Excel, under these colum headings: "Business Name" "Address" "City&qu...

Hi. Having trouble adding minutes to my account. Anyone have a phone number or e-mail for either Telefonica or Windows Live Call that I can contact for help? Thanks! Greetings, Supposedly, if you head over to: http://support.live.us.telefonica.com/?Country=US&Language=en Choose Payment and Balance Problems on the side, and then choose one of the "problems" listed, then choose " If you are still having trouble after the above steps, please Click Here ", you'll reach a form to assist. -- Jonathan Kay Microsoft MVP - Windows Live Messenger MSN Mes...

I am stuck at a problem where I want to export emails with specific "TO" ( can be a Distribution List). And I expect the excel to have the email address of the sender and the time at which the email was received. Is there a way to do this? I have looked on several forums and sites, but unable to find something like this. My ultimate goal is to track the emails coming in my Microsoft outlook sent to me Distribution List and export them in an excel sheet. The objective is to look for the number of emails recieved and the time/date at which it was received.. I a...

I have a two cells one with a 'number' value and another with a 'text' value i would like to have the number cell add up based on the text value. ie. 1 2 3 4 A 2 S B 2 S C 3 SM So then, because row A and B's text is S they will be added together to = 4. is there a way where i can make a formula or a table to calculate this? Z First, sort your data by that second column, so all the S's are grouped together. Then add a single header row at the top of your data. Select that range (all the rows and all the columns you need) and do:...

My autocad drawing is linked to a excel spreadsheet. For fast updating. But the window displayed in autocad changes if I have more than on excel sheet. As I switch from auotcad to excel is there a way to permently save the excel window display and make another window display and alway keep the window the same as you switch from one software to the other ...

I have 2 worksheets in my workbook. On worksheet "A" i have a number in red font with black background in cell B1. On worksheet "B" the contents of cell A1 is =A!B1. This copies the value located in cell B1 of worksheet "A" but not the color formatting. How can that get copied as well? Hi A formula has one single job: to return a value into its own cell. No formats, no copying, no dancing, no fun, only that. -- HTH. Best wishes Harald Followup to newsgroup only please "Mark" <mark@lbrgroup.com> skrev i melding news:32B8AA2D-DA1D-4B3F-AF73-9...

I have to export a spreadsheet from another data base. The numbers come over as ##-####-##-#, and I am unable to change their format. Does anyone know how to change this number to #########? Thank you! You could try this on a copy of your workbook..... Highlight the column, then Edit > Replace > FindWhat: - > ReplaceWith: Leave blank > ReplaceAll Vaya con Dios, Chuck, CABGx3 "Kamron" <Kamron@discussions.microsoft.com> wrote in message news:703B54A5-A645-48D3-A22C-B9D29454484B@microsoft.com... >I have to export a spreadsheet from ...

I have a table with fields: DT, INVOICE, AMT Now invoice numbers are different unique keys but not numeric, how to add a fourth temporary column in a query to insert serial numbers as well like: Serial, DT, INVOICE, AMT 1 2 3 4 .... -- Thanx & Best Regards, Faraz! hi Faraz, On 01.03.2010 09:07, Faraz A. Qureshi wrote: > Now invoice numbers are different unique keys but not numeric, how to add a > fourth temporary column in a query to insert serial numbers as well like: > > Serial, DT, INVOICE, AMT > 1 > 2 > 3 > 4 Using DCount(): ...