Solved

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

Posted on 2012-03-13
11
276 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 46

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 46

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 46

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 46

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Outlook Free & Paid Tools
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 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

22 Experts available now in Live!

Get 1:1 Help Now