Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
I am using a script to search out cells with specified text within a
specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
Try this
Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
it doesn't seem to be working. here is what it looks like. maybe i
typed something wrong. cell.Range("B" & cell.Row).Value = 0 God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
ooops, i got it. i should not have put the cell at the beginning. thank
you. ok now that i have that i need to take it up a notch. the cells look like this: Families with dogs Chapter 1 the script i am using finds the cells based on an input box. in this case i use "Chapter" to find the cells. i need to copy the chapter number into column "A". the chapter number will range form single digit to double digit. Any ideas? God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
Sub TST()
Dim cell As Range Set cell = ActiveCell Range("B" & cell.Row).Value = 0 End Sub I wrote this code to test it it works fine. Start the statement at Range and drop the "cell." preceding it. jsd219 wrote: it doesn't seem to be working. here is what it looks like. maybe i typed something wrong. cell.Range("B" & cell.Row).Value = 0 God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
If the cell value is "Families with dogs Chapter 1" and the chapter
number is always the last thing in the text, you could try something like this. I will use interim variables to make the method easier to follow. dim c as long dim d as long dim f as long c = instr(1, cell.value, "Chapter") ' This returns the number of the first occurence of "Chapter" in the cell value starting at ' the first letter in the cells text. d = instr(c, cell.value, " ") ' This returns the number of the first space after "Chapter" in the cells text. f = len(cell.value) - d ' This takes the total number of characters and subtracts the number of the last space ' f should always be one or two in your example. Range("A" & cell.row).value = strings.right(cell.value, 2) jsd219 wrote: ooops, i got it. i should not have put the cell at the beginning. thank you. ok now that i have that i need to take it up a notch. the cells look like this: Families with dogs Chapter 1 the script i am using finds the cells based on an input box. in this case i use "Chapter" to find the cells. i need to copy the chapter number into column "A". the chapter number will range form single digit to double digit. Any ideas? God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
It's working great, thanks
God bless jsd219 bobbo wrote: If the cell value is "Families with dogs Chapter 1" and the chapter number is always the last thing in the text, you could try something like this. I will use interim variables to make the method easier to follow. dim c as long dim d as long dim f as long c = instr(1, cell.value, "Chapter") ' This returns the number of the first occurence of "Chapter" in the cell value starting at ' the first letter in the cells text. d = instr(c, cell.value, " ") ' This returns the number of the first space after "Chapter" in the cells text. f = len(cell.value) - d ' This takes the total number of characters and subtracts the number of the last space ' f should always be one or two in your example. Range("A" & cell.row).value = strings.right(cell.value, 2) jsd219 wrote: ooops, i got it. i should not have put the cell at the beginning. thank you. ok now that i have that i need to take it up a notch. the cells look like this: Families with dogs Chapter 1 the script i am using finds the cells based on an input box. in this case i use "Chapter" to find the cells. i need to copy the chapter number into column "A". the chapter number will range form single digit to double digit. Any ideas? God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
Instead of looping through all the cells in the range you can just
seek out the cells that contain myword. I assume that rng is the total range. I will use the find method dim rng as range dim fnd as range dim fadd as string dim myword as string dim myword2 as string dim myword3 as string myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) set fnd = rng.find( Myword, Lookin:=xlvalues, Lookat:= xlpart) fadd = fnd.address Do if instr(1, fnd.offset(1,0).value, myword2) 0 then fnd.entirerow.interior.color =RGB(204, 255, 204) else if instr(1, fnd.offset(1,0).value, myword3) 0 then fnd.entirerow.interior.color = RGB(255, 255, 153) end if end if set fnd = rng.findnext(fnd) loop while not fnd is nothing and fadd < fnd.address jsd219 wrote: Can you help me with this one also? below you will see part of the script. When the script finds the cells with "House" in the contents it should then check the cell directly below it. If the cell below contains "myword2" the row with "myword" should be colored RGB(204, 255, 204) if the cell below has "myword3" then the row with "myword" should be colored RGB(255, 255, 153) myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) For Each cell In rng start_str = InStr(1, cell.Value, myword, vbTextCompare) If start_str Then cell.EntireRow.Interior.Color = RGB(204, 255, 204) Range("B" & cell.Row).Value = 0 cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If start_str2 = InStr(1, cell.Value, myword2, vbTextCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 End If If start_str3 Then cell.EntireRow.Interior.Color = RGB(255, 153, 0) Range("B" & cell.Row).Value = 1 End If God bless jsd219 bobbo wrote: If the cell value is "Families with dogs Chapter 1" and the chapter number is always the last thing in the text, you could try something like this. I will use interim variables to make the method easier to follow. dim c as long dim d as long dim f as long c = instr(1, cell.value, "Chapter") ' This returns the number of the first occurence of "Chapter" in the cell value starting at ' the first letter in the cells text. d = instr(c, cell.value, " ") ' This returns the number of the first space after "Chapter" in the cells text. f = len(cell.value) - d ' This takes the total number of characters and subtracts the number of the last space ' f should always be one or two in your example. Range("A" & cell.row).value = strings.right(cell.value, 2) jsd219 wrote: ooops, i got it. i should not have put the cell at the beginning. thank you. ok now that i have that i need to take it up a notch. the cells look like this: Families with dogs Chapter 1 the script i am using finds the cells based on an input box. in this case i use "Chapter" to find the cells. i need to copy the chapter number into column "A". the chapter number will range form single digit to double digit. Any ideas? God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
You do not have to loop through all the cells in the range you can just
use the find method. Here is an example Dim rng As Range Dim fnd As Range Dim fadd As String Dim myword As String Dim myword2 As String Dim myword3 As String myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) Set fnd = rng.Find(myword, LookIn:=xlValues, Lookat:=xlPart) fadd = fnd.Address Do If InStr(1, fnd.Offset(1, 0).Value, myword2) 0 Then fnd.EntireRow.Interior.Color = RGB(204, 255, 204) Else If InStr(1, fnd.Offset(1, 0).Value, myword3) 0 Then fnd.EntireRow.Interior.Color = RGB(255, 255, 153) End If End If Set fnd = rng.FindNext(fnd) Loop While Not fnd Is Nothing And fadd < fnd.Address jsd219 wrote: Can you help me with this one also? below you will see part of the script. When the script finds the cells with "House" in the contents it should then check the cell directly below it. If the cell below contains "myword2" the row with "myword" should be colored RGB(204, 255, 204) if the cell below has "myword3" then the row with "myword" should be colored RGB(255, 255, 153) myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) For Each cell In rng start_str = InStr(1, cell.Value, myword, vbTextCompare) If start_str Then cell.EntireRow.Interior.Color = RGB(204, 255, 204) Range("B" & cell.Row).Value = 0 cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If start_str2 = InStr(1, cell.Value, myword2, vbTextCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 End If If start_str3 Then cell.EntireRow.Interior.Color = RGB(255, 153, 0) Range("B" & cell.Row).Value = 1 End If God bless jsd219 bobbo wrote: If the cell value is "Families with dogs Chapter 1" and the chapter number is always the last thing in the text, you could try something like this. I will use interim variables to make the method easier to follow. dim c as long dim d as long dim f as long c = instr(1, cell.value, "Chapter") ' This returns the number of the first occurence of "Chapter" in the cell value starting at ' the first letter in the cells text. d = instr(c, cell.value, " ") ' This returns the number of the first space after "Chapter" in the cells text. f = len(cell.value) - d ' This takes the total number of characters and subtracts the number of the last space ' f should always be one or two in your example. Range("A" & cell.row).value = strings.right(cell.value, 2) jsd219 wrote: ooops, i got it. i should not have put the cell at the beginning. thank you. ok now that i have that i need to take it up a notch. the cells look like this: Families with dogs Chapter 1 the script i am using finds the cells based on an input box. in this case i use "Chapter" to find the cells. i need to copy the chapter number into column "A". the chapter number will range form single digit to double digit. Any ideas? God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
You do not have to loop through every cell in the range. You can use
the find method to find only those cells that contain myword. Dim rng As Range Dim fnd As Range Dim fadd As String Dim myword As String Dim myword2 As String Dim myword3 As String myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) Set fnd = rng.Find(myword, LookIn:=xlValues, Lookat:=xlPart) fadd = fnd.Address Do If InStr(1, fnd.Offset(1, 0).Value, myword2) 0 Then fnd.EntireRow.Interior.Color = RGB(204, 255, 204) Else If InStr(1, fnd.Offset(1, 0).Value, myword3) 0 Then fnd.EntireRow.Interior.Color = RGB(255, 255, 153) End If End If Set fnd = rng.FindNext(fnd) Loop While Not fnd Is Nothing And fadd < fnd.Address jsd219 wrote: Can you help me with this one also? below you will see part of the script. When the script finds the cells with "House" in the contents it should then check the cell directly below it. If the cell below contains "myword2" the row with "myword" should be colored RGB(204, 255, 204) if the cell below has "myword3" then the row with "myword" should be colored RGB(255, 255, 153) myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) For Each cell In rng start_str = InStr(1, cell.Value, myword, vbTextCompare) If start_str Then cell.EntireRow.Interior.Color = RGB(204, 255, 204) Range("B" & cell.Row).Value = 0 cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If start_str2 = InStr(1, cell.Value, myword2, vbTextCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 End If If start_str3 Then cell.EntireRow.Interior.Color = RGB(255, 153, 0) Range("B" & cell.Row).Value = 1 End If God bless jsd219 bobbo wrote: If the cell value is "Families with dogs Chapter 1" and the chapter number is always the last thing in the text, you could try something like this. I will use interim variables to make the method easier to follow. dim c as long dim d as long dim f as long c = instr(1, cell.value, "Chapter") ' This returns the number of the first occurence of "Chapter" in the cell value starting at ' the first letter in the cells text. d = instr(c, cell.value, " ") ' This returns the number of the first space after "Chapter" in the cells text. f = len(cell.value) - d ' This takes the total number of characters and subtracts the number of the last space ' f should always be one or two in your example. Range("A" & cell.row).value = strings.right(cell.value, 2) jsd219 wrote: ooops, i got it. i should not have put the cell at the beginning. thank you. ok now that i have that i need to take it up a notch. the cells look like this: Families with dogs Chapter 1 the script i am using finds the cells based on an input box. in this case i use "Chapter" to find the cells. i need to copy the chapter number into column "A". the chapter number will range form single digit to double digit. Any ideas? God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
specify a specific column to input text based on another cell's content
There is a flaw unfortunately. it does not use only cells with myword
and then compare it to the cell below for either myword2 or myword3 instead it finds any cell with myword2 or myword3 colors the cell above God bless jsd219 bobbo wrote: You do not have to loop through every cell in the range. You can use the find method to find only those cells that contain myword. Dim rng As Range Dim fnd As Range Dim fadd As String Dim myword As String Dim myword2 As String Dim myword3 As String myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) Set fnd = rng.Find(myword, LookIn:=xlValues, Lookat:=xlPart) fadd = fnd.Address Do If InStr(1, fnd.Offset(1, 0).Value, myword2) 0 Then fnd.EntireRow.Interior.Color = RGB(204, 255, 204) Else If InStr(1, fnd.Offset(1, 0).Value, myword3) 0 Then fnd.EntireRow.Interior.Color = RGB(255, 255, 153) End If End If Set fnd = rng.FindNext(fnd) Loop While Not fnd Is Nothing And fadd < fnd.Address jsd219 wrote: Can you help me with this one also? below you will see part of the script. When the script finds the cells with "House" in the contents it should then check the cell directly below it. If the cell below contains "myword2" the row with "myword" should be colored RGB(204, 255, 204) if the cell below has "myword3" then the row with "myword" should be colored RGB(255, 255, 153) myword = (InputBox(Prompt:="Enter myword", Default:="House")) myword2 = (InputBox(Prompt:="Enter myword2", Default:="Day")) myword3 = (InputBox(Prompt:="Enter myword2", Default:="Month")) For Each cell In rng start_str = InStr(1, cell.Value, myword, vbTextCompare) If start_str Then cell.EntireRow.Interior.Color = RGB(204, 255, 204) Range("B" & cell.Row).Value = 0 cell.Offset(0, 1).Value = Trim(Left(cell.Value, start_str - 1)) cell.Value = Trim(Right(cell.Value, Len(cell.Value) - start_str + 1)) End If start_str2 = InStr(1, cell.Value, myword2, vbTextCompare) If start_str2 Then cell.EntireRow.Interior.Color = RGB(255, 204, 0) Range("B" & cell.Row).Value = 1 End If If start_str3 Then cell.EntireRow.Interior.Color = RGB(255, 153, 0) Range("B" & cell.Row).Value = 1 End If God bless jsd219 bobbo wrote: If the cell value is "Families with dogs Chapter 1" and the chapter number is always the last thing in the text, you could try something like this. I will use interim variables to make the method easier to follow. dim c as long dim d as long dim f as long c = instr(1, cell.value, "Chapter") ' This returns the number of the first occurence of "Chapter" in the cell value starting at ' the first letter in the cells text. d = instr(c, cell.value, " ") ' This returns the number of the first space after "Chapter" in the cells text. f = len(cell.value) - d ' This takes the total number of characters and subtracts the number of the last space ' f should always be one or two in your example. Range("A" & cell.row).value = strings.right(cell.value, 2) jsd219 wrote: ooops, i got it. i should not have put the cell at the beginning. thank you. ok now that i have that i need to take it up a notch. the cells look like this: Families with dogs Chapter 1 the script i am using finds the cells based on an input box. in this case i use "Chapter" to find the cells. i need to copy the chapter number into column "A". the chapter number will range form single digit to double digit. Any ideas? God bless jsd219 bobbo wrote: Try this Range("B" & cell.row).value = 0 jsd219 wrote: I am using a script to search out cells with specified text within a specific column, once it finds these cells it make several changes, one action is a number needs to be placed in another cell of another column. The cell with the specified content is in column "N" and i need to place a "0" in column "B". i am currently using the line below to do the task: i would like to not rely on an offset and have it find column "B" can anyone help me with this please. cell.Offset(0, -12).Value = "0" God bless jsd219 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro help: moving specific cells based upon content | Excel Worksheet Functions | |||
how to find and replace content of a specific cell based on its ad | Excel Discussion (Misc queries) | |||
extract data from a range based on other cell's input | Excel Discussion (Misc queries) | |||
VLOOKUP based on PART of another cell's text | Excel Discussion (Misc queries) | |||
Automatically filling in cells based on another cell's content | Excel Worksheet Functions |