Solved

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

Posted on 2012-03-13
11
275 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 45

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
 
LVL 45

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 500 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 45

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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now