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

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

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

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

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

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

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