Offset 
Author Message
 Offset

Quote:
> Hello and Help!!!

> I have an excel form that has a range from B14 thru B27 that we use to
enter
> a part number and based on this part number I would like to retrieve the
> description for it and place it 2 cells to the right.  They way this is
done
> is when a part number has been entered into any part of the range I would
> select that part number entered and then click a command button that runs
> the code below that retrieved the description from my Iseries.  The only
> problem is that it places it 1 cell to the right and I would like to place
> it 2 cells to the right and have not been successful.

> I am using SEQUEL as a provider since in the sequel program I have created
a
> view has this information and sequel pulls it from the Iseries.  I don't
> know if this is the problem!!

> The information in my Iseries is in the following path:
> QS36F/PARTDESC..........and in the PARTDESC....I have
> PARTNO.....that holds the part number
> FL002......that holds the description for that part number

> Public Sub GetPartNumbers()

>     Dim myConn As ADODB.Connection
>     Dim myRS As ADODB.Recordset
>     Set myConn = New ADODB.Connection
>     myConn.ConnectionString = "Provider=SEQUEL ViewPoint;"
>     myConn.Open
>     Set myRS = New ADODB.Recordset

> Dim R As Range
> Set R = Selection(1) 'cell as range that was clicked on

> 'remove or change to whatever:
> If Intersect(R, Range("B14:B24")) Is Nothing Then
>     MsgBox "You are not in Range(""B14:B24"")"
>     Exit Sub
> End If

> selVal = Val(R.Value) 'returnes the number in cell associated
> If selVal = 0 Then
>     MsgBox "Select a Part Number then click on Get Part Number cmd button"
>     Exit Sub
> End If

> myRS.Open "USACCT/DDVDPARTNO /setvar((&PARTNO " & selVal & "))", myConn

> 'This brings in the results but it places it in the cell to the
> 'right and I need the results 2 cells to the right
> If myRS.RecordCount > 0 Then
>     R.CopyFromRecordset MyRS

> 'These are all the things I have tried and they don't
> 'seem to work
> '   Copy myRS(1, 2)
> '   Copy myRS.Offset(0, 1)
> '   R.Offset(, 2).CopyFromRecordset myRS
> '   G.CopyFromRecordset myRS
> '   R.CopyFromRecordset myRS
> '   Set R = R.Offset(0, 2)
> '   R.CopyFromRecordset myRS
> '   R.Offset(0, 2) = myRS

> 'The else works fine...
> Else
>     R.Offset(0, 2).Value = "No Records Returned"
> End If

> End Sub

> Thanks
> Jaime



Fri, 04 Nov 2005 15:53:11 GMT  
 Offset
From help on copyfromrecordset
Copies the contents of a DAO [ADO as well in xl 2000 and later] Recordset
object onto a worksheet, beginning at the upper-left corner of the specified
range

In your example R is the specified range.  R.offset(0,1) would be the cell
to the right and R.offset(0,2) would be two cells to the right.

You previously stated that R.CopyFromRecordset  worked - so using offset
should work as well.

Regards,
Tom Ogilvy


Quote:
> > Hello and Help!!!

> > I have an excel form that has a range from B14 thru B27 that we use to
> enter
> > a part number and based on this part number I would like to retrieve the
> > description for it and place it 2 cells to the right.  They way this is
> done
> > is when a part number has been entered into any part of the range I
would
> > select that part number entered and then click a command button that
runs
> > the code below that retrieved the description from my Iseries.  The only
> > problem is that it places it 1 cell to the right and I would like to
place
> > it 2 cells to the right and have not been successful.

> > I am using SEQUEL as a provider since in the sequel program I have
created
> a
> > view has this information and sequel pulls it from the Iseries.  I don't
> > know if this is the problem!!

> > The information in my Iseries is in the following path:
> > QS36F/PARTDESC..........and in the PARTDESC....I have
> > PARTNO.....that holds the part number
> > FL002......that holds the description for that part number

> > Public Sub GetPartNumbers()

> >     Dim myConn As ADODB.Connection
> >     Dim myRS As ADODB.Recordset
> >     Set myConn = New ADODB.Connection
> >     myConn.ConnectionString = "Provider=SEQUEL ViewPoint;"
> >     myConn.Open
> >     Set myRS = New ADODB.Recordset

> > Dim R As Range
> > Set R = Selection(1) 'cell as range that was clicked on

> > 'remove or change to whatever:
> > If Intersect(R, Range("B14:B24")) Is Nothing Then
> >     MsgBox "You are not in Range(""B14:B24"")"
> >     Exit Sub
> > End If

> > selVal = Val(R.Value) 'returnes the number in cell associated
> > If selVal = 0 Then
> >     MsgBox "Select a Part Number then click on Get Part Number cmd
button"
> >     Exit Sub
> > End If

> > myRS.Open "USACCT/DDVDPARTNO /setvar((&PARTNO " & selVal & "))", myConn

> > 'This brings in the results but it places it in the cell to the
> > 'right and I need the results 2 cells to the right
> > If myRS.RecordCount > 0 Then
> >     R.CopyFromRecordset MyRS

> > 'These are all the things I have tried and they don't
> > 'seem to work
> > '   Copy myRS(1, 2)
> > '   Copy myRS.Offset(0, 1)
> > '   R.Offset(, 2).CopyFromRecordset myRS
> > '   G.CopyFromRecordset myRS
> > '   R.CopyFromRecordset myRS
> > '   Set R = R.Offset(0, 2)
> > '   R.CopyFromRecordset myRS
> > '   R.Offset(0, 2) = myRS

> > 'The else works fine...
> > Else
> >     R.Offset(0, 2).Value = "No Records Returned"
> > End If

> > End Sub

> > Thanks
> > Jaime



Fri, 04 Nov 2005 16:23:57 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. if activecell.offset()=activecell.offset

2. How to prevent getting an error ModName: unknown ModVer: 0.0.0.0 Offset: 001e493a

3. Time stamp 6 hours offset

4. Passing arguments to my offset formula

5. Speed problem using Offset in VBA

6. Sum, Offset & Calculation

7. Offset,

8. Offset function not defined problem

9. Following from the Find and offset problem

10. changing offset value in macro


 
Powered by phpBB® Forum Software © phpBB Group