new to macros

Posted by saradhi on March 28, 2001 2:48 AM

hi everybody,
I am new to macros in excel and programming in VB as such.
I need help with macros in excel. My problem is the following.
1. I have three columns of data in which I have to find a specific value say 149 in column 3...
2. Then I need to modify the cell in column B below the target cell ( containing 149) as = cell value + cell value - previous cell value. now called X.
3. then I need to update the other cells below it
in column B as X +existing value...( column B )
until I find the next 149.
I know the flow chart for this program but cant find help on VB comands to achieve this as macro for excel.
I would realy appreciate help with this.
thanks a buch for the help.. in advance.

--saradhi

Posted by David Hawley on March 29, 2001 4:04 AM

Hi saradhi

I'm not too sure I have got this right, but give this a go.

Sub TryThis()
Dim FoundCell As Range
Dim NumCount As Integer, i As Integer
NumCount = WorksheetFunction.CountIf(Columns(3), "149") - 1
With Columns(3)
Set FoundCell = .Find(What:="149", After:=Range("C1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Offset(1, -1)
FoundCell = FoundCell * 2 + 149

For i = 1 To NumCount
Set FoundCell = .FindNext(After:=FoundCell.Offset(0, 1)).Offset(1, -1)
FoundCell = FoundCell * 2 + 149
Next i
End With
Set FoundCell = Nothing
End Sub

Dave

OzGrid Business Applications