I have posted a few times without response, abut am in need of a method using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout how to start it.
Do i need to set a target cell somehow, to start with so i can offset cells from it ?

Can anyone assist me in this please?

Corey.....

Corey, this is out of the VBA Help file:

Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

This is the same thing in a tighter form:

Worksheets(1).Activate
ActiveCell.Offset(3, 3).Activate

You can find more info in the VBA Help file by typing Offset in the search
boxEnter and select "Offset Property".


"Corey" wrote:

I have posted a few times without response, abut am in need of a method using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout how to start it.
Do i need to set a target cell somehow, to start with so i can offset cells from it ?

Can anyone assist me in this please?

Corey....

You can also get some information from "Refering to Cells Relative to Other
Cells", also in VBA Help.

"Corey" wrote:

I have posted a few times without response, abut am in need of a method using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout how to start it.
Do i need to set a target cell somehow, to start with so i can offset cells from it ?

Can anyone assist me in this please?

Corey....

Thank you for taking time to reply to my post JLGKhiz.

Do you know how i can get the activecell to be the selected value from a
combobox ?
My earlier post 2/1/07 9:02am(about 30 posts ago) explains what i need.

If i can get the selected value from the combobox(Cell in sheet that
contains the value) to be the active cell i am on my way.
At least i think anyhow.

Corey....
"JLGWhiz" wrote in message
...
You can also get some information from "Refering to Cells Relative to
Other
Cells", also in VBA Help.

"Corey" wrote:

I have posted a few times without response, abut am in need of a method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout how
to start it.
Do i need to set a target cell somehow, to start with so i can offset
cells from it ?

Can anyone assist me in this please?

Corey....

To add some, you may also set up an offset to a known location (range) and
may refer to that range either by address or by its name if it is a named
range.

Range("A1").Offset(3,4) = 55
would put the value 55 into cell E4 (3 rows down, 4 columns to the right of
A1)

If A1 had been named UpperLeft you could refer to it this way:
Range("UpperLeft").Offset(3,4)=55
same result

If you want/need to reference cells on a different sheet than the currently
active sheet, preceed Range( with the sheet reference, either by name or
array number, name is usually easier to figure out.
Sheets("Sheet1").Range("UpperLeft").Offset(3,4) = 55

Obviously, the values used for row/column offsets can be calculated values
such as you might come up with inside of a loop.

"JLGWhiz" wrote:

Corey, this is out of the VBA Help file:

Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

This is the same thing in a tighter form:

Worksheets(1).Activate
ActiveCell.Offset(3, 3).Activate

You can find more info in the VBA Help file by typing Offset in the search
boxEnter and select "Offset Property".


"Corey" wrote:

I have posted a few times without response, abut am in need of a method using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout how to start it.
Do i need to set a target cell somehow, to start with so i can offset cells from it ?

Can anyone assist me in this please?

Corey....

Thanks for the reply.
Is it possible to offset from a value that was chosen from a combobox in
another sheet?

I know the COLUMN (A) that the combobox value WILL be in but not the ROW?

Corey....

See my ealier post 2/1/2007 9:02am



"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
To add some, you may also set up an offset to a known location (range) and
may refer to that range either by address or by its name if it is a named
range.

Range("A1").Offset(3,4) = 55
would put the value 55 into cell E4 (3 rows down, 4 columns to the right
of
A1)

If A1 had been named UpperLeft you could refer to it this way:
Range("UpperLeft").Offset(3,4)=55
same result

If you want/need to reference cells on a different sheet than the
currently
active sheet, preceed Range( with the sheet reference, either by name or
array number, name is usually easier to figure out.
Sheets("Sheet1").Range("UpperLeft").Offset(3,4) = 55

Obviously, the values used for row/column offsets can be calculated values
such as you might come up with inside of a loop.

"JLGWhiz" wrote:

Corey, this is out of the VBA Help file:

Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

This is the same thing in a tighter form:

Worksheets(1).Activate
ActiveCell.Offset(3, 3).Activate

You can find more info in the VBA Help file by typing Offset in the
search
boxEnter and select "Offset Property".


"Corey" wrote:

I have posted a few times without response, abut am in need of a method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout
how to start it.
Do i need to set a target cell somehow, to start with so i can offset
cells from it ?

Can anyone assist me in this please?

Corey....

Can you provide a link to that post? I cannot seem to find it.

If you used a combo box from the Forms toolbar, and not from the Control
Toolbox, you can assign a cell to store the selection made into, and use that
to figure things out. That combo box has two properties, the list source,
and a linked cell. When you choose from the list, the linked cell then holds
the number of the item in the list you selected. Since you'd know the linked
cell address, then things should be easy from there?

"Corey" wrote:

Thanks for the reply.
Is it possible to offset from a value that was chosen from a combobox in
another sheet?

I know the COLUMN (A) that the combobox value WILL be in but not the ROW?

Corey....

See my ealier post 2/1/2007 9:02am



"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
To add some, you may also set up an offset to a known location (range) and
may refer to that range either by address or by its name if it is a named
range.

Range("A1").Offset(3,4) = 55
would put the value 55 into cell E4 (3 rows down, 4 columns to the right
of
A1)

If A1 had been named UpperLeft you could refer to it this way:
Range("UpperLeft").Offset(3,4)=55
same result

If you want/need to reference cells on a different sheet than the
currently
active sheet, preceed Range( with the sheet reference, either by name or
array number, name is usually easier to figure out.
Sheets("Sheet1").Range("UpperLeft").Offset(3,4) = 55

Obviously, the values used for row/column offsets can be calculated values
such as you might come up with inside of a loop.

"JLGWhiz" wrote:

Corey, this is out of the VBA Help file:

Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

This is the same thing in a tighter form:

Worksheets(1).Activate
ActiveCell.Offset(3, 3).Activate

You can find more info in the VBA Help file by typing Offset in the
search
boxEnter and select "Offset Property".


"Corey" wrote:

I have posted a few times without response, abut am in need of a method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout
how to start it.
Do i need to set a target cell somehow, to start with so i can offset
cells from it ?

Can anyone assist me in this please?

Corey....

I think you are looking for the Find method (check VBA help for details)

For example, the macro below will find the cell with the number 45 in Column
A of Sheet1 and, if a cell w/that value is found, takes the value 4 columns
over (Col E) and puts it in cell A1 of Sheet2. Change the sheet references
as needed and double check the arguments for the Find method to what you may
need (you'll certainly need to change "what:=45" to something like "what:=
Me.combobox1.value" (w/o quotes)). I'm assuming this code is intended to be
put w/the userform that contains your combobox or you'll need some other
means of getting the value of that combobox to use in the search.


Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet1").Range("A:A")
Set rngFound = .Find(what:=45, after:=.Range("A1"), _
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0

If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value
End If

End Sub



"Corey" wrote:

Thank you for taking time to reply to my post JLGKhiz.

Do you know how i can get the activecell to be the selected value from a
combobox ?
My earlier post 2/1/07 9:02am(about 30 posts ago) explains what i need.

If i can get the selected value from the combobox(Cell in sheet that
contains the value) to be the active cell i am on my way.
At least i think anyhow.

Corey....
"JLGWhiz" wrote in message
...
You can also get some information from "Refering to Cells Relative to
Other
Cells", also in VBA Help.

"Corey" wrote:

I have posted a few times without response, abut am in need of a method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout how
to start it.
Do i need to set a target cell somehow, to start with so i can offset
cells from it ?

Can anyone assist me in this please?

Corey....

With the following section of code, i currently have a combobox value placed
in cell O4.

With ActiveWorkbook.workSheets("Inspections")
..Select
Range("O4").Value = ComboBox1.Value
End With

The combobox value is taken from sheet4 and placed in O4 in sheet2 for
viewing when required.

This value from sheet4, is ALWAYS in column A, but can be in any row.

I want to place many other values from sheet4 into sheet2 also, but need to
reference these other values ACCORDING to where the value in the combobox
value is.
IE.

I want to use some thing like, if combobox1.value was in seet4.range("A1")
then :

Combobox1.value = sheet4.target cell

sheet2.range("A5").value = targetcell.offset(0,4).value ' I can then place
the other values from sheet4 into sheet2 by where those values are related
to the location of the combobox value(Target).


To hopefully explain clearer.
EG.

sheet4:

A1 = 500
A5 = ABC
B3 = Yellow
C2 = 1000


When the combobox value is selected(sheet4.column A):

Sheet2:
Target = 500
A5 = target.offset(0,4)
B3 = target.offset(1,2)
C2 = target.offset(2,1)
etc....

All these values that are brought in from sheet4, are NOT in any specific
ROW.
Therefore i need to refernce them by WHERE the combobox value WAS.

Can anyone help me get started on this ?

Corey....














"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Can you provide a link to that post? I cannot seem to find it.

If you used a combo box from the Forms toolbar, and not from the Control
Toolbox, you can assign a cell to store the selection made into, and use
that
to figure things out. That combo box has two properties, the list source,
and a linked cell. When you choose from the list, the linked cell then
holds
the number of the item in the list you selected. Since you'd know the
linked
cell address, then things should be easy from there?

"Corey" wrote:

Thanks for the reply.
Is it possible to offset from a value that was chosen from a combobox in
another sheet?

I know the COLUMN (A) that the combobox value WILL be in but not the ROW?

Corey....

See my ealier post 2/1/2007 9:02am



"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
To add some, you may also set up an offset to a known location (range)
and
may refer to that range either by address or by its name if it is a
named
range.

Range("A1").Offset(3,4) = 55
would put the value 55 into cell E4 (3 rows down, 4 columns to the
right
of
A1)

If A1 had been named UpperLeft you could refer to it this way:
Range("UpperLeft").Offset(3,4)=55
same result

If you want/need to reference cells on a different sheet than the
currently
active sheet, preceed Range( with the sheet reference, either by name
or
array number, name is usually easier to figure out.
Sheets("Sheet1").Range("UpperLeft").Offset(3,4) = 55

Obviously, the values used for row/column offsets can be calculated
values
such as you might come up with inside of a loop.

"JLGWhiz" wrote:

Corey, this is out of the VBA Help file:

Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

This is the same thing in a tighter form:

Worksheets(1).Activate
ActiveCell.Offset(3, 3).Activate

You can find more info in the VBA Help file by typing Offset in the
search
boxEnter and select "Offset Property".


"Corey" wrote:

I have posted a few times without response, abut am in need of a
method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot
workout
how to start it.
Do i need to set a target cell somehow, to start with so i can
offset
cells from it ?

Can anyone assist me in this please?

Corey....

Thanks for the reply JMB,
I think you are on the correct reasoning as what i need.
If i use the code for FIND you supplied and modify to something like:

Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet4").Range("A:A")
Set rngFound = .Find(what:=Me.combobox1.value, after:=.Range("A1"), _ '
Combobox1 value that is on userform3....
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0
' Can i Replace below lines with what is below it.....
<=============================
If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value ' <========== What would this bit do though
?. I need more than the combobox value.
End If
' Replacement code to bring in other values from Sheet4, from FIND Cell. '
<======================== New lines to relace above section.
Sheet3.range("E2").value = Sheet4.rngFound.Offset(2, -1).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C1").value placed in
sheet3.range("E2") ?
Sheet3.range("C3").value = Sheet4.rngFound.Offset(4, 5).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E7").value placed in
sheet3.range("C3") ?
Sheet3.range("F5").value = Sheet4.rngFound.Offset(2, 1).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C3").value placed in
sheet3.Range("F5") ?
Sheet3.range("H1").value = Sheet4.rngFound.Offset(4, 2).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E4").value placed in
sheet3.Range("H1") ?
etc....

End Sub

Will this work OK ?


Corey....

"JMB" wrote in message
...
I think you are looking for the Find method (check VBA help for details)

For example, the macro below will find the cell with the number 45 in
Column
A of Sheet1 and, if a cell w/that value is found, takes the value 4
columns
over (Col E) and puts it in cell A1 of Sheet2. Change the sheet
references
as needed and double check the arguments for the Find method to what you
may
need (you'll certainly need to change "what:=45" to something like "what:=
Me.combobox1.value" (w/o quotes)). I'm assuming this code is intended to
be
put w/the userform that contains your combobox or you'll need some other
means of getting the value of that combobox to use in the search.


Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet1").Range("A:A")
Set rngFound = .Find(what:=45, after:=.Range("A1"), _
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0

If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value
End If

End Sub



"Corey" wrote:

Thank you for taking time to reply to my post JLGKhiz.

Do you know how i can get the activecell to be the selected value from a
combobox ?
My earlier post 2/1/07 9:02am(about 30 posts ago) explains what i need.

If i can get the selected value from the combobox(Cell in sheet that
contains the value) to be the active cell i am on my way.
At least i think anyhow.

Corey....
"JLGWhiz" wrote in message
...
You can also get some information from "Refering to Cells Relative to
Other
Cells", also in VBA Help.

"Corey" wrote:

I have posted a few times without response, abut am in need of a
method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout
how
to start it.
Do i need to set a target cell somehow, to start with so i can offset
cells from it ?

Can anyone assist me in this please?

Corey....

rngFound should not need to be qualified w/the worksheet. Also, offset is
Offset(row, column) if you don't use named arguments.

Using your first example, I believe it s/b:
Sheet3.range("E2").value = rngFound.Offset(-1, 2).value

' Would this line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C1").value placed in
sheet3.range("E2") ?


More on referencing cells within ranges
http://www.cpearson.com/excel/cells.htm


"Corey" wrote:

Thanks for the reply JMB,
I think you are on the correct reasoning as what i need.
If i use the code for FIND you supplied and modify to something like:

Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet4").Range("A:A")
Set rngFound = .Find(what:=Me.combobox1.value, after:=.Range("A1"), _ '
Combobox1 value that is on userform3....
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0
' Can i Replace below lines with what is below it.....
<=============================
If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value ' <========== What would this bit do though
?. I need more than the combobox value.
End If
' Replacement code to bring in other values from Sheet4, from FIND Cell. '
<======================== New lines to relace above section.
Sheet3.range("E2").value = Sheet4.rngFound.Offset(2, -1).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C1").value placed in
sheet3.range("E2") ?
Sheet3.range("C3").value = Sheet4.rngFound.Offset(4, 5).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E7").value placed in
sheet3.range("C3") ?
Sheet3.range("F5").value = Sheet4.rngFound.Offset(2, 1).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C3").value placed in
sheet3.Range("F5") ?
Sheet3.range("H1").value = Sheet4.rngFound.Offset(4, 2).value ' Would this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E4").value placed in
sheet3.Range("H1") ?
etc....

End Sub

Will this work OK ?


Corey....

"JMB" wrote in message
...
I think you are looking for the Find method (check VBA help for details)

For example, the macro below will find the cell with the number 45 in
Column
A of Sheet1 and, if a cell w/that value is found, takes the value 4
columns
over (Col E) and puts it in cell A1 of Sheet2. Change the sheet
references
as needed and double check the arguments for the Find method to what you
may
need (you'll certainly need to change "what:=45" to something like "what:=
Me.combobox1.value" (w/o quotes)). I'm assuming this code is intended to
be
put w/the userform that contains your combobox or you'll need some other
means of getting the value of that combobox to use in the search.


Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet1").Range("A:A")
Set rngFound = .Find(what:=45, after:=.Range("A1"), _
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0

If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value
End If

End Sub



"Corey" wrote:

Thank you for taking time to reply to my post JLGKhiz.

Do you know how i can get the activecell to be the selected value from a
combobox ?
My earlier post 2/1/07 9:02am(about 30 posts ago) explains what i need.

If i can get the selected value from the combobox(Cell in sheet that
contains the value) to be the active cell i am on my way.
At least i think anyhow.

Corey....
"JLGWhiz" wrote in message
...
You can also get some information from "Refering to Cells Relative to
Other
Cells", also in VBA Help.

"Corey" wrote:

I have posted a few times without response, abut am in need of a
method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot workout
how
to start it.
Do i need to set a target cell somehow, to start with so i can offset
cells from it ?

Can anyone assist me in this please?

Corey....

Thanks will give it a go tomorrow morning.
I did not realise that the Row came BEFORE the column in this case.
I always assumed A2 etc...

Corey....
"JMB" wrote in message
...
rngFound should not need to be qualified w/the worksheet. Also, offset is
Offset(row, column) if you don't use named arguments.

Using your first example, I believe it s/b:
Sheet3.range("E2").value = rngFound.Offset(-1, 2).value

' Would this line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C1").value placed in
sheet3.range("E2") ?


More on referencing cells within ranges
http://www.cpearson.com/excel/cells.htm


"Corey" wrote:

Thanks for the reply JMB,
I think you are on the correct reasoning as what i need.
If i use the code for FIND you supplied and modify to something like:

Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet4").Range("A:A")
Set rngFound = .Find(what:=Me.combobox1.value, after:=.Range("A1"), _ '
Combobox1 value that is on userform3....
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0
' Can i Replace below lines with what is below it.....
<=============================
If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value ' <========== What would this bit do
though
?. I need more than the combobox value.
End If
' Replacement code to bring in other values from Sheet4, from FIND Cell.
'
<======================== New lines to relace above section.
Sheet3.range("E2").value = Sheet4.rngFound.Offset(2, -1).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C1").value placed in
sheet3.range("E2") ?
Sheet3.range("C3").value = Sheet4.rngFound.Offset(4, 5).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E7").value placed in
sheet3.range("C3") ?
Sheet3.range("F5").value = Sheet4.rngFound.Offset(2, 1).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C3").value placed in
sheet3.Range("F5") ?
Sheet3.range("H1").value = Sheet4.rngFound.Offset(4, 2).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E4").value placed in
sheet3.Range("H1") ?
etc....

End Sub

Will this work OK ?


Corey....

"JMB" wrote in message
...
I think you are looking for the Find method (check VBA help for details)

For example, the macro below will find the cell with the number 45 in
Column
A of Sheet1 and, if a cell w/that value is found, takes the value 4
columns
over (Col E) and puts it in cell A1 of Sheet2. Change the sheet
references
as needed and double check the arguments for the Find method to what
you
may
need (you'll certainly need to change "what:=45" to something like
"what:=
Me.combobox1.value" (w/o quotes)). I'm assuming this code is intended
to
be
put w/the userform that contains your combobox or you'll need some
other
means of getting the value of that combobox to use in the search.


Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet1").Range("A:A")
Set rngFound = .Find(what:=45, after:=.Range("A1"), _
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0

If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value
End If

End Sub



"Corey" wrote:

Thank you for taking time to reply to my post JLGKhiz.

Do you know how i can get the activecell to be the selected value from
a
combobox ?
My earlier post 2/1/07 9:02am(about 30 posts ago) explains what i
need.

If i can get the selected value from the combobox(Cell in sheet that
contains the value) to be the active cell i am on my way.
At least i think anyhow.

Corey....
"JLGWhiz" wrote in message
...
You can also get some information from "Refering to Cells Relative
to
Other
Cells", also in VBA Help.

"Corey" wrote:

I have posted a few times without response, abut am in need of a
method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot
workout
how
to start it.
Do i need to set a target cell somehow, to start with so i can
offset
cells from it ?

Can anyone assist me in this please?

Corey....

Think i am on my way.

I had to modify this:
Set rngFound = .Find(what:=45, after:=.Range("A1"), LookIn:=xlValues,
lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext,
MatchCase:=False, matchbyte:=False)
to Set rngFound = .Find(what:=45, after:=.Range("A1"), LookIn:=xlValues,
lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext,
MatchCase:=False, matchbyte:=False)
as i had a # symbol infront of the value, and was therefore NOT finding
anything to input.

Corey....


"Corey" wrote in message
...
Thanks will give it a go tomorrow morning.
I did not realise that the Row came BEFORE the column in this case.
I always assumed A2 etc...

Corey....
"JMB" wrote in message
...
rngFound should not need to be qualified w/the worksheet. Also, offset
is
Offset(row, column) if you don't use named arguments.

Using your first example, I believe it s/b:
Sheet3.range("E2").value = rngFound.Offset(-1, 2).value

' Would this line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C1").value placed in
sheet3.range("E2") ?


More on referencing cells within ranges
http://www.cpearson.com/excel/cells.htm


"Corey" wrote:

Thanks for the reply JMB,
I think you are on the correct reasoning as what i need.
If i use the code for FIND you supplied and modify to something like:

Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet4").Range("A:A")
Set rngFound = .Find(what:=Me.combobox1.value, after:=.Range("A1"), _
'
Combobox1 value that is on userform3....
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0
' Can i Replace below lines with what is below it.....
<=============================
If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value ' <========== What would this bit do
though
?. I need more than the combobox value.
End If
' Replacement code to bring in other values from Sheet4, from FIND
Cell. '
<======================== New lines to relace above section.
Sheet3.range("E2").value = Sheet4.rngFound.Offset(2, -1).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C1").value placed in
sheet3.range("E2") ?
Sheet3.range("C3").value = Sheet4.rngFound.Offset(4, 5).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E7").value placed in
sheet3.range("C3") ?
Sheet3.range("F5").value = Sheet4.rngFound.Offset(2, 1).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("C3").value placed in
sheet3.Range("F5") ?
Sheet3.range("H1").value = Sheet4.rngFound.Offset(4, 2).value ' Would
this
line work OK, if FOUND.Cell was in sheet4.range("A2") and _
the required value was in sheet4.range("E4").value placed in
sheet3.Range("H1") ?
etc....

End Sub

Will this work OK ?


Corey....

"JMB" wrote in message
...
I think you are looking for the Find method (check VBA help for
details)

For example, the macro below will find the cell with the number 45 in
Column
A of Sheet1 and, if a cell w/that value is found, takes the value 4
columns
over (Col E) and puts it in cell A1 of Sheet2. Change the sheet
references
as needed and double check the arguments for the Find method to what
you
may
need (you'll certainly need to change "what:=45" to something like
"what:=
Me.combobox1.value" (w/o quotes)). I'm assuming this code is intended
to
be
put w/the userform that contains your combobox or you'll need some
other
means of getting the value of that combobox to use in the search.


Sub Test()
Dim rngFound As Range

On Error Resume Next
With Worksheets("Sheet1").Range("A:A")
Set rngFound = .Find(what:=45, after:=.Range("A1"), _
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
End With
On Error GoTo 0

If Not rngFound Is Nothing Then
Worksheets("Sheet2").Range("A1").Value = _
rngFound.Offset(0, 4).Value
End If

End Sub



"Corey" wrote:

Thank you for taking time to reply to my post JLGKhiz.

Do you know how i can get the activecell to be the selected value
from a
combobox ?
My earlier post 2/1/07 9:02am(about 30 posts ago) explains what i
need.

If i can get the selected value from the combobox(Cell in sheet that
contains the value) to be the active cell i am on my way.
At least i think anyhow.

Corey....
"JLGWhiz" wrote in message
...
You can also get some information from "Refering to Cells Relative
to
Other
Cells", also in VBA Help.

"Corey" wrote:

I have posted a few times without response, abut am in need of a
method
using the offset(x, x) code.
I have never delt with this type of code before and so cannot
workout
how
to start it.
Do i need to set a target cell somehow, to start with so i can
offset
cells from it ?

Can anyone assist me in this please?

Corey....