Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how copy personal function vba to get data who update data to diferent cells excel

Posted on 2012-03-13
11
Medium Priority
?
288 Views
Last Modified: 2012-03-13
hi..

i have a excel , at wich i make a vba function. when i write function to a cell as:

=GetSupplierId()

it run fine, and give me a sql data value fine

but when i copy -paste it to other cell, excel don't give me right value

when i copy it function to more 2 cells, only first cell give me right value, others cells copy same value than first cell copied

problem is i have more 1000 rows, and i can't write function one by one cell, and when i copy it , dont run fine

thanks a lot

i hope someone could help me
MINUTA-2284820110902-importante.xls
0
Comment
Question by:tenriquez199
  • 5
  • 4
  • 2
11 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 37716562
Use Copy|PasteSpecial|Formulas
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37716628
hi MartinLiss

thanks your comment

i did it, but result is same..... only first cell give me fine data

example

i'm on cell k11,  and copy it

then mark cells k12 thru K14, and paste only formula , but result is it :

K12 8332.00        (is fine)
K13 8332.00        (is wrong)
K14 8332.00       (is wrong)


any other idea?

thank a lot sincerly
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37716665
reviewing my vba code i think here is the problem, cause i think the function activecell always take a cell value whre is located , so show same value

any other idea ? i think we need to change function activecell or something that

Function GetSupplierId()
   
   
    Dim importe  As Double
    Dim IdDoc As String
    IdDoc = ActiveCell.Offset(, -7)
    Rem IdDoc = cell.Text
 
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
    GetSupplierId = rs.Fields(0).Value
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:Martin Liss
ID: 37716751
Something like this maybe? I don't have time to test it.

Function GetSupplierId()
   
   
    Dim importe  As Double
    Dim IdDoc As String
    'IdDoc = ActiveCell.Offset(, -7)

Dim r As Range
Dim i As Long
    Rem IdDoc = cell.Text
 
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")


    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")

For i = 1 to 1000 ' change this to the 1000 rows you want to change like 24 to 1023
    r = ("A" & i) ' change 'A' to the correct column
    'GetSupplierId = rs.Fields(0).Value
    r.Value = rs.Fields(0).Value
Next
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0
 
LVL 10

Accepted Solution

by:
SANTABABY earned 2000 total points
ID: 37716803
Please make the following changes:

In Module 1: replace the GetSupplierId function code with the following code.

Function GetSupplierId(Optional row As Long = 0, Optional col As Long = 0)
    If row = 0 Or col = 0 Then
        GetSupplierId = "?"
        Exit Function
    End If
   
    Dim importe  As Double
    Dim IdDoc As String
    'IdDoc = ActiveCell.Offset(, -7)
    IdDoc = Cells(row, col - 7)
    Rem IdDoc = cell.Text
 
   
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
    GetSupplierId = rs.Fields(0).Value
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function



Then in you spreadsheet:
Change all cells  that have the formula =GetSupplierID to the following:
=GetSupplierId(ROW(),COLUMN())

Please let me know if it works for you.

Thanks,
SB
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 37716918
Dear Santa…:)  Anyhow in my code I was trying to avoid the asker's code from opening and closing the database 1000 times and yours could do the same by do the Opening and Closing  once outside of the Function.
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37717538
hi MartinLiss

i have an error when run , i adebug VBA
error-vba.JPG
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37717543
Martinliss this is the code

Function GetSupplierId()
   
   
    Dim importe  As Double
    Dim IdDoc As String
    'IdDoc = ActiveCell.Offset(, -7)

Dim r As Range
Dim i As Long
    Rem IdDoc = cell.Text
 
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")


    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")

For i = 1 To 1459
    r = ("D" & i)
    'GetSupplierId = rs.Fields(0).Value
    r.Value = rs.Fields(0).Value
Next
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function


............


the error is : onject variable or block with don't stabished
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 37717602
Forget about r

Change

For i = 1 To 1459
    r = ("D" & i)
    'GetSupplierId = rs.Fields(0).Value
    r.Value = rs.Fields(0).Value
Next

to

For i = 1 To 1459
    'GetSupplierId = rs.Fields(0).Value
   Range("D" & i) = rs.Fields(0).Value
Next
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37717679
hi Martinliss

i did the chnages , don't run fine send error name

Function GetSupplierId()
    Dim importe  As Double
    Dim IdDoc As String
    Dim r As Range
    Dim i As Long
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
    For i = 1 To 1459
        Range("K" & i) = rs.Fields(0).Value
    Next
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 37717829
Don't you need to run the query for each row?

Function GetSupplierId()
    Dim importe  As Double
    Dim IdDoc As String
    Dim r As Range
    Dim i As Long
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    For i = 1 To 1459
        IdDoc = Range("D" & i).Value
        Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
        Range("K" & i) = rs.Fields(0).Value
        rs.Close
    Next
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question