Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Fill Cells With Formula; Conditional To Seperate Column
I'm working on a spreadsheet where certain cells in columns need to be filled
with a formula, but only if cells in the same corresponding row from a different column meet certain criteria. Example: __|L__|M__|N__|O__|P__|Q__|R__|......|W__| 08|___|___|___|____|___|___|___|......|Condition| 09|___|___|___|____|___|___|___|......|Condition| 10|___|___|___|____|___|___|___|......|Condition| 11|___|___|___|____|___|___|___|......|Condition| etc Here's what I'd want to happen. If the condition in cell W8 is met, then fill cell L8 with "Formula X". If it is not met, leave the cell alone (skip and do not change contents). The condition is a text entry of "D1" (will vary) in cell W8, if that's there, fill the cell with "Formula X", if not, don't change a thing. I'd need this to happen from row 08 down until there are no more entries in the W column. Each column would have its own macro - I should be able to understand enough to change a single macro written for column "L" as needed so there's no need to reply with multiple, unique, macros for each column. I located this solution to an old problem, but was unable to find the example spreadsheet the user posted (expired link). Sub CondAutofill() Set rng = Range(Range("B4"), _ Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng If Not IsEmpty(cell) And _ InStr(1, cell.Value, "total", _ vbTextCompare) = 0 Then Range("D3").Copy cell.Offset(0, 2) End If Next End Sub This does accomplish close to what I need; however, I lack the knowledge to modify it properly to meet my needs. Thanks, CVinje |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Fill Cells With Formula; Conditional To Seperate Column
Hi,
Try this. It read form W8 to the last row in column w and populates column L if it finds a match to D1 Sub sonic() Dim MyRange As Range, MyText As String MyText = Range("D1").Value LastRow = Cells(Rows.Count, "W").End(xlUp).Row Set MyRange = Range("W8:W" & LastRow) For Each c In MyRange If c.Value = MyText Then c.Offset(, -11).Value = "Something" End If Next End Sub Mike "CVinje" wrote: I'm working on a spreadsheet where certain cells in columns need to be filled with a formula, but only if cells in the same corresponding row from a different column meet certain criteria. Example: __|L__|M__|N__|O__|P__|Q__|R__|......|W__| 08|___|___|___|____|___|___|___|......|Condition| 09|___|___|___|____|___|___|___|......|Condition| 10|___|___|___|____|___|___|___|......|Condition| 11|___|___|___|____|___|___|___|......|Condition| etc Here's what I'd want to happen. If the condition in cell W8 is met, then fill cell L8 with "Formula X". If it is not met, leave the cell alone (skip and do not change contents). The condition is a text entry of "D1" (will vary) in cell W8, if that's there, fill the cell with "Formula X", if not, don't change a thing. I'd need this to happen from row 08 down until there are no more entries in the W column. Each column would have its own macro - I should be able to understand enough to change a single macro written for column "L" as needed so there's no need to reply with multiple, unique, macros for each column. I located this solution to an old problem, but was unable to find the example spreadsheet the user posted (expired link). Sub CondAutofill() Set rng = Range(Range("B4"), _ Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng If Not IsEmpty(cell) And _ InStr(1, cell.Value, "total", _ vbTextCompare) = 0 Then Range("D3").Copy cell.Offset(0, 2) End If Next End Sub This does accomplish close to what I need; however, I lack the knowledge to modify it properly to meet my needs. Thanks, CVinje |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Fill Cells With Formula; Conditional To Seperate Column
The posted code works - just had to modify the part where it filled the cell
with "value = ..." to handle entering a formula. Thanks very much for your help!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Fill Cells With Formula; Conditional To Seperate Column
Glad I could help and thanks for the feedback
"CVinje" wrote: The posted code works - just had to modify the part where it filled the cell with "value = ..." to handle entering a formula. Thanks very much for your help!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
seperate first 2 lines of column in seperate columns in same row | Excel Discussion (Misc queries) | |||
formula using up 2 10 cells of $ 2 seperate & = $ amt | Excel Worksheet Functions | |||
How do I quickly fill a formula result into a column of cells? | Excel Worksheet Functions | |||
how do I create a formula to seperate name into two cells (last, . | Excel Discussion (Misc queries) |