Macro

[sa02000]

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

[David McRitchie]

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" <[email protected]> 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]

[sa02000]

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.

[David McRitchie]

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...TNGP15.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" <[email protected]> wrote in message
news:[email protected]...
>
> 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.

[sa02000]

Dave,
Thank you very much. It works great.

Jay