Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula based on another cell value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula based on another cell value
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula based on another cell value
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 -- Rick (MVP - Excel) "Fester" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula based on another cell value
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula based on another cell value
"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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula based on another cell value
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 On Oct 6, 1:21*pm, "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "Fester" wrote in message ... 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- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula based on another cell value
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Cell range based on color of cell to existing formula | Excel Programming | |||
Change cell formula based on input in different cell | Excel Programming | |||
formula for named cell/range based on cell values | Excel Programming | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |