Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search cells in multiple columns of same row
I am trying to write a script that looks for a cell with specified text
in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right form the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales of Water God bless jsd219 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
search cells in multiple columns of same row
I don't know about the populating-a-cell part of your request.
I believe you will need a macro or VBA code for that part, and others here could help with that. But as for finding *where* the put the X, this formula should work as one way: =ADDRESS(MATCH("X",OFFSET(INDIRECT(MATCH("Went up the hill",G1:G3,0)&":"&MATCH("Went up the hill",G1:G3,0)),-1,),0)+1,MATCH("Went up the hill",G1:G3,0)) (Replace "Went up the hill" with the real text that's in Column G. Replace G1:G3 with the actual range.) Question: what if the X in the previous line is in Column F? Then we can't move it one more to the right; the text strings are there. I hope you don't want it to go back to A then. That means more work on the above formula. Actually, I fully expect one of the macro geniuses here to do this all with a macro rather than messing with a formula. But I was curious as to whether I could find where to place your X with a formula. -dman- ================================== In .com, jsd219 spake thusly: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right form the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales of Water |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
search cells in multiple columns of same row
I appreciate it but yes i am hoping to get htis doen with a script not
formula. :-) God bless jsd219 Dallman Ross wrote: I don't know about the populating-a-cell part of your request. I believe you will need a macro or VBA code for that part, and others here could help with that. But as for finding *where* the put the X, this formula should work as one way: =ADDRESS(MATCH("X",OFFSET(INDIRECT(MATCH("Went up the hill",G1:G3,0)&":"&MATCH("Went up the hill",G1:G3,0)),-1,),0)+1,MATCH("Went up the hill",G1:G3,0)) (Replace "Went up the hill" with the real text that's in Column G. Replace G1:G3 with the actual range.) Question: what if the X in the previous line is in Column F? Then we can't move it one more to the right; the text strings are there. I hope you don't want it to go back to A then. That means more work on the above formula. Actually, I fully expect one of the macro geniuses here to do this all with a macro rather than messing with a formula. But I was curious as to whether I could find where to place your X with a formula. -dman- ================================== In .com, jsd219 spake thusly: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right form the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales of Water |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
search cells in multiple columns of same row
Maybe something like:
Option Explicit Sub testme() Dim res As Variant Dim wks As Worksheet Dim WhatToFind As String Dim iCol As Long Set wks = Worksheets("sheet1") WhatToFind = "JACK And JILL" With wks res = Application.Match(WhatToFind, .Range("H:H"), 0) If IsError(res) Then MsgBox "not found" Exit Sub End If For iCol = 1 To 6 'skip 7 not to overwrite column 7 If LCase(.Cells(res, iCol).Value) = LCase("x") Then .Cells(res, iCol + 1).Value = "X" Exit For End If Next iCol End With End Sub jsd219 wrote: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right form the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales of Water God bless jsd219 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
search cells in multiple columns of same row
I see you have another thread with the same question.
Good luck. Dave Peterson wrote: Maybe something like: Option Explicit Sub testme() Dim res As Variant Dim wks As Worksheet Dim WhatToFind As String Dim iCol As Long Set wks = Worksheets("sheet1") WhatToFind = "JACK And JILL" With wks res = Application.Match(WhatToFind, .Range("H:H"), 0) If IsError(res) Then MsgBox "not found" Exit Sub End If For iCol = 1 To 6 'skip 7 not to overwrite column 7 If LCase(.Cells(res, iCol).Value) = LCase("x") Then .Cells(res, iCol + 1).Value = "X" Exit For End If Next iCol End With End Sub jsd219 wrote: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right form the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales of Water God bless jsd219 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
search cells in multiple columns of same row
Yes, this script is a smaller part of a bigger one and i have posted
for various parts of the bigger script and someitmes i use similar if not the same verbage. :-) Unfortunately i can not get your or Tom's script to work properly. i know you two are awesome coders so i am assuming it is on my end. One of the problems is, i need this script to start at whatever row is selected. I am working on another script that will test the cells and select the appropriate ones, once those cells are selected i then need to call this script. so all i need this one to do is starting from the selected position (in this case the specified cell in column "N"), check its row thru columns C - H to see if any of those cells have an "x" in them. if they do then the script needs to place an "x" one column to the right and one row down. God bless jsd219 Dave Peterson wrote: Maybe something like: Option Explicit Sub testme() Dim res As Variant Dim wks As Worksheet Dim WhatToFind As String Dim iCol As Long Set wks = Worksheets("sheet1") WhatToFind = "JACK And JILL" With wks res = Application.Match(WhatToFind, .Range("H:H"), 0) If IsError(res) Then MsgBox "not found" Exit Sub End If For iCol = 1 To 6 'skip 7 not to overwrite column 7 If LCase(.Cells(res, iCol).Value) = LCase("x") Then .Cells(res, iCol + 1).Value = "X" Exit For End If Next iCol End With End Sub jsd219 wrote: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right form the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales of Water God bless jsd219 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
search cells in multiple columns of same row
Tom has responded at your other thread.
jsd219 wrote: Yes, this script is a smaller part of a bigger one and i have posted for various parts of the bigger script and someitmes i use similar if not the same verbage. :-) Unfortunately i can not get your or Tom's script to work properly. i know you two are awesome coders so i am assuming it is on my end. One of the problems is, i need this script to start at whatever row is selected. I am working on another script that will test the cells and select the appropriate ones, once those cells are selected i then need to call this script. so all i need this one to do is starting from the selected position (in this case the specified cell in column "N"), check its row thru columns C - H to see if any of those cells have an "x" in them. if they do then the script needs to place an "x" one column to the right and one row down. God bless jsd219 Dave Peterson wrote: Maybe something like: Option Explicit Sub testme() Dim res As Variant Dim wks As Worksheet Dim WhatToFind As String Dim iCol As Long Set wks = Worksheets("sheet1") WhatToFind = "JACK And JILL" With wks res = Application.Match(WhatToFind, .Range("H:H"), 0) If IsError(res) Then MsgBox "not found" Exit Sub End If For iCol = 1 To 6 'skip 7 not to overwrite column 7 If LCase(.Cells(res, iCol).Value) = LCase("x") Then .Cells(res, iCol + 1).Value = "X" Exit For End If Next iCol End With End Sub jsd219 wrote: I am trying to write a script that looks for a cell with specified text in a specified column, once it finds the cell it needs to check six different columns (a,b,c,d,e,f) one row above and find the column that has an "x", it then needs to place an "x" in its row one column to the right form the previous "x" example: if it finds the cell with "Went up the hill" the script needs to look up one row and check columns (a,b,c,d,e,f) until it finds the "X". In this case it will find the "X" on the row with "JACK AND JILL" in column "a" so it will place an "X" for the row "Went up the hill" (the orginal cell searched for) in column "b" a b c d e f g X JACK AND JILL Went up the hill To fetch 3.5 Pales of Water God bless jsd219 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using functions to compare multiple columns for mismatch of cells | Excel Worksheet Functions | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
how can i ignore blank cells when multiple cells? | Excel Worksheet Functions | |||
PLEASE HELP - Pasting Cells to WrkSheet with Hidden Columns | Excel Worksheet Functions |