Maybe something like:

dim wks as worksheet
dim LastRow as long
dim myCell as range
dim myRng as range

set wks = activesheet
with wks
lastRow = .cells(.rows.count,"B").end(xlup).row
set myrng = .range("b2:B" & lastrow)
for each mycell in myrng.cells
select case lcase(mycell.value)
case is = lcase("x")
mycell.offset(0,4).formular1c1 = "=rc[-1]/3"
case is = lcase("Y")
mycell.offset(0,4).formular1c1 = "=(rc[-1]+rc[2])/3"
case else
'do nothing
end select
next mycell
end with

===========
I used .formular1c1. Then I could use this kind of formula:
=(rc[-1]+rc[2])/3

r means the same row as the cell with the formula.
c[-1] means the column to the left of the cell with the formula
c[+1] is the cell to the right of the cell with the formula

You may want to consider using a formula that includes the test:

=if(b2="y",someformula,if(b2="x",someotherformula, evenanotherformula))

Then the formulas will react to any changes in column B.






Fester wrote:

I want to run a loop that looks at a value in one cell, and then
enters one formula for one value, and another for a different value.

So if Cell B2="X" then
range("E2").value = "formula"
elseif Cell B2 = "Y" then
range("E2").value = "other formula

this would loop through all cells until it found an empty cell.

Any help is appreciated.

Brendon

--

Dave Peterson

OK, here's what I came up with:

Range("F2:F" & RNGEND).Select
Dim LVLRNG As Range
For Each LVLRNG In Selection.Cells
If ActiveCell.Offset(0, -3).Value = "A" Then
LVLRNG.Value = "Appt"
ElseIf ActiveCell.Offset(0, -3).Value = "W" Then
LVLRNG.Value = "Walk-In"
End If
Next LVLRNG

However, when it loops through, it makes all of them "APPT" even
though there are "W"'s in the column that's three rows over.

FYI, I'm just making sure it works, once it does, than the
lvlrng.value will equal the formula that I want.

Brendon



Fester wrote:

I want to run a loop that looks at a value in one cell, and then
enters one formula for one value, and another for a different value.

So if Cell B2="X" then
* range("E2").value = "formula"
elseif Cell B2 = "Y" then
* range("E2").value = "other formula

this would loop through all cells until it found an empty cell.

Any help is appreciated.

Brendon

--

Dave Peterson

First of, you don't have to (and probably shouldn't) select the range before
working with it. Second, the ActiveCell does not change just because you
iterate the Selection... use LVLRNG instead of ActiveCell... that's what is
changing in each loop. Try your code this way (where I assume you have
Dim'med and assigned a value to RNGEND prior to running this code)...

Dim LVLRNG As Range
For Each LVLRNG In Range("F2:F" & RNGEND)
If LVLRNG.Offset(0, -3).Value = "A" Then
LVLRNG.Value = "Appt"
ElseIf LVLRNG.Offset(0, -3).Value = "W" Then
LVLRNG.Value = "Walk-In"
End If
Next LVLRNG

--
Rick (MVP - Excel)


"Fester" wrote in message
...
OK, here's what I came up with:

Range("F2:F" & RNGEND).Select
Dim LVLRNG As Range
For Each LVLRNG In Selection.Cells
If ActiveCell.Offset(0, -3).Value = "A" Then
LVLRNG.Value = "Appt"
ElseIf ActiveCell.Offset(0, -3).Value = "W" Then
LVLRNG.Value = "Walk-In"
End If
Next LVLRNG

However, when it loops through, it makes all of them "APPT" even
though there are "W"'s in the column that's three rows over.

FYI, I'm just making sure it works, once it does, than the
lvlrng.value will equal the formula that I want.

Brendon



Fester wrote:

I want to run a loop that looks at a value in one cell, and then
enters one formula for one value, and another for a different value.

So if Cell B2="X" then
range("E2").value = "formula"
elseif Cell B2 = "Y" then
range("E2").value = "other formula

this would loop through all cells until it found an empty cell.

Any help is appreciated.

Brendon

--

Dave Peterson