Formula based on another cell value

  • Thread starter Thread starter Fester
  • Start date Start date
F

Fester

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
 
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.
 
Something like this should work...

Sub Marine()
Dim R As Range
For Each R In Range(Range("B2"), Range("B2").End(xlDown))
If R.Value = "X" Then
R.Offset(, 3).Formula = "<<first formula>>"
ElseIf R.Value = "Y" Then
R.Offset(, 3).Formula = "<<second formula>>"
End If
Next
End Sub
 
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 said:
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
Click to expand...



Sub QuickSample()


Dim row


'==== version 1 ==========

For row = 1 To Cells.SpecialCells(xlCellTypeLastCell).row
Cells(row, 2).Select
If Cells(row, 2) = "" Then
row = 9999
col = 9999
ElseIf UCase$(Cells(row, 2)) = "X" Then
Cells(row, 5) = "formula"
ElseIf UCase$(Cells(row, 2)) = "Y" Then
Cells(row, 5) = "other formula"
End If
Next row


'==== version 2 ==========


For row = 1 To Cells.SpecialCells(xlCellTypeLastCell).row
If Cells(row, 2) = "" Then
row = 9999
col = 9999
ElseIf UCase$(Cells(row, 2)) = "X" Then
Cells(row, 5) = "=C" + Trim$(Str$(row)) + "*2 "
ElseIf UCase$(Cells(row, 2)) = "Y" Then
Cells(row, 5) = "=sum(C4:C" + Trim$(Str$(row)) + ")"
End If
Next row



End Sub
 
OK, Thanks Rick and Dave, Instead of looking at Column F, I looked in
Column C and than changed column F accordingly, worked like a charm.

Thank you both very much.

Brendon
 
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)


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
 
Back
Top