We help IT Professionals succeed at work.

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

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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Use Copy|PasteSpecial|Formulas

Author

Commented:
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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Software Professional
CERTIFIED EXPERT
Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
hi MartinLiss

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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
SANTABABYSoftware Professional
CERTIFIED EXPERT

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.