Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Can anybody please help me write a code to do this in excel macro? I tried recording a macro while I was doing auto filter but that does not work very well for a generic code. I am not copying my code to keep the confusion away. IN EACH CELL OF A COLUMN COPY THE FORMULA FROM PREVIOUS CELL (IN THE SAME COLUMN) IF THE ADJACENT CELL ON LEFT HAS A VALUE OTHERWISE STOP. SO, FOR EXAMPLE IF I AM IN CELL I3. I WANT TO CHECK IF H3 IS EMPTY OR NOT, IF H3 HAS A VALUE (JUST ANYTHING) THEN COPY FORMULA FROM I2 INTO I3. AND DO SAME FOR I4 WHICH IS, CHECK IF H4 IS EMPTY OR NOT, IF H4 HAS A VALUE THEN COPY FORMULA FROM I3 INTO I4 AND WHEN AN EMPTY CELL IS ENCOUNTERED IN COLUMN H THEN STOP. THEN PARSE THROUGH ALL THE CELLS IN COLUMN I AGAIN AND IF A CELL HAS #N/A VALUE THEN FILL CELL WITH SOMETHING OR IF A CELL HAS SOMETHING ELSE THEN DO NOTHING AND GO TO NEXT CELL OR IF CELL IS EMPTY THEN STOP. Thank you very much. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=474794 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Hi ....,
I'm not going to read all of that capital letter stuff and parse it into sentences, paragraphs etc. It looks like you are trying to simulate Fill Down in an adjacent column -- if that is the case then take a look at Filldown as a Macro solution (#filld) http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld I would certainly think that Excel Forum would include some newsgroup etiquette since your are posting through them to the Microsoft hosted newsgroups, All caps is never acceptable in any newsgroup I've been in, it is extremely hard to read -- they may refer to as shouting -- but it is extremely hard to read in any case. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "sa02000" wrote ... Can anybody please help me write a code to do this in excel macro? I tried recording a macro while I was doing auto filter but that does not work very well for a generic code. I am not copying my code to keep the confusion away. IN EACH CELL OF A COLUMN COPY THE FORMULA FROM PREVIOUS CELL (IN THE [clipped] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Dave, it works great. Thank you very much. I apologize for caps in my original post. But I am not sure how you ha capital letters in your post where as my post did not show capita letters. I had no intention of being rude or shouting at anyone a all. I did try to make the part of my request bold so that it stands out an makes it little easier to read. Also, you got a macro for second request that I had in my origina post. Here is the request again. Parse through all the cells in column, lets say, I and if a cell ha #N/A value then fill cell with some value or if a cell has somethin else then do nothing and go to next cell or if cell is empty then stop -- sa0200 ----------------------------------------------------------------------- sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774 View this thread: http://www.excelforum.com/showthread.php?threadid=47479 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Hi "sa02000",
There is no boldface in a text newsgroup perhaps your ExcelForum changes them when sending. Here is the thread in Google Groups. http://groups.google.com/groups?thre...GP15.phx .gbl There is no loop in the two macros within the previously referenced topic on my webpage. If I understand the second part I think that can be done without a loop as well. Select cell at top of data in column to be worked Ctrl+Shift+ArrowDown will select cells down to a blank cell and will include error cells Ctrl+G (edit, goto), constants and [x] errors -- will have to add error statment Del key to clear errors type in 33 into the first occurrence of error Ctrl+D to fill down created data in f3:f20 cleared F18 #n/a into cell in F5 and F7 =#n/a into cell F11 and F15 select cell f3 and run macro Sub Macro12() Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeConstants, 16).Select msgbox err.code exit sub Selection.ClearContents ActiveCell.FormulaR1C1 = "33" Selection.FillDown End Sub Sub Macro12() Dim rng As Range '-- added Set rng = Selection '-- added Range(Selection, Selection.End(xlDown)).Select On Error Resume Next '-- added Selection.SpecialCells(xlCellTypeConstants, 16).Select If Err.Number = 0 Then '-- added it is not 1004 Selection.ClearContents ActiveCell.FormulaR1C1 = "33" Selection.FillDown End If rng.Select '-- added Range(Selection, Selection.End(xlDown)).Select On Error Resume Next '-- added Selection.SpecialCells(xlCellTypeFormulas, 16).Select If Err.Number = 0 Then '-- added it is not 1004 Selection.ClearContents ActiveCell.FormulaR1C1 = "33" Selection.FillDown End If '-- added End Sub -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "sa02000" wrote in message ... Dave, it works great. Thank you very much. I apologize for caps in my original post. But I am not sure how you had capital letters in your post where as my post did not show capital letters. I had no intention of being rude or shouting at anyone at all. I did try to make the part of my request bold so that it stands out and makes it little easier to read. Also, you got a macro for second request that I had in my original post. Here is the request again. Parse through all the cells in column, lets say, I and if a cell has #N/A value then fill cell with some value or if a cell has something else then do nothing and go to next cell or if cell is empty then stop. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Dave, Thank you very much. It works great. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=474794 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |